topic Re: Proc Transpose Help in SAS Procedures

Proc Transpose Help

jacob_klimek — Tue, 11 Oct 2016 19:55:59 GMT

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.

Re: Proc Transpose Help

Reeza — Tue, 11 Oct 2016 20:14:44 GMT

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;

Re: Proc Transpose Help

Reeza — Tue, 11 Oct 2016 20:19:35 GMT

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=);

Re: Proc Transpose Help

YZ89 — Tue, 11 Oct 2016 21:03:01 GMT

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;

if last.ID then do;

output;

do i=1 to dim(val);

a(i)=.;

b(i)=.;

c(i)=.;

end;

run;