Proc Transpose Help

[jacob_klimek]

Hello,

I have data that looks like :

ID     TYPE      ANSWER_1     ANSWER_2

1          A                4                      5

1          B                6                      8

1          C                3                      1

2          A                5                      5

2          B                11                    3

2          C                0                      7

And I want it to look like:

ID         a         b        c

1          4          6       3

2          5          11     0

I have been trying to mess with proc transpose, but the closest I have gotten has been:

proc transpose data=have out=want;

        var   ANSWER_1 ANSWER_2;

        id type;

        by id;

run;

This gets close, but it doesn't make the ANSWER_1 and ANSWER_2 wide.

Any help would be greatly appreciated.

[Reeza · Accepted solution]

Your want dataset doesn't include Answer2 either...

I can make a guess as this is what you want, so basically you need to run proc transpose twice and merge the results.

proc transpose data=have out=answer1 prefix=A1_;
by id;
id type;
var answer_1;
run;

proc transpose data=have out=answer2 prefix=A2_;
by id;
id type;
var answer_2;
run;

data want;
merge answer1 answer2;
by id;
run;

[Reeza]

If you'd like a more automated way consider the macro here:

http://www.sascommunity.org/mwiki/images/b/be/BB-07-2013.sas

Here's how it would look for your data:

%transpose(libname_in=work,
                 libname_out=work,
                 data=have,
                 out=want,
                 by=id,
                 prefix=,
                 var=answer_1 answer_2,
                 autovars=,
                 id=type,
                 descendingid=,
                 var_first=Yes,
                 format=,
                 delimiter=,
                 copy=,
                 drop=,
                 sort=,
                 sort_options=,
                 use_varname=,
                 preloadfmt=,
                 guessingrows=,
                 newid=);

[YZ89]

data Have;

input ID TYPE : $1. ANSWER_1 ANSWER_2;

datalines;

1 A 4 5

1 B 6 8

1 C 3 1

2 A 5 5

2 B 11 3

2 C 0 7

;

run;

proc transpose data=have out=want;

var ANSWER_1 ANSWER_2;

id type;

by id;

run;

Data a1 a2;

set Want;

if _name_='ANSWER_1' then output a1;

else output a2;

drop _name_;

run;

proc sort data=a1;

by ID;

;

run;

proc sort data=a2;

by ID;

run;

data Want;

merge a1 a2 (rename=(A=A2 B=B2 C=C2));

by ID;

run;

**** Or: ***;

data Want (keep=ID A1-A2 B1-B2 C1-C2);

label ID='ID'

A1='A-1'

B1='B-1'

C1='C-1'

A2='A-2'

B2='B-2'

C2='C-2'

;

array a{*} A1-A2;

array b{*} B1-B2;

array c{*} C1-C2;

array val{*} ANSWER_1 ANSWER_2;

do until(last.ID);

set have;

by ID;

do i=1 to dim(val);

If Type='A' then a{i}=val{i};

else If Type='B' then b{i}=val{i};

else If Type='C' then c{i}=val{i};

end;

end;

if last.ID then do;

output;

do i=1 to dim(val);

a(i)=.;

b(i)=.;

c(i)=.;

end;

end;

run;