topic Re: How to do loop over a loop in SAS Macro? in SAS Programming

How to do loop over a loop in SAS Macro?

KS99 — Sat, 10 Apr 2021 23:54:38 GMT

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 -,

Re: How to do loop over a loop in SAS Macro?

Patrick — Sun, 11 Apr 2021 00:31:37 GMT

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.

Re: How to do loop over a loop in SAS Macro?

PaigeMiller — Sun, 11 Apr 2021 00:22:27 GMT

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

Re: How to do loop over a loop in SAS Macro?

KS99 — Sun, 11 Apr 2021 03:40:46 GMT

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,

Re: How to do loop over a loop in SAS Macro?

Patrick — Sun, 11 Apr 2021 06:37:14 GMT

You can find this documented under below links.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n0vl7e3du5f7cjn1g4ajozqxe6b6.htm&locale=en

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=mcrolref&docsetTarget=n0700fspmubii5n1vecutttwz93n.htm&locale=en

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