topic Re: code to pull forward missing values in SAS Programming

code to pull forward missing values

csetzkorn — Thu, 30 Mar 2017 14:16:13 GMT

I am using the following code to impute/pull forward missing values:

DATA WORK.Test;
 INPUT Cat1 $ Cat2 $ RunningNumber Value1 Value2;
 DATALINES;
Cat1_1 Cat2_1 1 1 2
Cat1_1 Cat2_1 2 . 3
Cat1_1 Cat2_1 3 . .
Cat1_1 Cat2_1 4 2 1
Cat1_2 Cat2_2 1 3 2
Cat1_2 Cat2_2 2 . 3
Cat1_2 Cat2_2 3 2 4
;

/*ensure that things are in the right order*/
proc sort data = WORK.Test;
 by Cat1 Cat2 RunningNumber;
run;

DATA WORK.Test;
 SET WORK.Test;
 by Cat1 Cat2;
 
 retain _Temp1;
 if not missing(Value1)
 then 
 do
 _Temp1=Value1;
 ImputedValue1=Value1;
 end;
 else 
 ImputedValue1=_Temp1;
 drop _Temp1;

 retain _Temp2;
 if not missing(Value2)
 then 
 do
 _Temp2=Value2;
 ImputedValue2=Value2;
 end;
 else 
 ImputedValue2=_Temp2;
 drop _Temp2;
run;

Inside each combination of Cat1 and Cat2 missing value should be imputed from the last seen non missing value 1 or 2 ordered by RunningNumber. The current result looks like this and is correct:

Cat1 Cat2 RunningNumber Value1 Value2 ImputedValue1 ImputedValue2
Cat1_1 Cat2_1 1 1 2 1 2
Cat1_1 Cat2_1 2 . 3 1 3
Cat1_1 Cat2_1 3 . . 1 3
Cat1_1 Cat2_1 4 2 1 2 1
Cat1_2 Cat2_2 1 3 2 3 2
Cat1_2 Cat2_2 2 . 3 3 3
Cat1_2 Cat2_2 3 2 4 2 4

Could you experts please be so kind and review this bit of code. IMHO it is correct. Thanks.

Re: code to pull forward missing values

art297 — Thu, 30 Mar 2017 14:21:49 GMT

Only problem I see is that you don't account for changes in cat1 cat2 thus, if the first running number is missing a value, it will grab it from the previous cat1 cat2 combination.

Art, CEO, AnalystFinder.com

Re: code to pull forward missing values

csetzkorn — Thu, 30 Mar 2017 14:35:34 GMT

Thanks - how can I avoid this please? I would have thought that by Cat1 Cat2; takes care of this ...

Re: code to pull forward missing values

art297 — Thu, 30 Mar 2017 14:40:10 GMT

You have two (as I recall) sets of code that being with:

 if not missing(Value1)

A quick change might be something like (untested):

 if first.cat2 or not missing(Value1)

Art, CEO, AnalystFinder.com

Re: code to pull forward missing values

data_null__ — Thu, 30 Mar 2017 16:48:02 GMT

This is more easily done using the UPDATE trick.

DATA WORK.Test;
   INPUT Cat1 $ Cat2 $ RunningNumber Value1 Value2;
   DATALINES;
Cat1_1 Cat2_1 1 1 2
Cat1_1 Cat2_1 2 . 3
Cat1_1 Cat2_1 3 . .
Cat1_1 Cat2_1 4 2 1
Cat1_2 Cat2_2 1 3 2
Cat1_2 Cat2_2 2 . 3
Cat1_2 Cat2_2 3 2 4
;;;;
   run;
proc print;
   run;
data locf;
   update test(obs=0) test;
   by cat1 cat2;
   output;
   run;
proc print;
   run;

Re: code to pull forward missing values

art297 — Thu, 30 Mar 2017 17:58:05 GMT

@data_null__: I agree

Art, CEO, AnalystFinder.com

p.s. Are you going to be at SGF?