Hello SAS experts, I need to export multiple data sets to single excel spreadsheet by using ods excelxp SAS 9.3. When I ran my code, SAS output my each table to one spreadsheet. I need the second loop tables on one sheet. Any helps are appreciated. Here is my code: data varlist; input name$ 20. ; datalines; Survey_1 Survey_2 Survey_3 Survey_4 Survey_5 Survey_6 Survey_7 Survey_8 Survey_9 ; run; proc sql noprint; select name into :var1 - :var9 from varlist; quit; data vlist; input name$ 15. ; datalines; Life Health Property Casualty ; run; proc sql noprint; select name into :v1 - :v4 from vlist; quit; ODS noresults; ODS listing close; /*Turn off the standard line printer destination*/ ods tagsets.ExcelXP path="&dir." file="Testreport_2017.xml" style=seaside /*Styles to control appearance of output*/; %macro exceloutput; %do i=1 %to 4; ods tagsets.ExcelXP options ( sheet_name= "&&v&i." autofit_height= 'yes' Sheet_interval="none" absolute_column_width= '8,8,8,8,8,8,8,8'); title j=l "All "; options missing=" "; proc report data=allpass(where=(examtitle="&&v&i.")) NOWD; Column group total ("Overall" npass passpct) ("General" gpass gpasspct) ("State" spass spasspct); define group /Center display "Group"; define total / center display "Total"; define npass / center display "N" ; define passpct /center display "PCT"; define gpass /center display "N"; define gpasspct /center display "PCT" ; define spass /center display "N"; define spasspct /center display "PCT" ; compute group; count+1; if (mod(count,2)) then do; call define(_row_,"style","style=[background=CXECEDEC]"); end; endcomp; run; %do j=1 %to 9; ods tagsets.ExcelXP options ( sheet_name= "&&v&i." autofit_height= 'yes' Sheet_interval="none" absolute_column_width= '8,8,8,8,8,8,8,8' ); options missing=" "; title "&&var&j."; proc report data=surveypass&j.(where=(examtitle="&&v&i.")) NOWD; Column &&var&j. total ("Overall" npass passpct) ("General" gpass gpasspct) ("State" spass spasspct); define &&var&j. /Center display "Group"; define total / center display "Total"; define npass / center display "N" ; define passpct /center display "PCT" style(column)={tagattr="format:###0.00%"}; define gpass /center display "N"; define gpasspct /center display "PCT" style(column)={tagattr="format:###0.00%"}; define spass /center display "N" ; define spasspct /center display "PCT" style(column)={tagattr="format:###0.00%"};; compute total; count+1; if (mod(count,2)) then do; call define(_row_,"style","style=[background=CXECEDEC]"); end; endcomp; run; %end; ods tagsets.excelxp options(sheet_interval="table" sheet_name=&&v&i. ); %end; %mend; %exceloutput; ods tagsets.excelxp close; /* Close and release the xml file so it can be opened with Excel*/ ODS listing;
... View more
