topic Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet. in SAS Programming

Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

deblee73 — Fri, 18 Aug 2023 16:41:47 GMT

I have 1 SAS dataset of 8 unique customer IDs and want to export each unique ID to a separate Excel worksheet. I also want to add the same visible title to each worksheet. I've got code that does one or the other but not both.

/* create sample dataset*/

data sample;
input ID $ Name $;
datalines;
1 John
2 Jane
3 Michael
4 Emily
5 David
6 Sophia
7 James
8 Olivia
;
run;

/* code to export each ID to a different worksheet*/

proc sql;
select distinct ID into :category_list separated by ' '
from sample;
quit;
%macro export_to_excel;
%do i = 1 %to %sysfunc(countw(&category_list));
%let current_category = %scan(&category_list, &i);

proc export data=sample(where=(ID="&current_category"))
outfile="c:\TEMP\test22.xlsx"
dbms=excel replace;
sheet="&current_category";

run;
%end;
%mend;
%export_to_excel;
ods excel close;

/*code for exporting with format and 2 TITLE ROWS*/
/* export report */
ods escapechar="~";
ods excel file="c:\temp\test126.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.sample/*(obs=5) */spanrows

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

run;
ods excel close;

The desired outcome would be: one customer per worksheet

Thank you.

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

Tom — Fri, 18 Aug 2023 17:33:25 GMT

Why not just use the ODS EXCEL option that will embed the titles into the sheet instead of putting it into the print header area of the sheet?

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

ballardw — Fri, 18 Aug 2023 18:00:59 GMT

Here's where I would start:

BY groups are generally preferable to multiple subsets of data and calling a procedure multiple times.

I used proc print as you didn't provide much of a "report" because of the reduced data. Proc Report will do by groups but, as with Print, you would have to explicitly add the BY variable(s) to the column definitions to place them in the body of the report.

The system option NOBYLINE suppresses the default "By variable name= value" . Don't forget to reset the default behavior when done.

Note that the TITLE statement has a fair number of options for appearance and will appear with each table generated.

ODS TEXT appears only one time before the procedure output. The By group also allows use of the Sheet_interval to control output to one per sheet (in this case).

/* sort by ID if needed*/

options nobyline;
ods excel file="c:\temp\by_example.xlsx"
   options(sheet_interval='BYGROUP');
title1 color=green italic "BANKRUPTCY CASE NO.";
title2 color=green italic "FILED";
proc print data=sample noobs;
   by id;
   var id name;
run;
title;
ods excel close;

/*reset default byline behavior*/
options byline;

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

deblee73 — Fri, 18 Aug 2023 18:13:28 GMT

Thank you, both.

The first response I believe it what I need but not sure which of the two code samples to put it in and where in the code.

The second response does what I want except puts the titles in print preview rather than body of the sheet.

I made a simple dataset just for demonstration purposes.

I can live with the second one but the report I was asked to build had the titles in the body of the sheet as the first few rows.

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

ballardw — Fri, 18 Aug 2023 19:22:38 GMT

Look at the options for ODS Excel. One of them Embedded_titles='ON' should place the titles in the body of the sheet.

options(sheet_interval='BYGROUP' Embedded_titles='ON')

There's around 46 options. Please look some of them up on your own.

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

Reeza — Fri, 18 Aug 2023 19:42:05 GMT

The first example in the documentation covers everything in your question.

https://go.documentation.sas.com/doc/en/pgmsascdc/v_042/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm#n0bmw0v2sv7lepn17aovz1217r9k

A new worksheet is created for each BY group.
The BY lines are suppressed.
The title created by the TITLE statement is embedded in the output.
The worksheet labels are customized.
A blank worksheet with a custom tab is inserted after the last BY group. (not needed in your use case)

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

deblee73 — Fri, 18 Aug 2023 19:47:12 GMT

Perfection. Thanks for sharing your knowledge. Take Care!

Re: Export 1 SAS dataset to multiple Excel worksheets with same title on each worksheet.

deblee73 — Fri, 18 Aug 2023 19:49:41 GMT

Thank you for the link to the documentation.