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!
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.
%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;
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;
data _null_;
input value $20.;
call symputx(cats("variable",_n_),value);
datalines;
age
race
country
marital_status
;
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
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 : ¯oVars1.;
%put Last Macro Variable : &¯oVars&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!
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 25. 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.