topic Re: Appending monthly tables in SAS Programming

Appending monthly tables

Solly7 — Mon, 02 Oct 2023 10:08:37 GMT

Good day, I have library called GEN which consists of monthly tables from 201801 to 202308 renamed as transact_base_201801 to transact_base_2023.

So, I need to append the tables from transact_base_201909 till transact_base_202308 using below code and I get the attached error. Kindly assist please

%macro easy_zero;
data easy_zero_rtc;
set GEN.TRANSACT_BASE_201909 - GEN.TRANSACT_BASE_202308; 
keep stamp sub_prod_cde SUB_SEGMENT Account_Type RTC_ONLINE_AMT RTC_BRNCH_AMT RTC_APP_AMT RTC_ONLINE_VOL RTC_BRNCH_VOL RTC_APP_VOL;
where Account_Type ^='Credit Card' and sub_prod_cde='WX';
run;
%mend easy_zero;

%easy_zero;

Re: Appending monthly tables

PeterClemmensen — Mon, 02 Oct 2023 10:18:46 GMT

I think the log is pretty self explanatory. A few points though.

Using this syntax in the Set Statement assumes that a data set with the name transact_base and the relevant suffix is available for each integer between 201909 and 202308. That being the integer, not the date. For example, you can see that SAS looks for the data set GEN.TRANSACT_BASE_201913, which is not available and gives an error.
There is really no need for a macro here.
If you want to stack all the data sets prefixed with TRANSACT_BASE_ in the GEN library you can use the colon operator in the Set Statement like this

set GEN.TRANSACT_BASE_: ;

A small working example, since I can't access your GEN library:

data TRANSACT_BASE_201911; x = 1; run;
data TRANSACT_BASE_201912; x = 2; run;
data TRANSACT_BASE_202001; x = 3; run;

data want;
   set TRANSACT_BASE_: ;
run;

Re: Appending monthly tables

Kurt_Bremser — Mon, 02 Oct 2023 10:21:44 GMT

You can't treat month designators like consecutive numbers, you need to do some date calculations:

%macro easy_zero(begin,end);
%local dat;
data easy_zero_rtc;
set
%let dat = %sysfunc(inputn(&begin.,yymmn6.));
%do %while (&dat. le %sysfunc(inputn(&end.,yymmn6.)));
  gen.transact_base_%sysfunc(putn(&dat.,yymmn6.))
  %let dat = %sysfunc(intnx(month,&dat.,1,b));
%end;
; 
keep stamp sub_prod_cde SUB_SEGMENT Account_Type RTC_ONLINE_AMT RTC_BRNCH_AMT RTC_APP_AMT RTC_ONLINE_VOL RTC_BRNCH_VOL RTC_APP_VOL;
where Account_Type ^= 'Credit Card' and sub_prod_cde = 'WX';
run;
%mend easy_zero;

%easy_zero(201909,202308)

Re: Appending monthly tables

PaigeMiller — Mon, 02 Oct 2023 10:36:24 GMT

set GEN.TRANSACT_BASE_201909 - GEN.TRANSACT_BASE_202308;

SAS thinks that this list of data set names goes from integer 201909 to integer 202308, and so what integer comes after 201912? Why 201913, of course, and this data set does not exist. SAS does not know that this is a list of months where 201913 is impossible.

You probably could create a list of data set names in library GEN that actually exist, via a macro variable. In this case, we query the dictionary tables to find out what data sets actually exist in GEN, and store the results in a macro variable &DSNAMES

/* UNTESTED CODE */
proc sql noprint;
     select distinct cats('gen.',memname) into :dsnames separated by ' ' from dictionary.tables 
     where libname='GEN' and input(substr(memname,15),6.) between 201909 and 202308;
quit;

and then use

set &dsnames;