BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bkoksal
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

3 REPLIES 3
Haikuo
Onyx | Level 15

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

data_null__
Jade | Level 19

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

bkoksal
Calcite | Level 5

Excellent. Many thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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