I have several missing values of alcohol. If the previous wave answered "never," the missing value would be
"never." If wave1 had a missing value and wave 2 had a non-missing value, the wave2's answer could be assigned to wave1. I was wondering if there is any way I can do that. Thanks!
Obs | id | wave | follow_up_year | alcohol | Expected-alcohol |
1 | 100039 | 1 | 14 | never | never |
2 | 100039 | 2 | 14 | never | never |
3 | 100039 | 3 | 14 | never | never |
4 | 100039 | 4 | 14 | never | never |
5 | 100039 | 5 | 14 | . | never |
6 | 100039 | 6 | 14 | never | never |
7 | 100039 | 7 | 14 | never | never |
8 | 100039 | 8 | 14 | never | never |
9 | 100044 | 1 | 4 | never | never |
10 | 100044 | 2 | 4 | never | never |
11 | 100052 | 1 | 14 | . | >=3 |
12 | 100052 | 2 | 14 | >=3 | >=3 |
13 | 100052 | 3 | 14 | . | >=3 |
14 | 100052 | 4 | 14 | . | >=3 |
15 | 100052 | 5 | 14 | . | >=3 |
16 | 100052 | 6 | 14 | >=3 | >=3 |
17 | 100052 | 7 | 14 | . | >=3 |
18 | 100052 | 8 | 14 | >=3 | >=3 |
19 | 100055 | 1 | 12 | >=3 | >=3 |
20 | 100055 | 2 | 12 | 1-2 | 1-2 |
21 | 100055 | 3 | 12 | 1-2 | 1-2 |
22 | 100055 | 4 | 12 | >=3 | >=3 |
23 | 100055 | 5 | 12 | . | >=3 |
24 | 100055 | 6 | 12 | >=3 | >=3 |
25 | 100055 | 7 | 12 | . | >=3 |
26 | 100057 | 1 | 14 | never | never |
27 | 100057 | 2 | 14 | never | never |
28 | 100057 | 3 | 14 | never | never |
29 | 100057 | 4 | 14 | never | never |
30 | 100057 | 5 | 14 | 1-2 | 1-2 |
31 | 100057 | 6 | 14 | . | 1-2 |
32 | 100057 | 7 | 14 | never | never |
33 | 100057 | 8 | 14 | 1-2 | 1-2 |
data want;
/* first loop catches the first non-missing value */
do until (last.id);
set have;
by id;
if _alcohol = "" then _alcohol = alcohol;
end;
do until (last.id);
set have;
by id;
if alcohol = ""
then alcohol = _alcohol;
else _alcohol = alcohol;
output;
end;
drop _alcohol;
run;
Untested, posted from my tablet.
data want;
/* first loop catches the first non-missing value */
do until (last.id);
set have;
by id;
if _alcohol = "" then _alcohol = alcohol;
end;
do until (last.id);
set have;
by id;
if alcohol = ""
then alcohol = _alcohol;
else _alcohol = alcohol;
output;
end;
drop _alcohol;
run;
Untested, posted from my tablet.
Calrifications are needed:
1) You asked for * If the previous wave answered "never," the missing value would be
"never." *. If the previous is non-missing and not "never" should it be copied to the current ?
2) How to act if 1st and 2nd (or more in sequence) are missing - should they get the first non-missing value or remain as they are ?
3) If ther are more than one missing value in sequence, should the value that was copied to the first one should be copied to the all following missing values ? (obs 13 gets value of obs 12. should it be copied to obs 14 and 15 too ?)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.