DATA Step, Macro, Functions and more

change data format from multiple rows per id to one row per id

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

change data format from multiple rows per id to one row per id

I have data like below :

 

ID visit m1 m2 m3 m4
1 week1 10      
1 week1   13    
1 week1     15  
1 week1       14
2 week2 12      
2 week2   11    
2 week2     14  
2 week2       13
3 week3 13      
3 week3   10    
3 week3     11  
3 week3       12

 

And I want to have data like :

ID visit m1 m2 m3 m4
1 week1 10 13 15 14
2 week2 12 11 14 13
3 week3 13 10 11 12

 

One way is to output 4 data for m1, m2, m3 and m4 and then merge. 

I wonder if there is a simpler way to do it? Thanks


Accepted Solutions
Solution
‎09-27-2016 10:06 AM
Super User
Posts: 17,784

Re: change data format from multiple rows per id to one row per id

Use SQL and take max of each column per id

 

use update trick

data want;

Update have (obs=0) have;

by id visit;

run;

 

https://communities.sas.com/t5/Base-SAS-Programming/How-can-I-combine-multiple-rows-to-a-single-row/...

View solution in original post


All Replies
Solution
‎09-27-2016 10:06 AM
Super User
Posts: 17,784

Re: change data format from multiple rows per id to one row per id

Use SQL and take max of each column per id

 

use update trick

data want;

Update have (obs=0) have;

by id visit;

run;

 

https://communities.sas.com/t5/Base-SAS-Programming/How-can-I-combine-multiple-rows-to-a-single-row/...

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 216 views
  • 3 likes
  • 2 in conversation