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
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.
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
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.
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:
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;
Last time I checked Tagsets doesn't support graphics.
If you have SAS 9.4+ you can use ODS Excel. The options are similar to Tagsets.excelxp.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.