The SAS Output Delivery System and reporting techniques

Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Reply
Occasional Contributor
Posts: 6

Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Hi everyone,

 

I am trying to export results from a PROC PRINT step, and mulitple PROC MEANS steps (analyses of different class variables) to individual Excel worksheets within an Excel workbook. I would also like to include output from multiple procedures to a single worksheet. For example, I would like to export output from a PROC MEANS step and results from a PROC SGPLOT step (to build a histogram with the variable from the VAR statement in the proc means step) to a single Excel worksheet. 

 

I have tried variations of ODS and PROC EXPORT steps but have not been able to get the worksheets exactly how I want them. 

 

Thanks,

Eddie

Respected Advisor
Posts: 3,060

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

If you taking output from various procedures, including those that don't produce datasets then ODS is the way to go. What ODS methods have you tried? I'm guessing you may be using the EXCELXP tagset. If this is the case then you need to put your multiple procs inside a single ODS file creation step, assuming in this case everything goes in the same sheet:

 

ods tagsets.excelxp file="MyExcel.xml" options(sheet_interval="NONE");

proc print;
run;

proc means;
run;

proc print;
run;

ods tagsets.excelxp close;

I suggest you start with something simple like the above and then enhance it.

 

Occasional Contributor
Posts: 6

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Thanks! I tried something similar. Do I have to use the XML extension? And I can go aheah and include the sheet_name option after the sheet_interval option?

 

Eddie

Respected Advisor
Posts: 3,060

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

If you use the XML extension then you avoid being prompted by Excel when double clicking on the file to open it.

 

Yes you can include a sheet name. If you add a new ODS statement with a new sheet name part way through your PROC steps then following steps will go into the new sheet / tab.

Occasional Contributor
Posts: 6

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Thanks. Can you walk me through the ODS statement in the following code?
Especially the ODS statement leading up to the PROC MEANS step? The PROC
MEANS output is being appended to the first (and only) sheet rather than on
its own worksheet.

ods excel file="M:\CLIENT SERVICES\Employee Folders\Eddie\Statistics\Dual
Feed Raw Data.xlsx"
options(sheet_interval="none"
sheet_name="Dual Feed Raw Data");

ods text="Dual Feed Raw Data";
proc print data=work.report2;
run;

ods excel file="M:\CLIENT SERVICES\Employee Folders\Eddie\Statistics\Dual
Feed Raw Data.xlsx"
options(sheet_interval="none"
sheet_name="Average Total Attribution");
ods text="Average Total Attribution";
proc means data=work.report2;
var Total_Attributed;
run;

Thanks,
Eddie

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Grand Advisor
Posts: 17,316

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

This code wouldn't work as is. Here's a draft, untested that would be closer.

 

ods excel file="M:\CLIENT SERVICES\Employee Folders\Eddie\Statistics\Dual
Feed Raw Data.xlsx"
options(sheet_interval="none"
sheet_name="Dual Feed Raw Data");

title "Dual Feed Raw Data";
proc print data=work.report2;
run;


options(sheet_interval="none"
sheet_name="Average Total Attribution");
title "Average Total Attribution";
proc means data=work.report2;
var Total_Attributed;
run;

ods excel close;

There's still a bug about getting sheet interval properly set up, see this post:

 

https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-excel-amp-multiple-sheets/m-p/261953/highl...

Grand Advisor
Posts: 10,204

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

ODS TAGSETS.Excelxp with the options (sheet_interval= ). Insert statement with just ODS TAGSETS.EXCELXP OPTIONS(SHEET_INTERVAL= ); to change behavior at that point in the process. us NONE before procedures going to same page; PROC to send next procedure to new sheet. May be disappointed with SGPLOT output though as Excelxp is not for images.
Occasional Contributor
Posts: 6

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Thanks for replying. 

 

This is what I have. Can I use to the xlsx, csvall, or xls extension instead of xml? And how could I add a histogram to an Excel worksheet that already includes PROC MEANS output? Thanks, Eddie

 

ods tagsets.excelxp file="M:\CLIENT SERVICES\Employee Folders\Eddie\Statistics.xml"
options (sheet_interval="NONE");

proc print data=work.report2;
run;

proc means data=work.report2;
var Total_Attributed;
run;

ods tagsets.excelxp close;

Grand Advisor
Posts: 17,316

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Last time I checked Tagsets doesn't support graphics. 

SAS Super FREQ
Posts: 8,717

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

Hi:
Reeza is correct. ODS TAGSETS.EXCELXP, ODS CSV, ODS CSVALL do not support graphics inclusion. Only ODS MSOFFICE2K (an HTML file) or ODS TAGSETS.MSOFFICE2K_X (also an HTML file) or ODS EXCEL (with 9.4) will allow you to include graphics.

cynthia
Grand Advisor
Posts: 17,316

Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook

If you have SAS 9.4+ you can use ODS Excel. The options are similar to Tagsets.excelxp. 

 

 

Ask a Question
Discussion stats
  • 10 replies
  • 512 views
  • 0 likes
  • 5 in conversation