BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
deblee73
Calcite | Level 5

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

 

deblee73_0-1692376881362.png

Thank you.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

7 REPLIES 7
Tom
Super User Tom
Super User

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?

ballardw
Super User

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;
deblee73
Calcite | Level 5

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. 

ballardw
Super User

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.

deblee73
Calcite | Level 5

Perfection.  Thanks for sharing your knowledge.  Take Care!

Reeza
Super User

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

 

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

 

  • 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)

 

deblee73
Calcite | Level 5

Thank you for the link to the documentation. 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1271 views
  • 0 likes
  • 4 in conversation