topic Re: need help tracking multiple Name under same ID in New SAS User

need help tracking multiple Name under same ID

haider_imam — Wed, 02 Oct 2019 17:20:14 GMT

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!

Re: need help tracking multiple Name under same ID

Reeza — Wed, 02 Oct 2019 17:24:45 GMT

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!

Re: need help tracking multiple Name under same ID

Kurt_Bremser — Wed, 02 Oct 2019 17:29:06 GMT

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;

Re: need help tracking multiple Name under same ID

Reeza — Wed, 02 Oct 2019 17:37:00 GMT

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

Re: need help tracking multiple Name under same ID

novinosrin — Wed, 02 Oct 2019 17:40:46 GMT


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;

Re: need help tracking multiple Name under same ID

Kurt_Bremser — Wed, 02 Oct 2019 17:59:06 GMT

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

Re: need help tracking multiple Name under same ID

Reeza — Wed, 02 Oct 2019 18:21:33 GMT

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