BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
csetzkorn
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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

csetzkorn
Lapis Lazuli | Level 10
Thanks - how can I avoid this please? I would have thought that by Cat1 Cat2; takes care of this ...
art297
Opal | Level 21

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

data_null__
Jade | Level 19

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

art297
Opal | Level 21

@data_null__: I agree

 

Art, CEO, AnalystFinder.com

 

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

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
  • 5 replies
  • 810 views
  • 1 like
  • 3 in conversation