BookmarkSubscribeRSS Feed
Emma_at_SAS
Lapis Lazuli | Level 10

I have a large list of variables that I want to use in a loop as *&&variable&i

Therefore, I want to introduce them as macro variables

%let variable1 = age;

%let variable2 = race;

%let variable3 = country;

%let variable4 = marital_status;

and so on...

I do not want to type "%let variable1", "%let variable2", "%let variable3" before each variable name because my list is so long.

How may I write a code to receive the name of the variables and create the %let variable1 = before each variable.

 

Thanks! 

8 REPLIES 8
ballardw
Super User

Where does the list about the desired variable names reside? In a data set? a single macro variable? if so what does that macro variable look like?

 

It may also be a good idea to provide how you intend to use the &&variable&i . Sometimes there are clearer ways than the indirect reference.

Emma_at_SAS
Lapis Lazuli | Level 10

%let numvar = 100;

%let variable1 = age;

%let variable2 = race;

%let variable3 = country;

%let variable4 = marital_status;

*I have to add 96 more variables in this list for macro variables;

 

*I want to create a loop for proc freq and create excel output;

%macro freq_table;

ods excel file="\\C:\output_frequencies.xlsx";

%do i = 1 %to &numvar; *this will go from 1 to 100;

ods excel options(embedded_titles='on' embedded_footnotes='on' sheet_name="&&variable&i" sheet_interval = 'proc');

proc freq data = my_data_set varheader = namelabel nosummary;

                tables  X1*V2*&&variable&i ;

run;

%end;

ods excel close;

%mend;

%freq_table;

Tom
Super User Tom
Super User

If you have the list of variables in a dataset then there is no need for macro variables or macro code at all to do what you want.

Let's assume you have dataset named CONTENTS with a variable named NAME.

So you could use a data step to generate a separate PROC step for each observation in CONTENTS.  You could use CALL EXECUTE, but I find it easier and clearer to write the code to a file and then %INCLUDE the file.  Then you can use PUT statement options like using the $QUOTE format to quoted the NAME when defining the SHEET_NAME.

ods excel file="\\C:\output_frequencies.xlsx"
 options (embedded_titles='on' embedded_footnotes='on' sheet_interval='proc')
;
filename code temp;
data _null_;
  set contents;
  file code;
  put 'excel options (sheet_name=' name :$quote. ');'
    / 'proc freq data=my_data_set varheader=namelabel nosummary;'
    / '  tables X1*V2*' name ';'
    / 'run;'
  ;
run;
%include code / source2;
ods excel close;
Emma_at_SAS
Lapis Lazuli | Level 10
the desired variable names reside in a data set.

Thank you!
Emma_at_SAS
Lapis Lazuli | Level 10

Thank you Reeza for your suggested code. May you please add some notes to it and clarify what each part does? It is above my level of expertise and I cannot use it!  

 

Thank you

Reeza
Super User
I'd actually recommend you run it, add your own comments to what you think is happening and I'll be happy to help. Running it is the best way to learn from it. Comments in this particular case won't help much.

If you need instructions on the basics of working with macro variables using SQL I recommend the following:
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=sqlproc&docsetTarget=p0xl...
Reeza
Super User

Assuming you want the list of variables from a data set, I would recommend the following approach. Change your library and data set name to get the relevant variables. 

 

proc sql noprint;
select name into :macroVars1- 
from sashelp.vcolumn
where libname = "SASHELP" and memname = "CLASS"
and upper(name) not in ('NAME', 'SEX');
quit;

*Number of observations stored in automatic macro variables;
%put Number of Macro Variables created : &sqlObs;

*Check assignments;
%put First Macro Variable : &macroVars1.;
%put Last Macro Variable : &&macroVars&sqlObs;

@Emma_at_SAS wrote:

I have a large list of variables that I want to use in a loop as *&&variable&i

Therefore, I want to introduce them as macro variables

%let variable1 = age;

%let variable2 = race;

%let variable3 = country;

%let variable4 = marital_status;

and so on...

I do not want to type "%let variable1", "%let variable2", "%let variable3" before each variable name because my list is so long.

How may I write a code to receive the name of the variables and create the %let variable1 = before each variable.

 

Thanks! 


 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 905 views
  • 3 likes
  • 5 in conversation