Hi Currently I have a data set which has Names and IDs and we have multiple Names under one ID
Name ID
jon 12
brian 12
craig 40
what I want is to simplify to have both names in the same row for each ID so something like
Name Name1 ID
jon brian 12
craig 40
any advice on how I can go about that? or get something similar even?
Even if I can get something like below I can always delete duplicates ID
Name Name1 ID
jon brian 12
brian jon 12
craig 40
Thanks!
Use PROC SORT + NODUPKEY to remove duplicates and then use PROC TRANSPOSE.
proc sort data=have nodupkey;
by id name;
run;
proc transpose data=have out=want prefix=NAME;
by ID;
var name;
run;
@haider_imam wrote:
Hi Currently I have a data set which has Names and IDs and we have multiple Names under one ID
Name ID
jon 12
brian 12
craig 40
what I want is to simplify to have both names in the same row for each ID so something like
Name Name1 ID
jon brian 12
craig 40
any advice on how I can go about that? or get something similar even?
Even if I can get something like below I can always delete duplicates ID
Name Name1 ID
jon brian 12
brian jon 12
craig 40
Thanks!
Use PROC SORT + NODUPKEY to remove duplicates and then use PROC TRANSPOSE.
proc sort data=have nodupkey;
by id name;
run;
proc transpose data=have out=want prefix=NAME;
by ID;
var name;
run;
@haider_imam wrote:
Hi Currently I have a data set which has Names and IDs and we have multiple Names under one ID
Name ID
jon 12
brian 12
craig 40
what I want is to simplify to have both names in the same row for each ID so something like
Name Name1 ID
jon brian 12
craig 40
any advice on how I can go about that? or get something similar even?
Even if I can get something like below I can always delete duplicates ID
Name Name1 ID
jon brian 12
brian jon 12
craig 40
Thanks!
First, create a count, then transpose:
proc sort data=have;
by id;
run;
data inter;
set have;
by id;
if first.id
then count = 1;
else count + 1;
run;
proc transpose data=inter out=want prefix=name;
by id;
var name;
id count;
run;
@Kurt_Bremser you don't need to create that ID ahead of time anymore, PROC TRANSPOSE does it automatically now as needed.
Nice, learned something new once again. Was that added in rather recently?
data have;
input Name $ ID;
cards;
jon 12
brian 12
craig 40
;
proc sql noprint;
select max(obs) into :obs
from ( select count(*) as obs from have group by id ) ;
quit;
proc summary nway data=have missing;
class id;
output
out = want(drop=_type_ _freq_)
idgroup(out[&obs](name)=) ;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.