need help tracking multiple Name under same ID

[haider_imam]

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!

[Reeza · Accepted solution]

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!

---

[Kurt_Bremser]

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;

[Reeza]

@Kurt_Bremser you don't need to create that ID ahead of time anymore, PROC TRANSPOSE does it automatically now as needed. 

[Kurt_Bremser]

Nice, learned something new once again. Was that added in rather recently?

[Reeza]

I think in 9.4 it was modified but not 100% sure.

[novinosrin]

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;