topic Re: Need to combine multiple records into one in SAS Data Management

Need to combine multiple records into one

GFDwyer — Fri, 11 Mar 2016 20:28:14 GMT

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

Re: Need to combine multiple records into one

ballardw — Fri, 11 Mar 2016 21:43:00 GMT

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.

Re: Need to combine multiple records into one

GFDwyer — Fri, 11 Mar 2016 21:46:25 GMT

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.

Re: Need to combine multiple records into one

LinusH — Sat, 12 Mar 2016 11:14:35 GMT

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