I'd like to create tables with the use of a nested do loop. Each loop should initialize a macro variable, and the table created should have its name as a combination of the two. The outline of my code is:
%let names_a = name1 name2 name3;
%let names_b = no1 no2 no3;
%do i=1 %to 3;
%let var_1=%qscan(&names_a,&i,%str( ));
%do j=1 %to 3;
%let var_2=%qscan(&names_b,&j,%str( ));
data libname.&var_1.&var_2;
run;
%end
%end
So I'd like to have 9 tables in total, each with name name1no1, name1no2,....name3no3.
This won't work, but I figure it has something to do with qscan. I'm new to SAS programming and any help would deeply be appreciated.
Hi,
Try using a semicolon after each "%end", so that they become "%end;". Then it might be easier for you to restart your SAS session before trying the code again so that the statements are recognised as they should be.
I assume your code is within a macro function definition.
Regards,
Amir.
%let names_a = name1 name2 name3;
%let names_b = no1 no2 no3;
%macro test;
%do i=1 %to 3;
%let var_1=%scan(&names_a,&i,%str( ));
%do j=1 %to 3;
%let var_2=%scan(&names_b,&j,%str( ));
data work.&var_1.&var_2;
set sashelp.class;
run;
%end;
%end;
%mend test;
%test
log file:
NOTE: SAS initialization used:
real time 5.08 seconds
cpu time 1.37 seconds
1 %let names_a = name1 name2 name3;
2 %let names_b = no1 no2 no3;
3
4
5 %macro test;
6 %do i=1 %to 3;
7 %let var_1=%scan(&names_a,&i,%str( ));
8 %do j=1 %to 3;
9 %let var_2=%scan(&names_b,&j,%str( ));
10 data work.&var_1.&var_2;
11 set sashelp.class;
12 run;
13 %end;
14 %end;
15 %mend test;
16 %test
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME1NO1 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.09 seconds
cpu time 0.04 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME1NO2 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME1NO3 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME2NO1 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME2NO2 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME2NO3 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME3NO1 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME3NO2 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.NAME3NO3 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Yes. %QSCAN() is the problem (other than the typos that others have noted) use %SCAN() instead.
The quoting is preventing the two macro variable values from being joined into a single token.
For example this program will create two datasets named A and B instead of a single dataset named AB.
%let a=%quote(A);
%let b=%quote(B);
data &a.&b ;
x=1;
run;
Thank you Tom! That explains it
Another option is to %unquote() the values. SAS is supposed to unquote values automatically after the macro processor has done all of its work, but sometimes it doesn't work, so you need to add the unquoting yourself. One rule I was taught with the macro language is that if the SAS code looks good (from mprint) but you are getting errors, try %unquote.
%let a=%quote(A); %let b=%quote(B); data %unquote(&a.&b) ; x=1; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.