SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Need to combine multiple records into one

Reply
New Contributor
Posts: 2

Need to combine multiple records into one

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 

 

 

Super User
Posts: 11,343

Re: Need to combine multiple records into one

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.

New Contributor
Posts: 2

Re: Need to combine multiple records into one

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. 

Super User
Posts: 5,427

Re: Need to combine multiple records into one

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
Ask a Question
Discussion stats
  • 3 replies
  • 320 views
  • 0 likes
  • 3 in conversation