DATA Step, Macro, Functions and more

code to pull forward missing values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

code to pull forward missing values

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.


Accepted Solutions
Solution
‎03-30-2017 11:50 AM
PROC Star
Posts: 7,486

Re: code to pull forward missing values

Posted in reply to csetzkorn

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

View solution in original post


All Replies
PROC Star
Posts: 7,486

Re: code to pull forward missing values

Posted in reply to csetzkorn

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

Frequent Contributor
Posts: 110

Re: code to pull forward missing values

Thanks - how can I avoid this please? I would have thought that by Cat1 Cat2; takes care of this ...
Solution
‎03-30-2017 11:50 AM
PROC Star
Posts: 7,486

Re: code to pull forward missing values

Posted in reply to csetzkorn

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

Respected Advisor
Posts: 3,799

Re: code to pull forward missing values

Posted in reply to csetzkorn

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;

Capture.PNG

PROC Star
Posts: 7,486

Re: code to pull forward missing values

Posted in reply to data_null__

@data_null__: I agree

 

Art, CEO, AnalystFinder.com

 

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

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 162 views
  • 1 like
  • 3 in conversation