06-01-2016 03:35 PM
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.
06-01-2016 04:07 PM
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.
06-01-2016 04:50 PM
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?
06-01-2016 05:38 PM
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.
06-01-2016 06:06 PM
06-01-2016 06:45 PM
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:
06-01-2016 04:10 PM
06-01-2016 04:53 PM
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"
proc print data=work.report2;
proc means data=work.report2;
ods tagsets.excelxp close;
06-01-2016 06:45 PM