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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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