How to use the distinct variable values as an array{}

[Yurie]

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.

[LinusH]

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

[ballardw]

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]

Hi, Ballardw

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

--Yurie

[Reeza]

I think you're looking for something like this:

[Reeza]

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?