topic Re: Pick The Last Non Missing Value in SAS Programming

Pick The Last Non Missing Value

sasmaverick — Thu, 18 Aug 2016 16:21:56 GMT

I have the following dataset:
X Y
1 70
1 .
1 .
1 .
1 80
1 .
1 .

I want to assign the last non missing value to the missing observations. Expected output as follows
X Y
1 70
1 70
1 70
1 70
1 80
1 80
1 80

Please help. I tried do end loop but to no use.

Re: Pick The Last Non Missing Value

sasmaverick — Thu, 18 Aug 2016 16:29:29 GMT

Adding to the above requirment, I may also need to pick last non missing in a BY group. That is, I don't need to assign 80 if X=2.

Re: Pick The Last Non Missing Value

paulkaefer — Thu, 18 Aug 2016 16:37:12 GMT

Remove the rename and drop statements if you want the new variable to be in its own column.

data temp;
   input X Y;
   datalines;
1 70
1 .
1 .
1 .
1 80
1 .
1 .
;

data temp2;
    set temp;
    retain last_non_missing;
    
    if (not missing(Y)) then
        last_non_missing = Y;

    rename last_non_missing=Y;
    drop Y;
run;

Re: Pick The Last Non Missing Value

paulkaefer — Thu, 18 Aug 2016 16:40:45 GMT

with your updated requirement, is this what you want? note that the Y value when X is 3 will be .

data temp;
   input X Y;
   datalines;
1 70
1 .
1 .
1 .
1 80
1 .
1 .
2 60
2 .
3 .
;

data temp2;
    set temp;
    retain last_non_missing;
    
    if (first.x) then
        last_non_missing = .;

    if (not missing(Y)) then
        last_non_missing = Y;

    by x;
    rename last_non_missing=Y;
    drop Y;
run;

Re: Pick The Last Non Missing Value

data_null__ — Thu, 18 Aug 2016 18:01:25 GMT

This is a good place to apply the UPDATE trick it handles BY group automatically.

data XY;
   input X Y @@;
   cards;
1 70 1 . 1 . 1 .
1 80 1 . 1 . 
2 60 2 . 3 .
;;;;
   run;
proc print;
   run;
data LOCF;
   update XY(obs=0) XY;
   by X;
   output;
   run;
proc print;
   run;