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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 895 views
  • 0 likes
  • 3 in conversation