BookmarkSubscribeRSS Feed
daisy6
Quartz | Level 8

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;

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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
Obsidian | Level 7

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
Quartz | Level 8

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7290 views
  • 0 likes
  • 4 in conversation