DATA Step, Macro, Functions and more

filling missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

filling missing values

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

ty
154
266
3.
487
592
6.
7.
88
921
1053
1189
12.
13.
14.
1575
1680
1779
1845
1994
2076

Want:

tylagfornew_y
154..54
266..66
3.668776.5
487..87
592..92
6.92850
7.92850
88..8
921..21
1053..53
1189..89
12.897582
13.897582
14.897582
1575..75
1680..80
1779..79
1845..45
1994..94
2076..76

Accepted Solutions
Solution
‎02-08-2012 09:07 AM
Respected Advisor
Posts: 3,799

Re: filling missing values

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

Re: filling missing values

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=_Smiley Happy;

  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=_Smiley Happy;

  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

Solution
‎02-08-2012 09:07 AM
Respected Advisor
Posts: 3,799

Re: filling missing values

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

Occasional Contributor
Posts: 11

Re: filling missing values

Posted in reply to data_null__

Excellent. Many thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 292 views
  • 3 likes
  • 3 in conversation