DATA Step, Macro, Functions and more

SAS Macro: Merge only existing datasets

Reply
Frequent Contributor
Posts: 75

SAS Macro: Merge only existing datasets

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;
Super User
Posts: 3,260

Re: SAS Macro: Merge only existing datasets

[ Edited ]
%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 @FreelanceReinhard

Super User
Posts: 19,867

Re: SAS Macro: Merge only existing datasets

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:;

 

Trusted Advisor
Posts: 1,118

Re: SAS Macro: Merge only existing datasets

@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.

Ask a Question
Discussion stats
  • 3 replies
  • 262 views
  • 2 likes
  • 4 in conversation