Hii i have a dataset like this
id | reas | freq count |
1 | 3 | 17 |
1 | 6 | 23 |
1 | 7 | 56 |
1 | 9 | 33 |
1 | 10 | 24 |
2 | 3 | 64 |
2 | 6 | 23 |
2 | 7 | 34 |
2 | 8 | 64 |
2 | 10 | 43 |
and the output i want is can anyone help me how to solve this ?
reas | id=1 | id=2 |
3 | 17 | 64 |
6 | 23 | 23 |
7 | 56 | 34 |
8 | 33 | 64 |
10 | 24 | 43 |
thanks in advance
proc sort data=have out=_have;
by reas;
run;
proc transpose data=_have out=want prefix=ID ;
by reas;
id id;
var freqcount;
run;
proc sort data=have out=_have;
by reas;
run;
proc transpose data=_have out=want prefix=ID ;
by reas;
id id;
var freqcount;
run;
You can try data step or using sql
/* data step */
proc sort data=have;
by reas;
run;
data want;
length
reas id_1 id_2 8;
merge
have(where=(id=1) rename=(freqcount=id_1) in=a)
have(where=(id=2) rename=(freqcount=id_2) in=b);
by reas;
drop id;
run;
/* PROC SQL */
proc sql;
create table want3 as
select
coalesce(a.reas,b.reas) as reas,
a.freqcount as id_1,
b.freqcount as Id_2
from have(where=(id=1)) a full join
have(where=(id=2)) b
on a.reas=b.reas;
quit;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.