Help using Base SAS procedures

Proc Transpose Help

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Proc Transpose Help

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.

 

 

 


Accepted Solutions
Solution
‎10-11-2016 05:45 PM
Super User
Posts: 17,960

Re: Proc Transpose Help

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;

View solution in original post


All Replies
Solution
‎10-11-2016 05:45 PM
Super User
Posts: 17,960

Re: Proc Transpose Help

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;
Super User
Posts: 17,960

Re: Proc Transpose Help

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=);
Occasional Learner
Posts: 1

Re: Proc Transpose Help

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 333 views
  • 0 likes
  • 3 in conversation