SAS nested loop syntax

Question

I have a SAS code that works fine to read to datasets and merge them. The data sets are named according to the quarter and year of the data, e.g.: "data1_Q11999" and "data2_Q11999". The code I use to do this is below.

Now I want to loop over several of this datasets by increasing the year from 1999 to 2014 and the quarter from 1 to 4 (i.e. two loops).

My understanding is that I need to create a macro to do this, but I am having some issues with the syntax.

The code is below. I tried to wrap the code around a %macro statement with do loops but keep getting a bunch of syntax errors. Is there a straightforward way of achieving this?

data origfile;
infile "D:/data1_Q11999.txt" dlm= '|' MISSOVER DSD lrecl=32767 firstobs=1 ;
input
fico : 8.
dt_first_pi : 8.
id : $16.
run;


data svcgfile;
infile "D:/data2_Q11999.txt" dlm= '|' MISSOVER DSD lrecl=32767 firstobs=1 ;
input
id : $12.
Period : 8.
actual_loss : 12.
;
run;


PROC SORT DATA=origfile OUT=origfile; 
  BY id; 
RUN; 

PROC SORT DATA=svcgfile OUT=svcgfile; 
  BY id; 
RUN; 

DATA mergedata; 
  MERGE origfile svcgfile;
  BY id; 
RUN; 

Answer 1

Assuming that you want to generate a separated merged file for year quarter you could use a macro like this.

%macro read(first_yr,last_yr);
%local year qtr;
%do year=&first_yr %to &last_yr ;
%do qtr=1 %to 4 ;
data data1;
  infile "D:\data1_Q&qtr.&year..txt" dsd dlm= '|' truncover ;
  length id $16 fico dt_first_pi 8 ;
  input fico dt_first_pi id ;
run;
proc sort data=data1; by id; run;

data data2;
  infile "D:\data2_Q&qtr.&year..txt" dsd dlm= '|' truncover ;
  length id $16 period actual_loss 8 ;
  input id period actual_loss ;
run;
proc sort data=data2; by id; run;

data result_q&qtr.&year. ;
  merge data1 data2 ;
  by id;
run;
%end;
%end;
%mend read ;

Then you could call it like this to generate 64 separate datasets.

%read(1999,2014)

But you probably will really want to have those 64 dataset combined into one so that you can use it more easily for your next steps. You could probably fix the process that reads the data to generate it all at once, but here is a simple data step to combine any dataset that starts with RESULT_ like the macro above generates and combine them into a single dataset.

data want ;
  length year qtr 8 dsname $41 ;
  set result_: indsname=dsname ;
  year = input(substr(scan(dsname,-1,'.'),9),4.);
  qtr  = input(substr(scan(dsname,-1,'.'),8),1.);
run;