topic Re: SAS Macro: Merge only existing datasets in SAS Programming

SAS Macro: Merge only existing datasets

apple — Mon, 28 Mar 2016 01:25:56 GMT

Using SAS EG 7.1.

Basically for &LASTYEAR, the dataset with MONTHF =12 may not exist. So is there a way to ensure only the existing datasets are merged? Thank you


%let monlist = 06 12;

data Merged;
  set
  %do YEAR=&FIRSTYR %TO &LASTYR;
	%do i = 1 %to %sysfunc(countw(&monlist.));
    	%let monthf = %sysfunc(putn(%scan(&monlist.,&i.),z2.));
         Merged&YEAR.&MONTHF
  	%end; 
  %end;

run;

Re: SAS Macro: Merge only existing datasets

SASKiwi — Tue, 29 Mar 2016 01:31:23 GMT

%let monlist = 06 12;

data Merged;
  set
  %do YEAR=&FIRSTYR %TO &LASTYR;
	%do i = 1 %to %sysfunc(countw(&monlist.));
    	%let monthf = %sysfunc(putn(%scan(&monlist.,&i.),z2.));
        %if %sysfunc(exist(Merged&YEAR.&MONTHF.)) %then %do;
           Merged&YEAR.&MONTHF
          %end;
  	%end; 
  %end;

run;

Corrected thanks to @FreelanceReinh.

Re: SAS Macro: Merge only existing datasets

Reeza — Mon, 28 Mar 2016 03:39:50 GMT

You're appending (stacking datasets) not merging.

If you have the option, consider using a variable list or shortcut list instead of a macro to list the datasets.

For example you can can use the following to append all datasets that start with MONTH.

Set MONTH:;

Or you can use the following to append a series with each year and then anything that starts with Month2007, Month2008 and Month2009 will be included.

Set Month2007: Month2008: Month2009:;

Re: SAS Macro: Merge only existing datasets

FreelanceReinh — Mon, 28 Mar 2016 17:52:23 GMT

@apple: The SET statement needs a closing semicolon after the second %END statement.

@SASKiwi: The argument of EXIST should be Merged&YEAR.&MONTHF. Interestingly, if none of the datasets existed, the resulting "empty" SET; statement would be interpreted as SET _LAST_; and thus possibly read some random dataset.