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;
... View more
