topic How to use the distinct variable values as an array{} in SAS Procedures

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

Yurie — Fri, 21 Aug 2015 19:16:12 GMT

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.

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

LinusH — Fri, 21 Aug 2015 19:27:38 GMT

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

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

ballardw — Fri, 21 Aug 2015 19:33:03 GMT

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;

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

Yurie — Fri, 21 Aug 2015 19:40:38 GMT

Hi, Ballardw

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

--Yurie

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

Reeza — Fri, 21 Aug 2015 19:41:57 GMT

I think you're looking for something like this:

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

Reeza — Fri, 21 Aug 2015 19:43:37 GMT

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?