Help using Base SAS procedures

Merging rows by ID which isn't unique

Reply
Contributor
Posts: 27

Merging rows by ID which isn't unique

Hello!

I have a following problem. What I have right now:

ID V1 V2 V3
1 x    
1 x    
1 x    
1   x  
1   x  
1   x  
1     x
1     x
1     x
2 x    
2   x  
2     x
3 x    
3 x    
3   x  
3   x  
3     x
3     x
4 x    
4 x    
4   x  
4   x  
4     x
4     x


What I would like to get:

ID V1 V2 V3
1 x x x
1 x x x
1 x x x
2 x x x
3 x x x
3 x x x
4 x x x
4 x x x

 

I'd be grateful!

Dan

Super User
Posts: 17,831

Re: Merging rows by ID which isn't unique

What about update?

 

Not sure it would keep the correct amount of,records though, you may need to add a counter to uniquely identify iterations. 

 

Data want;

update have(obs=0) have;

by id counter;

run;

 

data should be:

 

id counter 

1 1

1 2

1 3

1 1

1 2

1 3

2 1

2 1

...etc 

Super User
Super User
Posts: 7,401

Re: Merging rows by ID which isn't unique

The question is, why do you have data like that, it looks to me like some step previous to this is the real cause of the issue.  Fixing the data before you get into this position would be my suggestion.

Super User
Posts: 5,083

Re: Merging rows by ID which isn't unique

Here's an approach that would work for numeric variables V1, V2, and V3:

 

data want;

merge have (where=(v1 > .)) have (where=(v2 > .)) have (where=(v3 > .));

by id;

run;

 

If any of these variables are character instead, you would have to change the corresponding WHERE condition (switch from > . to > ' ').

 

You will still get a message about a many-to-many match, but the results will be OK.  ("OK" might be open to interpretation if you have differing numbers of values, like 3 observations with a V1 value for an ID, but only 2 observations with a V2 value.)

Super User
Posts: 5,257

Re: Merging rows by ID which isn't unique

Another fix could be using SQL GROUP BY and max().
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 245 views
  • 3 likes
  • 5 in conversation