Multiple tables export to one excel spreadsheet

[daisy6]

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;

[ChrisNZ]

Simplify your code until it works, and then add complexity until it breaks.

As a starting point, this works fine:

ods tagsets.ExcelXP path="%sysfunc(pathname(work))"  file="Testreport_2017.xml";

ods tagsets.ExcelXP options ( sheet_name= "F" );

proc print data=SASHELP.CLASS(where=(SEX='F')) ;run;

ods tagsets.ExcelXP options ( sheet_name= "M" );
                                                                         
proc print data=SASHELP.CLASS(where=(SEX='M')) ;run;

ods tagsets.ExcelXP close; 

[MINX]

You can just simply use libname to refer excel file as a library. Then do multiple DATA step to different excel spreadsheet.

libname myxls "\\path\filename.xls"; 

DATA myxls.sheetname1;
 SET sasdataset1;
 DATA myxls.sheetname2;
 SET sasdataset2;
 RUN;

proc datasets lib=myxls; quit;
 
libname myxls CLEAR;

[daisy6]

Thanks for help! I will try to simplify the code and run it.

[Tom]

Do you want to output multiple tables into a single sheet as your title implies?

Or do you want multiple sheets in the same workbook as it looks like the code is trying to do?

If the latter then can't you just tell ODS to do that automatically by adding a BY statement and setting the SHEET_INTERVAL option?

[daisy6]

Thanks Tom. I like my multiple tables are written on one spreadsheet of excel, not multiple sheets or tabs of excel. My code works for 9.4 version but not for 9.3 version.