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.
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;
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;
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=);
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: