topic How to use the latest wave's non-missing value to fill missing value? in SAS Programming

How to use the latest wave's non-missing value to fill missing value?

nwang5 — Tue, 21 Feb 2023 18:07:48 GMT

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

Re: How to use the latest wave's non-missing value to fill missing value?

Kurt_Bremser — Wed, 22 Feb 2023 06:37:16 GMT

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.

Re: How to use the latest wave's non-missing value to fill missing value?

Shmuel — Wed, 22 Feb 2023 07:01:24 GMT

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 ?)