代码之家 › 专栏 › 技术社区 › Keliimek

动态连接不同计数的SA表

jointable sas dynamic sql

-1

Keliimek · 技术社区 · 7 年前

我在sas sql中工作。

MT          column      RT  AREA balance_07092017 
ACCOUNTS    balance_lcy 30      2004862772  
ACCOUNTS    balance_lcy 30  CA  121390255,8 
ACCOUNTS    balance_lcy 30  GL  323499587
ACCOUNTS    balance_lcy 30  TF  -7821721555 
C_ACCOUNTS  balance_lcy 35  CA  2,49733E+11 
C_ACCOUNTS  balance_lcy 35  NO  3748192715  


 MT         column      RT  AREA balance_08092017 
 ACCOUNTS   balance_lcy 30      -24278162321    
 ACCOUNTS   balance_lcy 30  CA  225363070.05    
 ACCOUNTS   balance_lcy 30  GL  3117815863.7
 ACCOUNTS   balance_lcy 30  TF  47914289803 
 C_ACCOUNTS balance_lcy 35  CA  37637391174 
 C_ACCOUNTS balance_lcy 35  NO  163722935.2

是否可以创建一个脚本来自动连接这些表?它们应该是这样的

MT          column      RT  AREA balance_07092017 balance_08092017 balance_09092017 balance_10092017....
ACCOUNTS    balance_lcy 30      2004862772        -24278162321
ACCOUNTS    balance_lcy 30  CA  121390255,8       225363070.05
ACCOUNTS    balance_lcy 30  GL  323499587         3117815863.7
ACCOUNTS    balance_lcy 30  TF  -7821721555       47914289803
C_ACCOUNTS  balance_lcy 35  CA  2,49733E+11       37637391174
C_ACCOUNTS  balance_lcy 35  NO  3748192715        163722935.2

下面是创建我需要连接的表的代码

%macro sqlloop(start,end); 
   PROC SQL; 
     %DO DT_REP=&start. %TO &end.; 

        %let year=%sysfunc(year(&DT_REP.));
        %let month=%sysfunc(month(&DT_REP.));
        %let month1=%sysfunc(PUTN(&month.,z2.));
        %let day=%sysfunc(day(&DT_REP.));
        %let day1=%sysfunc(PUTN(&day.,z2.));
        %let datum= &day1.&month1.&year.;
        %put &datum.;

       CREATE TABLE DUPLICITY_BAL_&datum. as 
        select 'ACCOUNTS' as MT, 'balance_lcy' as column, rec_type, area, sum(balance_lcy) as balance_lcy, count(balance_lcy) as count
        from database.ACCOUNTS
        where version_no = 1
          and dt_rep = &DT_REP.
        group by rec_type, area

        union all
        select 'C_ACCOUNTS' as MT, 'balance_lcy' as column, rec_type, area, sum(balance_lcy) as balance_lcy, count(balance_lcy) as count
        from database.C_ACCOUNTS
        where version_no = 1
          and dt_rep = &DT_REP.
        group by rec_type, area;
     %END; 
   QUIT;
%mend; 

%sqlloop(start=21070, end=21073)

2 回复 | 直到 7 年前

Tom 7 年前

为什么要使用SQL?使用SAS代码合并多个数据集非常简单。如果要合并以开头的所有数据集 TableName_ 您使用 :

data want ;
  merge TableName_: ;
  by MT column RT AREA ;
run;

首先,为什么要将数据拆分为多个表?为什么不一次生成所有日期?

%macro sqlloop(start,end); 
PROC SQL; 
  CREATE TABLE DUPLICITY_BAL as 
    select 'ACCOUNTS' as MT
         , 'balance_lcy' as column
         , dt_rep
         , rec_type
         , area
         , sum(balance_lcy) as balance_lcy
         , count(balance_lcy) as count
    from database.ACCOUNTS
    where version_no = 1
      and dt_rep between &start and &end
    group by 1,2,3,4,5

    union all
    select 'C_ACCOUNTS' as MT
         , 'balance_lcy' as column
         , dt_rep
         , rec_type
         , area
         , sum(balance_lcy) as balance_lcy
         , count(balance_lcy) as count
    from database.C_ACCOUNTS
    where version_no = 1
      and dt_rep between &start and &end
    group by 1,2,3,4,5
  ;
quit;
%mend sqlloop;

Reeza 7 年前

追加表并将balance\u日期重命名为balance,或在所有数据集都相同的名称。