Export 1 SAS dataset to multiple Excel worksheets and add titles

[deblee73]

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.

[Tom · Accepted solution]

Use BY statement and ODS EXCEL option 

SHEET_INTERVAL='BYGROUP'

[deblee73]

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