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 -,
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.
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.
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,
You can find this documented under below links.
Each iteration for resolving an ampersand will consume a period. That's why you sometimes need several periods when dealing with multiple ampersands.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.