How can I send a PROC PRINT that has BY Groups to ODS EXCEL and have it stay on one sheet?
I need to create an Excel report that looks like a PROC PRINT with By Groups, and a Sum.
Input SAS dataset looks like this:
Region State Count
------------------------------------------------
EASTERN GA 5
EASTERN FL 4
EASTERN AL 3
WESTERN CA 7
WESTERN OR 8
WESTERN WA 9
Spreadsheet needs to look like this:
Region State Count
------------------------------------------------
EASTERN GA 5
FL 4
AL 3
WESTERN CA 7
OR 8
WA 9
--------------------------------------------
Grand Total 36
I need the BY and ID in the following code to get the region to look right, but using the BY causes multiple sheets.
(SAS Release 9.04.01M2P072314)
ods excel file="&OUTPUT_PATH.\&OUTPUT_FILE._&yyyymmdd..xlsx" options ( /* Display options */ sheet_name="OUTPUT_RPT" absolute_column_width='40,6,7' embedded_titles='on') ; run; proc print data=cons_rpt noobs sumlabel label; var Region State Count ; by Region; id Region; label Region='Grand Total' ; sum count; run; ods excel close; run;
(Please don't make me use Tagsets.ExcelXP)
Thanks!
Try the SHEET_INTERVAL option in your options list, set it to none.
Sheet_Interval='none'
Try the SHEET_INTERVAL option in your options list, set it to none.
Sheet_Interval='none'
You should consider PROC tabulate.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.