BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
haider_imam
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!


 

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Super User

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
Super User

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

 

delete_test.png

Reeza
Super User
I think in 9.4 it was modified but not 100% sure.
novinosrin
Tourmaline | Level 20

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: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 6 replies
  • 1428 views
  • 2 likes
  • 4 in conversation