SAS-Append data to a table and increment a flag on the resultant table

I have two tables on SAS “Table_A” and “Table_A_Archive”, as part my ETL process “Table_A” is created on a daily basis and the data should be archived on “Table_A_Archive”. When the data is archived on “Table_A_Archive” a a flag will be created/updated “DT_FLAG”.

On the first day this is how the table would look like

 "Table_A"
 | ID  | Load_Date
 ------ -------------
 | 100 | 01JUN2020:12:13:56

 "Table_A_Archive"
  | ID  | Load_Date           | DT_FLAG
  ------ --------------------- ---------
  | 100 | 01JUN2020:12:13:56  | 1

On Day 2

 "Table_A"
 | ID  | Load_Date
 ------ ------------
 | 101 | 02JUN2020:12:13:56

 "Table_A_Archive"
 | ID  | Load_Date           | DT_FLAG
 ------ --------------------- ---------
 | 100 | 01JUN2020:12:13:56  | 2
 | 101 | 02JUN2020:12:13:56  | 1

The new data should be loaded with the DT_FLAG of 1 and old records DT_FLAG should be incremented by 1. Load_Date is the pivotal point. I have written a SAS code but it seems a bit messy, can someone please help me with a SAS Datastep

 %macro Cntl_archive(table_name=,arch_table_name=);
 %GLOBAL WRK;

 %if %sysfunc(exist(&arch_table_name.))  %then %do;

 proc append base=&arch_table_name. data=&table_name. force;
 run;

 proc sql;
 Create table TEMP as 
 Select distinct Load_Date,Load_Date as WRK from &arch_table_name.
 order by Load_Date desc
 ;quit;

 proc rank data=TEMP descending out=TEMP;
 var WRK;
 ranks count;
 run;

 data &arch_table_name. (drop=DT_FLAG);
 set    &arch_table_name.;
 run;

 proc sql;
 Create table &arch_table_name. as
 Select T0.*,T1.count as DT_FLAG from &arch_table_name. T0
 inner join TEMP T1 on T0.Load_Date=T1.Load_Date
 ;quit
 %end;
 %else %do;

data &arch_table_name.;
set &table_name.;
DT_FLAG= 1; 
IS_ACTIVE='';
run;
%end;
%mend Cntl_archive;

Answer

I tried solving it by this method.

%macro Cntl_archive(table_name=,arch_table_name=);

%if %sysfunc(exist(&arch_table_name.))  %then %do;

data Data_append;
set &table_name.;
if _n_ = 1
then do;
  set &arch_table_name.(keep=dt_flag) point=nobs nobs=nobs;
dt_flag + 1;
end;
run;

proc append base=&arch_table_name. data=Data_append force;
run;

%end;
%else %do;

data &arch_table_name.;
set &table_name.;
DT_FLAG= 1; 
IS_ACTIVE='';
run;
%end;
%mend Cntl_archive;

Advertisement

Answer