topic Re: SAS macro to append multiple data sets in SAS Programming

SAS macro to append multiple data sets

ajatshatru — Wed, 27 Mar 2024 11:34:18 GMT

Hi I have data sets as follows :

DATA_SET_202202 (Records for month Feb, 2022)

DATA_SET_202203 (Records for month Mar, 2022)

and so on

I want to append all the data sets in a single table such that records from DATA_SET_202202 are first followed by _202203

and so on

I am using the below MACRO for this :

%MACRO JOIN;

%LET DD = '202202';

%DO COUNT = 0 %TO 4 ; /*FOR TEST RUN*/

%LET DD_DATE = INPUT(&DD.,YYMMN6.);

DATA _NULL_ ;

CALL SYMPUTX('BASEMON&COUNT', PUT(INTNX('MONTH',&DD_DATE.,&COUNT.),YYMMN6.));

RUN;

%PUT BASEMON&COUNT:&&BASEMON&COUNT;

%IF &COUNT. = 0 %THEN %DO ;

DATA TEST;

SET DATA_SET_&&BASEMON&COUNT.;

RUN;

%END;

%ELSE %D0;

PROC APPEND BASE =TEST DATA=DATA_SET_&&BASEMON&COUNT.;

RUN;

%END;

%MEND;

In this i am getting error that my variable basemon&count itself is not getting created. Can anyone please suggest

Re: SAS macro to append multiple data sets

Kurt_Bremser — Wed, 27 Mar 2024 12:09:36 GMT

'BASEMON&COUNT'

The most important function of single quotes: they prevent the resolution of macro triggers. Use double quotes.

Re: SAS macro to append multiple data sets

PaigeMiller — Wed, 27 Mar 2024 12:15:35 GMT

Why a macro at all?

Why not this:

data want; 
    set data_set_2022: ;
run;

Re: SAS macro to append multiple data sets

Reeza — Wed, 27 Mar 2024 15:12:26 GMT

Instead of listing them manually use shortcut lists.

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

Additionally you can use INDSNAME option to store the source data set name and then sort by that variable if you want a specific order.

data want;
length source dsn $32.;
set data_set_2022: indsname=source; *will append any datasets that start with the prefix;
dsn = source;
run;

@ajatshatru wrote:

Hi I have data sets as follows :

DATA_SET_202202 (Records for month Feb, 2022)

DATA_SET_202203 (Records for month Mar, 2022)

and so on

I want to append all the data sets in a single table such that records from DATA_SET_202202 are first followed by _202203

and so on

I am using the below MACRO for this :

%MACRO JOIN;

%LET DD = '202202';

%DO COUNT = 0 %TO 4 ; /*FOR TEST RUN*/

%LET DD_DATE = INPUT(&DD.,YYMMN6.);

DATA _NULL_ ;

CALL SYMPUTX('BASEMON&COUNT', PUT(INTNX('MONTH',&DD_DATE.,&COUNT.),YYMMN6.));

RUN;

%PUT BASEMON&COUNT:&&BASEMON&COUNT;

%IF &COUNT. = 0 %THEN %DO ;

DATA TEST;

SET DATA_SET_&&BASEMON&COUNT.;

RUN;

%END;

%ELSE %D0;

PROC APPEND BASE =TEST DATA=DATA_SET_&&BASEMON&COUNT.;

RUN;

%END;

%END;

%MEND;

In this i am getting error that my variable basemon&count itself is not getting created. Can anyone please suggest

Re: SAS macro to append multiple data sets

Tom — Wed, 27 Mar 2024 15:26:19 GMT

Depending on your actual needs you might look at using dataset lists.

You can use prefix based lists by using a colon. So if you want to combine any dataset from 2022 it might look like this.

data test;
  set DATA_SET_2022: ;
run;

Or you can use numeric sequences using hyphen. So if you want March 2022 to August 2023 you might use this:

data test;
  set DATA_SET_202203 - DATA_SET_202212 DATA_SET_202301 - DATA_SET_202308 ;
run;

You could make your macro simpler by just generating the list of dataset names.

%macro dslist(prefix,start,end);
%local offset;
%do offset=0 %to %sysfunc(intck(month,&start,&end));
 &prefix.%sysfunc(intnx(month,&start,&offset),yymmn6.)
%end;
%mend dslist;

so that you could call it in the middle of a statement, like the SET statement.

data test;
  set %dslist(data_set_,'01SEP2022'd,'01MAR2023'd) ;
run;

Re: SAS macro to append multiple data sets

FreelanceReinh — Wed, 27 Mar 2024 15:50:51 GMT

You could even use the NODSNFERR system option in conjunction with a dataset list spanning several years:

options nodsnferr;

data test;
set data_set_202202-data_set_202403;
run;

options dsnferr;

Re: SAS macro to append multiple data sets

yabwon — Wed, 27 Mar 2024 15:56:03 GMT

I would add one more "option":

options nodsnferr;

data test;
  set data_set_: INDSNAME=inds;
  obsFromDataSet=inds;
  dataPeriod = input(scan(inds,-1,"_"),yymmdd10.);
  format dataPeriod yymmdd10.;
run;

options dsnferr;

The INDSNAME= gets you info about dataset from which a given observation is from.

Bart

Re: SAS macro to append multiple data sets

s_lassen — Wed, 27 Mar 2024 16:20:41 GMT

You have had a few suggestions of various ways to simplify your code using wildcards etc. - but if you need to do the append with a specific number of increasing dates, your own method may be the right way to do it.

The problem you have with the BASEMON&count variable is, as @Kurt_Bremser remarked, that you put the macro variable name in single quotes, like 'BASEMON&COUNT', this means the data step will try to put data into a macro variable named BASEMON&COUNT and not e.g. BASEMON1 (no resolution of the COUNT variable, so the macro variable name will not be syntactically correct).

But I would probably write the macro somewhat differently, a first version would look like this:

%MACRO JOIN; 
%local StartMonth NoofMonths OutData Date Month DD_Month;
%LET StartMonth = 202202; /* a better and more descriptive name than DD */
%let NoofMonths=5; /* we do 1 to 5 and not 0 to 4, is intuitively easier */
%let OutData=TEST;
/* now convert StartMonth to e.g. 01FEB2022 */
%let Date=%sysfunc(input(&StartMonth,YYMMN6.),date9.);

/* Delete output, if exists */
Proc delete data=&outdata;run;

/* and then we loop, proc APPEND will create a new table if BASE does not exist */
%do Month=1 %to &NoofMonths;
  %let DD_Month=%sysfunc(put("&Date"d,YYMMN6.));
  PROC APPEND BASE =&outdata DATA=DATA_SET_&DD_Month;
  RUN;
  %let Date=%sysfunc(intnx(MONTH,"&Date"d,1,B),date9.);
  %end;

%MEND;

You could also make the DATE macro variable a numeric variable, but I prefer using the DATE9. format, which makes SYMBOLGEN output much easier to understand.

When running for real, you will want to use parameters for your macro, e.g.

%MACRO JOIN(StartMonth,NoofMonths,OutData); 
%local Date Month DD_Month;
/* now convert StartMonth to e.g. 01FEB2022 */
%let Date=%sysfunc(input(&StartMonth,YYMMN6.),date9.);

/* Delete output, if exists */
Proc delete data=&outdata;run;

/* and then we loop, proc APPEND will create a new table if BASE does not exist */
%do Month=1 %to &NoofMonths;
  %let DD_Month=%sysfunc(put("&Date"d,YYMMN6.));
  PROC APPEND BASE =&outdata DATA=DATA_SET_&DD_Month;
  RUN;
  %let Date=%sysfunc(intnx(MONTH,"&Date"d,1),date9.);
  %end;

%MEND;

So you can call it like

%join(202202,5,TEST);

Re: SAS macro to append multiple data sets

ajatshatru — Wed, 27 Mar 2024 16:45:14 GMT

I do not have just a single data_set_2022. I have many monthly tables like this which i need to append in a single data set. So i am using a macro and a loop

Re: SAS macro to append multiple data sets

Tom — Wed, 27 Mar 2024 16:50:35 GMT

@ajatshatru wrote:

I do not have just a single data_set_2022. I have many monthly tables like this which i need to append in a single data set. So i am using a macro and a loop

It was obvious from your question that you had multiple datasets. None of the answered submitted so far assumed otherwise.

Re: SAS macro to append multiple data sets

yabwon — Wed, 27 Mar 2024 16:53:30 GMT

Read this: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/p00hxg3x8lwivcn1f0e9axziw57y.htm#p02h8j6i59drj1n1l5449rfd9jbq

so you will understand that this is what you need.

Bart

Re: SAS macro to append multiple data sets

PaigeMiller — Wed, 27 Mar 2024 17:13:21 GMT

@ajatshatru wrote:

I do not have just a single data_set_2022. I have many monthly tables like this which i need to append in a single data set. So i am using a macro and a loop

I did not write code for the case where you had a single data set. The code I wrote appends all data sets whose name BEGINS WITH data_set_2022.