How to do loop over a loop in SAS Macro?

[KS99]

Hi, greetings, 

If you experts could help me with this coding, I would really appreciate it. 

I have seven different datasets, whose names are USA, GBR, JPN, Ind_73, Ind_36, Ind_28, and Ind_60. 

(First three are countries, last four are industries).  

By using SAS Macro, I am trying to split each dataset into 18 sub-datasets (say, annually, from 2000~2017).  

So, I tried to put the names of the seven datasets into var1=, var2, ... var7, 

and using double do-loop (by year within by dataset) I tried to achieve this and export them into EXCEL. 

My codes run as follows: 

%put _global_;
%LET var1=USA; %LET var2=JPN; %LET var3=GBR; %LET var4=Ind_73; %LET var5=Ind_36;
%LET var6=Ind_28; %LET var7=Ind_60;
%MACRO Divide_year;
%DO i=1 %TO 7;
%DO j=2000 %TO 2017;
DATA &var&i&j; SET &var&i; IF Annc_fyear=&j; KEEP Source Target FIC SIC_2D; run;
PROC EXPORT data=&var&i&j dbms=xlsx
outfile="C:\Users\kchoi9\Desktop\Network and Finance\Data\Three_countries_four_industries\&var&i&j.xlsx"
replace; RUN;
%END; %END; %MEND Divide_year; %Divide_year; 

But I get a whole bunch of errors, first and foremost like the following: 

ERROR: File WORK.VAR1.DATA does not exist. 

How should I solve this? Please help. 

KS -, 

[Patrick · Accepted solution]

There will be warning messages in your log telling you that macro variables don't exists. The reason is the syntax in your do loops. 

If you want to address a macro variable &var2 where the numeric portion in the name also uses a macro variable then you need to pay attention on how SAS resolves things. So for the above example the name in the code should be: &&var&i

%LET var1=USA;
%LET var2=JPN;
%LET var3=GBR;
%LET var4=Ind_73;
%LET var5=Ind_36;
%LET var6=Ind_28;
%LET var7=Ind_60;

%MACRO Divide_year;
  %DO i=1 %TO 7;
    %DO j=2000 %TO 2017;

      DATA &&var&i.._&j;
        SET &&var&i;

        IF Annc_fyear=&j;
        KEEP Source Target FIC SIC_2D;
      run;

      PROC EXPORT data=&&var&i.._&j dbms=xlsx
        outfile="C:\Users\kchoi9\Desktop\Network and Finance\Data\Three_countries_four_industries\&&var&i.._&j..xlsx"
        replace;
      RUN;

    %END;
  %END;
%MEND Divide_year;

%Divide_year;

You could also consider to make things a bit simpler for you using a code variation as below.

%MACRO Divide_year(inds);
  %DO j=2000 %TO 2017;

    DATA &inds._&j;
      SET &inds;

      IF Annc_fyear=&j;
      KEEP Source Target FIC SIC_2D;
    run;

    PROC EXPORT data=&inds._&j dbms=xlsx
      outfile="C:\Users\kchoi9\Desktop\Network and Finance\Data\Three_countries_four_industries\&inds._&j..xlsx"
      replace;
    RUN;

  %END;
%MEND Divide_year;

%Divide_year(USA);
%Divide_year(JPN);
%Divide_year(GBR);
%Divide_year(Ind_73);
%Divide_year(Ind_36);
%Divide_year(Ind_28);
%Divide_year(Ind_60);

If there would be more macro calls then you could also use a SAS data _null_ step with call execute() to generate and execute the macro calls.

[KS99]

Thank you Patrick! 

Both sets of codes work perfectly! 

Now I see was the hidden thorn bothering me for years. 

&&var&i.._&j;

 The double ampersand and two dots connecting macro indices.. 

Now I can deal with Macro. 

Have a nice weekend! 

Sincerely, 

KS, 

[Patrick]

You can find this documented under below links.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n0v... 

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n07...

Each iteration for resolving an ampersand will consume a period. That's why you sometimes need several periods when dealing with multiple ampersands.

[PaigeMiller]

Does data set Var1 exist? When SAS says it doesn't exist, then I believe SAS. Show us evidence that VAR1 exists (screen capture is okay).