topic Re: Usage of macro variables in data step and table name in SAS Programming

Usage of macro variables in data step and table name

Jonfra — Fri, 03 Aug 2012 10:30:50 GMT

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

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.

Re: Usage of macro variables in data step and table name

Amir — Fri, 03 Aug 2012 11:06:21 GMT

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.

Re: Usage of macro variables in data step and table name

Linlin — Fri, 03 Aug 2012 12:38:51 GMT

%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

Re: Usage of macro variables in data step and table name

Tom — Fri, 03 Aug 2012 14:12:21 GMT

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;

Re: Usage of macro variables in data step and table name

Jonfra — Mon, 06 Aug 2012 07:23:20 GMT

Thank you Tom! That explains it

Re: Usage of macro variables in data step and table name

Quentin — Mon, 06 Aug 2012 13:35:37 GMT

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;