BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jacob_klimek
Obsidian | Level 7

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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
Super User

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
Calcite | Level 5

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;

 

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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