Using a macro to export to excel freq tables and assign sheet name by categorical variable

[Kaeslock223_]

I'm trying to run a code that will print frequency tables showing demographics of each health condition by different hospitals and export it to excel. The excel export would be one file with several sheets named after each hospital. The code I created is below. In the log, there is nothing indicating an error except an exclamation point on the macro line so I am unsure how to go about solving it. Any help would be great!

Code:

%macro export_freq_excel(data=, var=, outpath=);

/* Error handling */
%if %sysevalf(%superq(data)=, boolean) %then %do;
%put ERROR: Data parameter not specified.;
%return;
%end;

%if %sysevalf(%superq(var)=, boolean) %then %do;
%put ERROR: Variable parameter not specified.;
%return;
%end;

%if %sysevalf(%superq(outpath)=, boolean) %then %do;
%put ERROR: Output path parameter not specified.;
%return;
%end;

/* Initialize macro variables */
%local var_value hosp_list i;
%let i=1;

/* Get distinct values of the variable */
proc sql noprint;
select distinct &var into :hosp_list separated by ' '
from &data
where &var ne ' '
order by &var;
quit;

/* Loop over variable values */
%do %while (%scan(&hosp_list, &i) ne %str());
%let var_value=%scan(&hosp_list, &i);

/* Create frequency table */
proc freq data=&data;
where &var="&var_value";
table dxcodes_1-dxcodes_9*(sex_ext race_ext agecat)/list nocum out=freq_table;
run;

/* Write table to Excel file */
%let outfilename=&outpath.\freq_&var._&var_value..xlsx;
ods excel(file=&outfilename) options(sheet_name="&var_value");
proc print data=freq_table noobs;
run;
ods excel(close);

%let i=%eval(&i+1);
%end;

/* Cleanup */
%let var_value=;
%let hosp_list=;
%let i=;
%mend;

Log:

121! %macro export_freq_excel(data=hospdemo,var=ChronHosp,outpath=S:\Informatics\Hospital Demographics);
122
123 %local i var_value hosp_list;
124 %macro export_freq_excel(data=hospdemo,var=ChronHosp,outpath=S:\Informatics\Hospital Demographics);
125 %macro export_freq_excel(data=, var=, outpath=);
126
127 /* Error handling */
128 %if %sysevalf(%superq(data)=, boolean) %then %do;
129 %put ERROR: Data parameter not specified.;
130 %return;
131 %end;
132
133 %if %sysevalf(%superq(var)=, boolean) %then %do;
134 %put ERROR: Variable parameter not specified.;
135 %return;
136 %end;
137
138 %if %sysevalf(%superq(outpath)=, boolean) %then %do;
139 %put ERROR: Output path parameter not specified.;
140 %return;
141 %end;
142
143 /* Initialize macro variables */
144 %local var_value hosp_list i;
145 %let i=1;
146
147 /* Get distinct values of the variable */
148 proc sql noprint;
149 select distinct &var into :hosp_list separated by ' '
150 from &data
151 where &var ne ' '
152 order by &var;
153 quit;
154
155 /* Loop over variable values */
156 %do %while (%scan(&hosp_list, &i) ne %str());
157 %let var_value=%scan(&hosp_list, &i);
158
159 /* Create frequency table */
160 proc freq data=&data;
161 where &var="&var_value";
162 table dxcodes_1-dxcodes_9*(sex_ext race_ext agecat)/list nocum out=freq_table;
163 run;
164
165 /* Write table to Excel file */
166 %let outfilename=&outpath.\freq_&var._&var_value..xlsx;
167 ods excel(file=&outfilename) options(sheet_name="&var_value");
168 proc print data=freq_table noobs;
169 run;
170 ods excel(close);
171
172 %let i=%eval(&i+1);
173 %end;
174
175 /* Cleanup */
176 %let var_value=;
177 %let hosp_list=;
178 %let i=;
179 %mend;

[Tom · Accepted solution]

You only showed the log for the compilation (definition) of the macro.

You do not show any code that actually tried to call the macro.

But why do you need use a macro (or any type of code generation) to do this?

Why not just use BY statement?  You can adjust the name of the sheet using the value of the BY variable.

Something like:

proc freq data=&data;
  table &var*(dxcodes_1-dxcodes_9)*(sex_ext race_ext agecat)/list nocum out=freq_table;
run;

options nobyline;
ods excel(file=&outfilename)  options(sheet_name="#byval(&var)");
proc print data=freq_table noobs;
  by &var;
run;
options byline;
ods excel(close);