BookmarkSubscribeRSS Feed
Fluorite | Level 6

Hello, I have a data set with about 4000 observations and about 100 distinct types in the variable type column. I need to print each type of data to one excel file with different worksheets and the worksheet name is the type name.

For example, I have

data dset missover;

input id type$ rev1 rev2 notes$;


   1 a 20 10 yet

   2 b 10 15 yes

   3 c 15 12 no

   4 b 10 10  .

   5 a 10  . yet

   6 d 10  2 ok

   7 e 5   5 but

   8 g 20 30 one

   9 k 5  6  three

  10 g 8  9  one 



So the distinct types are: a, b, c, d, e, g, k in this example

I expect to print one excel or csv file with

worksheet name: a

id type rev1 rev2 notes

1  a     20    10    yet

5  a     10      .    yet

worksheet name: b

id type rev1 rev2 notes

2  b     10    15    yes

4  b     10     10   

worksheet name: c

id type rev1 rev2 notes

3  c     15    12    no


and so on......

I expect to use the distinct type names as an array when I print each worksheet, since the types will be changed each time and there are more than 100 types when I do the report.

Any suggestions, solutions, or hints will be very appreciated.

Tourmaline | Level 20

Sounds overly complicated to use an array. Looks like you just got a list report. Sort your data and proc print by? (Not tested)

Data never sleeps
Super User

This might give you a working start. It creates an XML file that Excel can open. I use the xml extension to avoid the Excel name doesn't match file extension warnings.

The tabs are named Type=a, Type=B instead of just a, b etc.

proc sort data=dset; by type;run;

ods tagsets.excelxp file="C:\data\types.xml"
options (sheet_interval='Bygroup') style=meadow;

proc print data=dset noobs label;
by type;

ods tagsets.excelxp close;

Fluorite | Level 6

Hi, Ballardw

Thank you so, so much! The result  is what I wanted!


Super User

I think you're looking for something like this:

Super User

Actually, if its one workbook then @ballardw solution is correct, use the SHEET_INTERVAL option in ODS TAGSETS.EXCELXP

Also, I don't know how that would be organized as CSV, would it be one group per CSV file so multiple CSV files?



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4 in conversation