Export 1 SAS dataset to multiple Excel worksheets and add titles

deblee73 — Thu, 17 Aug 2023 23:49:12 GMT

HI. I am trying to use a combination of PROC EXPORT and PROC REPORT.

I have 1 SAS dataset and I'd like to export that to Excel into multiple worksheets based on customer ID (one worksheet per customer). I also want to add the same title to each worksheet. Seems I cannot add titles using PROC EXPORT and I cannot figure out how to breakout my dataset into multiple worksheets using PROC REPORT.

Here is the code for exporting to multiple worksheets:

proc sql;

select distinct ID into :category_list separated by ' '

from SUMMARY;

quit;

/* WORKS FOR OUTPUTTING TO SEPERATE WORKSHEETS */

%macro export_to_excel;

%do i = 1 %to %sysfunc(countw(&category_list));

%let current_category = %scan(&category_list, &i);

proc export data=SUMMARY(where=(ID="&current_category"))

outfile="c:\users\my_name\documents\test.xlsx"

dbms=excel replace;

sheet="&current_category";

run;

%end;

%mend;

Here is the code for exporting with titles:

/*WORKS FOR EXPORTING WITH FORMAT AND 3 TITLE ROWS*/
%let rundate = %SYSFUNC(today(),yymmddn8.);

/* export report */
ods escapechar="~";
ods excel file="c:\users\my_name\documents\test..xlsx" options(sheet_name="Sheet1");
ods text="~{style[color=green fontstyle=italic] BANKRUPTCY CASE NO.";
ods text="~{style[color=green fontstyle=italic] FILED";

proc report data=work.SUMMARY/*(obs=5) */spanrows

style(report)={pretext=" "};

run;
ods excel close;

How can I combine the two? 1 SAS dataset exported to 1 Excel workbook with 1 worksheet per customer with a title.

Thanks.

Re: Export 1 SAS dataset to multiple Excel worksheets and add titles

Tom — Fri, 18 Aug 2023 00:27:47 GMT

Use BY statement and ODS EXCEL option

SHEET_INTERVAL='BYGROUP'

Re: Export 1 SAS dataset to multiple Excel worksheets and add titles

deblee73 — Fri, 18 Aug 2023 00:55:37 GMT

Yea!!! It works. I will sleep tonight. Thank you.

topic Re: Export 1 SAS dataset to multiple Excel worksheets and add titles in SAS Programming

Export 1 SAS dataset to multiple Excel worksheets and add titles

Re: Export 1 SAS dataset to multiple Excel worksheets and add titles

SHEET_INTERVAL='BYGROUP'

Re: Export 1 SAS dataset to multiple Excel worksheets and add titles