BookmarkSubscribeRSS Feed
GFDwyer
Calcite | Level 5

I need to combine multiple records per patient into one long record. The dataset looks like this:

 

patient         cohort          score_a         score_b            score_c

A                    1                  2                      2                      

A                    2                                          2                       2

B                    2                  2                      1                      

B                    3                  1                      1

C                    1                                          2                       1
C                    2                  2                   
C                    3                  1

Ideally it should look like this:

patient        Cohort_a       cohort_b   cohort_c     score_a               score_b        score_c          score_d        score_e     score_f

A                    1                      2                                 1                             2               2                                              2              2

B                    2                      3                                 2                             1               0                       1                     1

C                    1                      2              3                                                2               1                       1 

 

 

3 REPLIES 3
ballardw
Super User

Is this layout supposed to be a report or a dataset?

 

If a dataset, what will you do next. There are very few tasks that the wide format is better for.

 

What would you want for output if a patient with 3 input records has score_a, Score_b and Score_c for all 3 records? I think you may need to spend some time describing a very explicit rule for how those scores get assigned to the wide format as I am not picking up immediately on how the values were assigned for patient C and why some seem to have been dropped as well as where the 0 for Score_c on patient B came from.

GFDwyer
Calcite | Level 5

It is for a dataset; I just need to get everything on one line to merge with another file. I would like to avoid duplicate records if possible. 

LinusH
Tourmaline | Level 20
Please describe the next step and the end result.
Transposing to wide format just for the sake of a merge operation might not be the best way to do it.
Having data stored in a long format makes it's easier to query, maintain and build dynamic reports on top of.
So without knowing the end result I would even suggest that you transpose score to rows...
Data never sleeps

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1257 views
  • 0 likes
  • 3 in conversation