Hello I have a question on how to duplicate data for subjects where data is missing on certain variables. I have a dataset where some subjects have 2 rows. Most information is unique and populated but some fields such as sex and weight may have missing data for the second record (see table 1). There is no pattern to the missingness with some subjects having sex and/or weight already populated. I am trying to replace the missing values with available values for these subjects. However, if a value exists, I would like to keep the populated values (see table 2). Table 1. Data with missingness SUBJID AGE SEX WEIGHT COHORT 1 37 F 65.5 1 1 2 2 40 M 70 1 2 41 M 2 3 M 80.5 1 4 32 M 75.3 1 4 33 75.2 2 5 50 F 68 2 6 45 F 75.5 1 7 30 M 68.5 1 7 2 Table 2. Final table with data updated in red. SUBJID AGE SEX WEIGHT COHORT 1 37 F 65.5 1 1 37 F 65.5 2 2 40 M 70 1 2 41 M 70 2 3 M 80.5 1 4 32 M 75.3 1 4 32 M 75.2 2 5 50 F 68 2 6 45 F 75.5 1 7 30 M 68.5 1 7 30 M 68.5 2 I have tried to use modify (see below) – which is fine where the duplicate data is the same in the transaction dataset as the Master, but for subjects 2 (age) and 4 (weight) where there is a different value, the values in the transaction dataset update the Master. I have done some reading and it appears a condition is easy when related to the transaction dataset, but I would like the condition to apply to the Master - if data is missing in Master then replace with transaction otherwise keep value in master. Other options are welcome. Thanks in advance for your time! data master;
input SUBJID $ AGE SEX $ WEIGHT COHORT;
datalines;
1 37 F 65.5 1
1 . . . 2
2 40 M 70 1
2 41 M . 2
3 . M 80.5 1
4 32 M 75.3 1
4 33 . 75.2 2
5 50 F 68 2
6 45 F 75.5 1
7 30 M 68.5 1
7 . . . 2
;
run;
data trans;
input SUBJID $ AGE SEX $ WEIGHT COHORT;
datalines;
1 37 F 65.5 1
2 40 M 70 1
3 . M 80.5 1
4 32 M 75.3 1
6 45 F 75.5 1
7 30 M 68.5 1
;
run; data trans;
set master;
keep subjid age sex weight;
where COHORT=1;
run;
data master;
modify master trans;
by subjid;
run;
... View more
