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