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="¤t_category"))
outfile="c:\TEMP\test22.xlsx"
dbms=excel replace;
sheet="¤t_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.
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.
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?
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;
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.
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.
Perfection. Thanks for sharing your knowledge. Take Care!
The first example in the documentation covers everything in your question.
Thank you for the link to the documentation.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.