topic Re: filling missing values in SAS Programming

filling missing values

bkoksal — Wed, 08 Feb 2012 07:59:11 GMT

Dear Stata Users,

I have a data set similar to the below. I want to have two new variables such that for missing values of y, the first variable will have the first previous nonmissing value of y, and the second variable will have the first next nonmissing value of y. Basically, I want to fill missing values of y so that these values are equal to the average of previous and next nonmissing values of y. Thanks.

Have

t	y
1	54
2	66
3	.
4	87
5	92
6	.
7	.
8	8
9	21
10	53
11	89
12	.
13	.
14	.
15	75
16	80
17	79
18	45
19	94
20	76

Want:

t	y	lag	for	new_y
1	54	.	.	54
2	66	.	.	66
3	.	66	87	76.5
4	87	.	.	87
5	92	.	.	92
6	.	92	8	50
7	.	92	8	50
8	8	.	.	8
9	21	.	.	21
10	53	.	.	53
11	89	.	.	89
12	.	89	75	82
13	.	89	75	82
14	.	89	75	82
15	75	.	.	75
16	80	.	.	80
17	79	.	.	79
18	45	.	.	45
19	94	.	.	94
20	76	.	.	76

Re: filling missing values

Haikuo — Wed, 08 Feb 2012 13:19:23 GMT

Well, I could not figure out how to do it in one data step. And I am sure someone will come up with more elegant approaches.

data have;

infile cards;

input Havet y;

cards;

1 54

2 66

3 .

4 87

5 92

6 .

7 .

8 8

9 21

10 53

11 89

12 .

13 .

14 .

15 75

16 80

17 79

18 45

19 94

20 76

;

data want1 (drop=_:);

do _n_=1 by 1 until (eof);

set have end=eof;

_lag_y=ifn(y ne ., y,_lag_y);

lag_y=ifn(y ne ., .,_lag_y);

output;

end;

run;

proc sort data=want1;

by descending havet;

run;

data want2 (drop=_:);

do _n_=1 by 1 until (eof);

set want1 end=eof;

_lead_y=ifn(y ne ., y,_lead_y);

lead_y=ifn(y ne ., .,_lead_y);

new_y=ifn(y ne ., y,mean(lag_y,lead_y));

output;

end;

run;

proc sort data=want2;

by havet;

run;

Regards,

Haikou

Re: filling missing values

data_null__ — Wed, 08 Feb 2012 14:07:54 GMT

In SAS/Graph this is called STEP interpolation both LEFT and RIGHT. Here is how I think it is done with PROC EXPAND. I don't know if PROC EXPAND can be made to transform Y1 and Y2 into a mean. You could do that in a data step.

data Have;
   input t  y @@;
   cards;
1     54 2  66 3  .
4     87 5  92 6  .
7     .  8  8  9  21
10    53 11 89 12 .
13    .  14 .  15 75
16    80 17 79 18 45
19    94 20 76
;;;;
   run;
 
proc expand data=have out=test;
   id t;
   convert y=y1 / method=step;
   convert y=y2 / tin=(reverse) tout=(reverse) method=step;
   run;
proc print;
   run;

Obs t y1 y2 y

1 1 54 54 54

2 2 66 66 66

3 3 66 87 .

4 4 87 87 87

5 5 92 92 92

6 6 92 8 .

7 7 92 8 .

8 8 8 8 8

9 9 21 21 21

10 10 53 53 53

11 11 89 89 89

12 12 89 75 .

13 13 89 75 .

14 14 89 75 .

15 15 75 75 75

16 16 80 80 80

17 17 79 79 79

18 18 45 45 45

19 19 94 94 94

20 20 76 76 76

Re: filling missing values

bkoksal — Wed, 08 Feb 2012 14:54:21 GMT

Excellent. Many thanks.

t	y
1	54
2	66
3	.
4	87
5	92
6	.
7	.
8	8
9	21
10	53
11	89
12	.
13	.
14	.
15	75
16	80
17	79
18	45
19	94
20	76

t	y
1	54
2	66
3	.
4	87
5	92
6	.
7	.
8	8
9	21
10	53
11	89
12	.
13	.
14	.
15	75
16	80
17	79
18	45
19	94
20	76

t	y
1	54
2	66
3	.
4	87
5	92
6	.
7	.
8	8
9	21
10	53
11	89
12	.
13	.
14	.
15	75
16	80
17	79
18	45
19	94
20	76