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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.