topic Multiple tables export to one excel spreadsheet in ODS and Base Reporting

Multiple tables export to one excel spreadsheet

daisy6 — Wed, 06 Sep 2017 14:22:55 GMT

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;

Re: Multiple tables export to one excel spreadsheet

ChrisNZ — Fri, 08 Sep 2017 03:01:24 GMT

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;

Re: Multiple tables export to one excel spreadsheet

MINX — Fri, 08 Sep 2017 19:04:01 GMT

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;

Re: Multiple tables export to one excel spreadsheet

daisy6 — Fri, 08 Sep 2017 19:44:01 GMT

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

Re: Multiple tables export to one excel spreadsheet

Tom — Fri, 08 Sep 2017 20:35:57 GMT

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?

Re: Multiple tables export to one excel spreadsheet

daisy6 — Wed, 13 Sep 2017 16:33:34 GMT

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.