topic Re: Exporting SAS Procedures to Multiple Excel Worksheets within a Singe Excel Workbook in ODS and Base Reporting

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

ECHIA78 — Wed, 01 Jun 2016 19:35:43 GMT

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

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

SASKiwi — Wed, 01 Jun 2016 20:07:31 GMT

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.

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

ballardw — Wed, 01 Jun 2016 20:10:38 GMT

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.

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

Reeza — Wed, 01 Jun 2016 20:44:30 GMT

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

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

ECHIA78 — Wed, 01 Jun 2016 20:50:54 GMT

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

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

ECHIA78 — Wed, 01 Jun 2016 20:53:14 GMT

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;

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

Reeza — Wed, 01 Jun 2016 21:34:20 GMT

Last time I checked Tagsets doesn't support graphics.

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

SASKiwi — Wed, 01 Jun 2016 21:38:05 GMT

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.

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

ECHIA78 — Wed, 01 Jun 2016 22:06:06 GMT

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. -##

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

Cynthia_sas — Wed, 01 Jun 2016 22:45:04 GMT

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

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

Reeza — Wed, 01 Jun 2016 22:45:19 GMT

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/highlight/true#M15551