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
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.
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.