Subset data to new variables

[hanson4022]

I come across it pretty often where I do some work with a dataset in SAS, and want to use that dataset in a different program, but the other program wants the variables in a different format. For instance, I would have a SAS dataset like this:

DATA dataset1;

   INPUT location treatment $ count ;

   DATALINES;

1    Trt1    1

2    Trt1    2

3    Trt1    3

4    Trt1    4

5    Trt1    5

1    Trt2    10

2    Trt2    20

3    Trt2    30

4    Trt2    40

5    Trt2    50

1    Trt3    100

2    Trt3    200

3    Trt3    300

4    Trt3    400

5    Trt3    500

;

proc print data= dataset1;

run;

I want each value of the name variable in this data set to name a new column and the count variable information will be listed under each respective name variable/column . The end result should look like this:

DATA dataset2;

   INPUT location Trt1 Trt2 Trt3;

   DATALINES;

1    1    10    100

2    2    20    200

3    3    30    300

4    4    40    400

5    5    50    500

;

proc print data= dataset2;

run;

Now for small data sets I can rearrange the data by hand, or I can write a macro in Excel to rearrange large data sets. However, is there a way to do this entirely in SAS or convert dataset2 back to dataset1 as well? Doing so would be one less step at least when I need to jump between using data in SAS and another program, so any insight or procedures to look into how to handle this kind of data manipulation would be helpful. I haven't found any specific leads searching yet. Thanks.

[Reeza · Accepted solution]

There's a proc for that

Proc Transpose.

SAS Learning Module: How to reshape data long to wide using proc transpose

SAS Learning Module: How to reshape data wide to long using proc transpose