BookmarkSubscribeRSS Feed
ECHIA78
Calcite | Level 5

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

10 REPLIES 10
SASKiwi
PROC Star

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.

 

ECHIA78
Calcite | Level 5

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

SASKiwi
PROC Star

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.

ECHIA78
Calcite | Level 5
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. -##
Reeza
Super User

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

ballardw
Super User
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.
ECHIA78
Calcite | Level 5

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;

Reeza
Super User

Last time I checked Tagsets doesn't support graphics. 

Cynthia_sas
SAS Super FREQ
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
Reeza
Super User

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

 

 

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
  • 10 replies
  • 1194 views
  • 0 likes
  • 5 in conversation