topic Re: Creating a list of macro variables for a large list of variables in SAS Programming

Creating a list of macro variables for a large list of variables

Emma_at_SAS — Fri, 04 Dec 2020 18:32:39 GMT

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!

Re: Creating a list of macro variables for a large list of variables

ballardw — Fri, 04 Dec 2020 18:48:11 GMT

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.

Re: Creating a list of macro variables for a large list of variables

Kurt_Bremser — Fri, 04 Dec 2020 18:49:21 GMT

data _null_;
input value $20.;
call symputx(cats("variable",_n_),value);
datalines;
age
race
country
marital_status
;

Re: Creating a list of macro variables for a large list of variables

Reeza — Fri, 04 Dec 2020 18:58:47 GMT

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!

Re: Creating a list of macro variables for a large list of variables

Emma_at_SAS — Fri, 04 Dec 2020 19:11:06 GMT

%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;

Re: Creating a list of macro variables for a large list of variables

Emma_at_SAS — Fri, 04 Dec 2020 19:12:10 GMT

the desired variable names reside in a data set.

Thank you!

Re: Creating a list of macro variables for a large list of variables

Emma_at_SAS — Mon, 07 Dec 2020 18:29:28 GMT

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

Re: Creating a list of macro variables for a large list of variables

Tom — Mon, 07 Dec 2020 18:40:16 GMT

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;

Re: Creating a list of macro variables for a large list of variables

Reeza — Mon, 07 Dec 2020 21:59:52 GMT

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=p0xlnvl46zgqffn17piej7tewe7p.htm&locale=en