search

juiyuang / p1

p1
0 stars 0 forks source link

test #1

Open juiyuang opened 2 years ago

juiyuang commented 2 years ago

test

juiyuang commented 2 years ago

test2

juiyuang commented 2 years ago

--1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN ('S','U','G') -- No need for these system accounts AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
    [UserType] = CASE membprinc.[type]
                     WHEN 'S' THEN 'SQL User'
                     WHEN 'U' THEN 'Windows User'
                     WHEN 'G' THEN 'Windows Group'
                 END,
    [DatabaseUserName] = membprinc.[name],
    [LoginName]        = ulogin.[name],
    [Role]             = roleprinc.[name],
    [PermissionType]   = perm.[permission_name],
    [PermissionState]  = perm.[state_desc],
    [ObjectType] = CASE perm.[class]
                       WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                       ELSE perm.[class_desc]             -- Higher-level objects
                   END,
    [Schema] = objschem.[name],
    [ObjectName] = CASE perm.[class]
                       WHEN 3 THEN permschem.[name]       -- Schemas
                       WHEN 4 THEN imp.[name]             -- Impersonations
                       ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                   END,
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members          AS members
    --Roles
    JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    --Role members (database users)
    JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
    --Login accounts
    LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
    --Permissions
    LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
    LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                       AND col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
WHERE
    membprinc.[type] IN ('S','U','G')
    -- No need for these system accounts
    AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

--3) List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserType]         = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [LoginName]        = '{All Users}',
    [Role]             = roleprinc.[name],
    [PermissionType]   = perm.[permission_name],
    [PermissionState]  = perm.[state_desc],
    [ObjectType] = CASE perm.[class]
                       WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                       ELSE perm.[class_desc]             -- Higher-level objects
                   END,
    [Schema] = objschem.[name],
    [ObjectName] = CASE perm.[class]
                       WHEN 3 THEN permschem.[name]       -- Schemas
                       WHEN 4 THEN imp.[name]             -- Impersonations
                       ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                   END,
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals            AS roleprinc
    --Role permissions
    LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    --All objects
    JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
    LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                       AND col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
WHERE
    roleprinc.[type] = 'R'
    AND roleprinc.[name] = 'public'
    AND obj.[is_ms_shipped] = 0

ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]

juiyuang commented 2 years ago

--1) List all access provisioned to a SQL user or Windows user/group directly SELECT [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' END, [DatabaseUserName] = princ.[name], [LoginName] = ulogin.[name], [Role] = NULL, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.[type_desc] -- Schema-contained objects ELSE perm.[class_desc] -- Higher-level objects END, [Schema] = objschem.[name], [ObjectName] = CASE perm.[class] WHEN 3 THEN permschem.[name] -- Schemas WHEN 4 THEN imp.[name] -- Impersonations ELSE OBJECT_NAME(perm.[major_id]) -- General objects END, [ColumnName] = col.[name] FROM --Database user sys.database_principals AS princ --Login accounts LEFT JOIN sys.server_principals AS ulogin ON ulogin.[sid] = princ.[sid] --Permissions LEFT JOIN sys.database_permissions AS perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN sys.schemas AS permschem ON permschem.[schema_id] = perm.[major_id] LEFT JOIN sys.objects AS obj ON obj.[object_id] = perm.[major_id] LEFT JOIN sys.schemas AS objschem ON objschem.[schema_id] = obj.[schema_id] --Table columns LEFT JOIN sys.columns AS col ON col.[object_id] = perm.[major_id] AND col.[column_id] = perm.[minor_id] --Impersonations LEFT JOIN sys.database_principals AS imp ON imp.[principal_id] = perm.[major_id] WHERE princ.[type] IN ('S','U','G') -- No need for these system accounts AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

--2) List all access provisioned to a SQL user or Windows user/group through a database or application role
SELECT
    [UserType] = CASE membprinc.[type]
                     WHEN 'S' THEN 'SQL User'
                     WHEN 'U' THEN 'Windows User'
                     WHEN 'G' THEN 'Windows Group'
                 END,
    [DatabaseUserName] = membprinc.[name],
    [LoginName]        = ulogin.[name],
    [Role]             = roleprinc.[name],
    [PermissionType]   = perm.[permission_name],
    [PermissionState]  = perm.[state_desc],
    [ObjectType] = CASE perm.[class]
                       WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                       ELSE perm.[class_desc]             -- Higher-level objects
                   END,
    [Schema] = objschem.[name],
    [ObjectName] = CASE perm.[class]
                       WHEN 3 THEN permschem.[name]       -- Schemas
                       WHEN 4 THEN imp.[name]             -- Impersonations
                       ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                   END,
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members          AS members
    --Roles
    JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
    --Role members (database users)
    JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
    --Login accounts
    LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
    --Permissions
    LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
    LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                       AND col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
WHERE
    membprinc.[type] IN ('S','U','G')
    -- No need for these system accounts
    AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

--3) List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserType]         = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [LoginName]        = '{All Users}',
    [Role]             = roleprinc.[name],
    [PermissionType]   = perm.[permission_name],
    [PermissionState]  = perm.[state_desc],
    [ObjectType] = CASE perm.[class]
                       WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                       ELSE perm.[class_desc]             -- Higher-level objects
                   END,
    [Schema] = objschem.[name],
    [ObjectName] = CASE perm.[class]
                       WHEN 3 THEN permschem.[name]       -- Schemas
                       WHEN 4 THEN imp.[name]             -- Impersonations
                       ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                   END,
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals            AS roleprinc
    --Role permissions
    LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
    LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
    --All objects
    JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
    LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
    --Table columns
    LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                       AND col.[column_id] = perm.[minor_id]
    --Impersonations
    LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
WHERE
    roleprinc.[type] = 'R'
    AND roleprinc.[name] = 'public'
    AND obj.[is_ms_shipped] = 0

ORDER BY [UserType], [DatabaseUserName], [LoginName], [Role], [Schema], [ObjectName], [ColumnName], [PermissionType], [PermissionState], [ObjectType]

juiyuang commented 2 years ago

select princ.name , princ.type_desc , perm.permission_name , perm.state_desc , perm.class_desc , object_name(perm.major_id) from sys.database_principals princ left join sys.database_permissions perm on perm.grantee_principal_id = princ.principal_id

juiyuang commented 2 years ago

sp_table_privileges '%%'

juiyuang commented 2 years ago

https://stackoverflow.com/questions/14604576/how-to-see-what-privileges-are-granted-to-schema-of-another-user

juiyuang commented 2 years ago

--List all access provisioned to a sql user or windows user/group directly SELECT
[UserName] = CASE princ.[type] WHEN 'S' THEN princ.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE princ.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM
--database user sys.database_principals princ
LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN
--Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] WHERE princ.[type] in ('S','U') UNION --List all access provisioned to a sql user or windows user/group through a database or application role SELECT
[UserName] = CASE memberprinc.[type] WHEN 'S' THEN memberprinc.[name] WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI END, [UserType] = CASE memberprinc.[type] WHEN 'S' THEN 'SQL User' WHEN 'U' THEN 'Windows User' END, [DatabaseUserName] = memberprinc.[name],
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM
--Role/member associations sys.database_role_members members JOIN --Roles sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] JOIN --Role members (database users) sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] LEFT JOIN --Login accounts sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] LEFT JOIN
--Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] UNION --List all access provisioned to the public role, which everyone gets by default SELECT
[UserName] = '{All Users}', [UserType] = '{All Users}', [DatabaseUserName] = '{All Users}',
[Role] = roleprinc.[name],
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = obj.type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME(perm.major_id), [ColumnName] = col.[name] FROM
--Roles sys.database_principals roleprinc LEFT JOIN
--Role permissions sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] LEFT JOIN --Table columns sys.columns col on col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id]
JOIN --All objects
sys.objects obj ON obj.[object_id] = perm.[major_id] WHERE --Only roles roleprinc.[type] = 'R' AND --Only public role roleprinc.[name] = 'public' AND --Only objects of ours, not the MS objects obj.is_ms_shipped = 0 ORDER BY princ.[Name], OBJECT_NAME(perm.major_id), col.[name], perm.[permission_name], perm.[state_desc], obj.type_desc--perm.[class_desc]

juiyuang commented 2 years ago

select * from sys.database_principals

select * from sys.schemas

select * from sys.database_permissions

select * from sys.database_role_members

select * from sys.database_principals

juiyuang commented 2 years ago

SELECT state_desc ,permission_name ,'ON' ,class_desc ,SCHEMA_NAME(major_id) ,'TO' ,USER_NAME(grantee_principal_id) FROM sys.database_permissions AS PERM JOIN sys.database_principals AS Prin ON PERM.major_ID = Prin.principal_id AND class_desc = 'SCHEMA' WHERE major_id = SCHEMA_ID('TestSchema')

juiyuang commented 2 years ago

SELECT sc.name as schema_name,

[PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc] -- [ObjectType] = obj.type_desc,--perm.[class_desc], -- [ObjectName] = OBJECT_NAME(perm.major_id), -- [ColumnName] = col.[name] FROM --database user sys.database_principals princ LEFT JOIN --Login accounts sys.login_token ulogin on princ.[sid] = ulogin.[sid] LEFT JOIN --Permissions sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id] /LEFT JOIN --Table columns sys.columns col ON col.[object_id] = perm.major_id AND col.[column_id] = perm.[minor_id] LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id] / JOIN sys.schemas sc ON sc.schema_id = princ.principal_id WHERE princ.[type] in ('S','U')

juiyuang commented 2 years ago

https://nikkoam.zoom.us/j/94753676443?pwd=RTZNQk00dzNwdmU1c3JIdGFMaGFkUT09

juiyuang commented 2 years ago

q9bAjZiW

sd_sansan_admin1@nikkoam.com

sd_sansan_admin2@nikkoam.com

juiyuang commented 2 years ago

*****;

%MACRO SMECHINEXTIMPORT(_DEBUG=1);

 ***************************************************;
 * 環境定義                                        *;
 ***************************************************;
 %IF &_DEBUG=0 %THEN %DO;
      %LET DATADIR   =\\int01\d$\batch\Shenzen_SMEChiNext100\data;
      %LET DATASOURCE=mkdb01;
      %LET USERID    =sysops;
      %LET PASSWORD  =sysops01$;
      %LET DATABASE  =MarketDB;
 %END;
 %ELSE %DO;
      %LET DATADIR   =\\oskdevint001\d$\batch\Shenzen_SMEChiNext100\data;
      %LET DATASOURCE=oskdevmkdb001;
      %LET USERID    =sysops;
      %LET PASSWORD  =sysops01$;
      %LET DATABASE  =MarketDB;
 %END;

 libname MarketUP OLEDB PROVIDER =SQLOLEDB
                  DATASOURCE     ="&DATASOURCE"
                  USER           ="&USERID"
                  PASSWORD       ="&PASSWORD"
                  PROPERTIES     =("INITIAL CATALOG"="&DATABASE")
                  SCHEMA         =dbo
                  CONNECTION     =SHAREDREAD
                  COMMAND_TIMEOUT=60
                  DEFER          =YES
                  DBINDEX        =YES
                  BULKLOAD       =YES
 ;

 *過去遡及日数*;
 %LET UPDATEDAYS=1;

 ***************************************************;
 * 日付定義                                        *;
 ***************************************************;
 data _null_;
      call symput('TODAY',compress(put(date(),yymmdd10.),'-'));
 run;

 proc sql noprint;
      select MIN(DT) into :KIJUNYMDOPEN
      from   NITDB.NAA0020
      where  DT   >="&TODAY"
         and STS1 not in ('1' '4')
      ;
 quit;

 data wk0;
      set NITDB.NAA0020(where=(DT   <="&TODAY"
                           and STS1 not in ('1' '4')));
 run;

 proc sort data=wk0; by descending DT;
 run;

 data wk0;
      set wk0;
      by descending DT;
      if _n_<=%EVAL(&UPDATEDAYS+1) then do;
           call symput('KIJUNYMD'||trim(left(_n_)),DT);
      end;
 run;

 %DO LOOP=1 %TO &UPDATEDAYS;
      %PUT KIJUNYMD&LOOP=&&KIJUNYMD&LOOP;
 %END;

 data _null_;
    set wk0;
    by descending DT;
    if _n_=&UPDATEDAYS then call symput('KIJUNYMDPASTINDEX',DT);
 run;

 %PUT KIJUNYMDPASTINDEX=&KIJUNYMDPASTINDEX;

 %LET indexCode = 399612;

 ***************************************************;
 * データファイル取込処理                          *;
 ***************************************************;
 %DO LOOP=1 %TO &UPDATEDAYS;

      ***************************************************;
      * 指数データ取込処理                              *;
      ***************************************************;
      %IF &&KIJUNYMD&LOOP=&KIJUNYMDOPEN %THEN %DO;

         *ファイル存在チェック  *;
         filename INDEX "&DATADIR\&indexCode.perf&&KIJUNYMD&LOOP.._E.xls";

         %LET INDEX=%SYSFUNC(FOPEN(INDEX));

         %IF &INDEX=0 %THEN %DO;
            %GOTO INDEXEND;
         %END;

         *ファイル参照定義      *;
          proc import datafile="&DATADIR\&indexCode.perf&&KIJUNYMD&LOOP.._E.xls" 
               out=Wk_Index replace dbms=xls;
               sheet='sheet1';
               getnames=No;

          run;
          quit;

          proc sort data=Wk_Index; by descending A;
          run;

          *データファイル取込処理*;
          data tblShenzen_SMEChiNext100Index;
                set Wk_Index(firstobs=2);                     

              *変数定義  *;
                length DateIndex $8 
                    IndexCode $10 
                    IndexName $20 
                    NumberofConstituents 8
                    CloseIndex 8
                    CloseTRIndex 8
                    TotalMarketCap 8
                    FreeFloatMarketCap 8
                    CreateDate 8
                ;

                DateIndex = compress(put(A*1-21916,yymmdd10.),'-');
                IndexCode =ktrim(B);
                IndexName=ktrim(C);
                NumberofConstituents=D;
                CloseIndex=E;
                CloseTRIndex=F;
                TotalMarketCap=G;
                FreeFloatMarketCap=H;
                CreateDate   =input(put(today(),date9.)||':'||put(time(),time8.),datetime18.);

                keep DateIndex IndexCode IndexName NumberofConstituents CloseIndex CloseTRIndex TotalMarketCap FreeFloatMarketCap CreateDate;
          run;

          *データベース更新      *;
          proc sql noprint;
               delete from MarketUP.tblShenzen_SMEChiNext100_Index where Date >="&KIJUNYMDPASTINDEX" ;
               insert into MarketUP.tblShenzen_SMEChiNext100_Index select *  from tblShenzen_SMEChiNext100Index where DateIndex >="&KIJUNYMDPASTINDEX";
          quit; 

      %END;

%INDEXEND: %LET INDEX=%SYSFUNC(FCLOSE(&INDEX)); filename INDEX clear;

      ***************************************************;
      * 構成銘柄データ取込処理                           *;
      ***************************************************;
      *ファイル参照定義      *;
      proc import datafile="&DATADIR\&indexCode.cons&&KIJUNYMD&LOOP.._E.xls" 
           out=Wk_Cons replace dbms=xls;
           sheet='sheet1';
           getnames=No;

      run;
      quit;

      *データファイル取込処理*;
      data tblShenzen_SMEChiNext100Cons;
            set Wk_Cons(firstobs=2);                      

          *変数定義  *;
            length DateIndex $8 
                IndexCode $10 
                IndexName $20 
                ISIN $12 
                LocalCode $10 
                ConstituentName_CH $20 
                ContituentName_EN $100 
                ClosePrice 8 
                FreeFloatShares 8 
                TotalMarketCap 8 
                AdjFreeFloatMarketCap 8 
                CloseWeight 8 
                Sector $60 
                Markets $100
                CreateDate 8 
            ;
            DateIndex = compress(put(A*1-21916,yymmdd10.),'-');
            IndexCode =ktrim(B);
            IndexName=ktrim(C);
            ISIN=ktrim(D);
            LocalCode=ktrim(E);
            *ConstituentName_CH=F*;
            ContituentName_EN=ktrim(G);
            ClosePrice=H;
            FreeFloatShares=I;
            TotalMarketCap=J;
            AdjFreeFloatMarketCap=K;
            CloseWeight=L;
            Sector=ktrim(M);
            Markets=ktrim(N);               
            CreateDate   =input(put(today(),date9.)||':'||put(time(),time8.),datetime18.);

            keep DateIndex IndexCode IndexName ISIN LocalCode ContituentName_EN ClosePrice FreeFloatShares TotalMarketCap AdjFreeFloatMarketCap CloseWeight Sector Markets CreateDate;
      run;

      *データベース更新      *;
      proc sql noprint;
           delete from MarketUP.tblShenzen_SMEChiNext100_Const where Date="&&KIJUNYMD&LOOP"    ;
           insert into MarketUP.tblShenzen_SMEChiNext100_Const select *  from tblShenzen_SMEChiNext100Cons;
      quit;

 %LOOPEND:

      ********************************************;
      * LOOP終了                                 *;
      ********************************************;
 %END;

%EXIT: ***;

%MEND SMECHINEXTIMPORT;

 *********************;
 * 処理実行          *;
 *********************;
 %SMECHINEXTIMPORT(_DEBUG=0);

/ %SMECHINEXTIMPORT(_DEBUG=1);/

juiyuang commented 2 years ago

*****;

%MACRO SMECHINEXTIMPORT(_DEBUG=1);

 ***************************************************;
 * 環境定義                                        *;
 ***************************************************;
 %IF &_DEBUG=0 %THEN %DO;
      %LET DATADIR   =\\int01\d$\batch\Shenzen_SMEChiNext100\data;
      %LET DATASOURCE=mkdb01;
      %LET USERID    =sysops;
      %LET PASSWORD  =sysops01$;
      %LET DATABASE  =MarketDB;
 %END;
 %ELSE %DO;
      %LET DATADIR   =\\oskdevint001\d$\batch\Shenzen_SMEChiNext100\data;
      %LET DATASOURCE=oskdevmkdb001;
      %LET USERID    =sysops;
      %LET PASSWORD  =sysops01$;
      %LET DATABASE  =MarketDB;
 %END;

 libname MarketUP OLEDB PROVIDER =SQLOLEDB
                  DATASOURCE     ="&DATASOURCE"
                  USER           ="&USERID"
                  PASSWORD       ="&PASSWORD"
                  PROPERTIES     =("INITIAL CATALOG"="&DATABASE")
                  SCHEMA         =dbo
                  CONNECTION     =SHAREDREAD
                  COMMAND_TIMEOUT=60
                  DEFER          =YES
                  DBINDEX        =YES
                  BULKLOAD       =YES
 ;

 *過去遡及日数*;
 %LET UPDATEDAYS=1;

 ***************************************************;
 * 日付定義                                        *;
 ***************************************************;
 data _null_;
      call symput('TODAY',compress(put(date(),yymmdd10.),'-'));
 run;

 proc sql noprint;
      select MIN(DT) into :KIJUNYMDOPEN
      from   NITDB.NAA0020
      where  DT   >="&TODAY"
         and STS1 not in ('1' '4')
      ;
 quit;

 data wk0;
      set NITDB.NAA0020(where=(DT   <="&TODAY"
                           and STS1 not in ('1' '4')));
 run;

 proc sort data=wk0; by descending DT;
 run;

 data wk0;
      set wk0;
      by descending DT;
      if _n_<=%EVAL(&UPDATEDAYS+1) then do;
           call symput('KIJUNYMD'||trim(left(_n_)),DT);
      end;
 run;

 %DO LOOP=1 %TO &UPDATEDAYS;
      %PUT KIJUNYMD&LOOP=&&KIJUNYMD&LOOP;
 %END;

 data _null_;
    set wk0;
    by descending DT;
    if _n_=&UPDATEDAYS then call symput('KIJUNYMDPASTINDEX',DT);
 run;

 %PUT KIJUNYMDPASTINDEX=&KIJUNYMDPASTINDEX;

 %LET indexCode = 399612;

 ***************************************************;
 * データファイル取込処理                          *;
 ***************************************************;
 %DO LOOP=1 %TO &UPDATEDAYS;

      ***************************************************;
      * 指数データ取込処理                              *;
      ***************************************************;
      %IF &&KIJUNYMD&LOOP=&KIJUNYMDOPEN %THEN %DO;

         *ファイル存在チェック  *;
         filename INDEX "&DATADIR\&indexCode.perf&&KIJUNYMD&LOOP.._E.xls";

         %LET INDEX=%SYSFUNC(FOPEN(INDEX));

         %IF &INDEX=0 %THEN %DO;
            %GOTO INDEXEND;
         %END;

         *ファイル参照定義      *;
          proc import datafile="&DATADIR\&indexCode.perf&&KIJUNYMD&LOOP.._E.xls" 
               out=Wk_Index replace dbms=xls;
               sheet='sheet1';
               getnames=No;

          run;
          quit;

          proc sort data=Wk_Index; by descending A;
          run;

          *データファイル取込処理*;
          data tblShenzen_SMEChiNext100Index;
                set Wk_Index(firstobs=2);                     

              *変数定義  *;
                length DateIndex $8 
                    IndexCode $10 
                    IndexName $20 
                    NumberofConstituents 8
                    CloseIndex 8
                    CloseTRIndex 8
                    TotalMarketCap 8
                    FreeFloatMarketCap 8
                    CreateDate 8
                ;

                DateIndex = compress(put(A*1-21916,yymmdd10.),'-');
                IndexCode =ktrim(B);
                IndexName=ktrim(C);
                NumberofConstituents=D;
                CloseIndex=E;
                CloseTRIndex=F;
                TotalMarketCap=G;
                FreeFloatMarketCap=H;
                CreateDate   =input(put(today(),date9.)||':'||put(time(),time8.),datetime18.);

                keep DateIndex IndexCode IndexName NumberofConstituents CloseIndex CloseTRIndex TotalMarketCap FreeFloatMarketCap CreateDate;
          run;

          *データベース更新      *;
          proc sql noprint;
               delete from MarketUP.tblShenzen_SMEChiNext100_Index where Date >="&KIJUNYMDPASTINDEX" ;
               insert into MarketUP.tblShenzen_SMEChiNext100_Index select *  from tblShenzen_SMEChiNext100Index where DateIndex >="&KIJUNYMDPASTINDEX";
          quit; 

      %END;

      %INDEXEND:

      ***************************************************;
      * 構成銘柄データ取込処理                           *;
      ***************************************************;
      *ファイル参照定義      *;
      proc import datafile="&DATADIR\&indexCode.cons&&KIJUNYMD&LOOP.._E.xls" 
           out=Wk_Cons replace dbms=xls;
           sheet='sheet1';
           getnames=No;

      run;
      quit;

      *データファイル取込処理*;
      data tblShenzen_SMEChiNext100Cons;
            set Wk_Cons(firstobs=2);                      

          *変数定義  *;
            length DateIndex $8 
                IndexCode $10 
                IndexName $20 
                ISIN $12 
                LocalCode $10 
                ConstituentName_CH $20 
                ContituentName_EN $100 
                ClosePrice 8 
                FreeFloatShares 8 
                TotalMarketCap 8 
                AdjFreeFloatMarketCap 8 
                CloseWeight 8 
                Sector $60 
                Markets $100
                CreateDate 8 
            ;
            DateIndex = compress(put(A*1-21916,yymmdd10.),'-');
            IndexCode =ktrim(B);
            IndexName=ktrim(C);
            ISIN=ktrim(D);
            LocalCode=ktrim(E);
            *ConstituentName_CH=F*;
            ContituentName_EN=ktrim(G);
            ClosePrice=H;
            FreeFloatShares=I;
            TotalMarketCap=J;
            AdjFreeFloatMarketCap=K;
            CloseWeight=L;
            Sector=ktrim(M);
            Markets=ktrim(N);               
            CreateDate   =input(put(today(),date9.)||':'||put(time(),time8.),datetime18.);

            keep DateIndex IndexCode IndexName ISIN LocalCode ContituentName_EN ClosePrice FreeFloatShares TotalMarketCap AdjFreeFloatMarketCap CloseWeight Sector Markets CreateDate;
      run;

      *データベース更新      *;
      proc sql noprint;
           delete from MarketUP.tblShenzen_SMEChiNext100_Const where Date="&&KIJUNYMD&LOOP"    ;
           insert into MarketUP.tblShenzen_SMEChiNext100_Const select *  from tblShenzen_SMEChiNext100Cons;
      quit;

 %LOOPEND:

      ********************************************;
      * LOOP終了                                 *;
      ********************************************;
 %END;

%EXIT: ***;

%MEND SMECHINEXTIMPORT;

 *********************;
 * 処理実行          *;
 *********************;
 %SMECHINEXTIMPORT(_DEBUG=0);
juiyuang commented 2 years ago

123