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.
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
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.