BookmarkSubscribeRSS Feed
Yurie
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$;

cards;

   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 

;

run;

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.

5 REPLIES 5
LinusH
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
ballardw
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;
run;

ods tagsets.excelxp close;

Yurie
Fluorite | Level 6

Hi, Ballardw

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

--Yurie

Reeza
Super User

I think you're looking for something like this:

Reeza
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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 5 replies
  • 947 views
  • 0 likes
  • 4 in conversation