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: 318
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: 19,865

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

Posted in reply to fengyuwuzu

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: 19,865

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

Posted in reply to fengyuwuzu

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
  • 218 views
  • 3 likes
  • 2 in conversation