Impute Missing Values

[Akshayvrata]

Hi,

Here is sample data just to make the question comprehensible.

Origin Destination Carrier Delay

EWR   DEL              UA       0:15

EWR    DEL             UA       -1:00

NYC      DEL            CA        2:00

DEL      NYC            UA        3:00

NYC     DEL             CA        -0:15

MUM     NYC            B6         1:00

MUM     NYC            B6          .

EWR      DEL            UA          .

Now we have to impute missing values in Delay with an average of delay that has to be specific for the carrier and (origin-->dest).

Please help me on this.   

Thanks in advance.

[PaigeMiller]

You could use the average of the delays for each Origin/Destination/Carrier combination in place of the missing values.

[Astounding]

Did you try to compute the average delay for each Origin / Destination / Carrier?  Show what you tried.

If you already did this, does the problem lie with getting that average back into your original data set to replace missing values?  Again, show what you tried.

[Akshayvrata]

Yeah! 

I have made a variable named as flight_code in which I have concatenated origin, destination and carrier.

Then using this I calculated mean by proc means by flight_code. I got the mean but I am unable to figure how to impute that into missing values..

And one more problem I am facing is that mean is coming in a numeric format not in time format. 

Thanks!

[Astounding]

Great.  You're familiar with some of the useful tools.  Next step would be to change the BY statement in PROC MEANS.  Instead of concatenating three variables, use all three original variables in the BY statement:

by origin destination carrier;

(The order of variables in the BY statement must match the sorted order to the data.)

Following that, you should be able to merge the results back in (again, using all three variables in the BY statement when merging).

The time format can be applied later, in the same step that merges the results back in.  Use the same time format that is in use for your existing DELAY variable.

[SuryaKiran · Accepted solution]

PROC SQL with CASE expression to calculate the Average for missing values. I'm assuming your delay values are hh:mm

data have;
infile datalines dlm=',' dsd missover;
input Origin $ Destination $ Carrier $ Delay;
datalines;
EWR,DEL,UA,15
EWR,DEL,UA,60
NYC,DEL,CA,120
DEL,NYC,UA,180
NYC,DEL,CA,-15
MUM,NYC,B6,60
MUM,NYC,B6,.
EWR,DEL,UA,.
;
run;

proc sql;
select 	Origin , 
		Destination , 
		Carrier,
		Delay,
		case when Delay is null then avg(Delay) 
			else Delay end	as New_Delay
from have
group by Origin , Destination , Carrier
;
quit;

Note: Please provide your sample data in the form of Data Step, it saves our time and you can expect quick replies . 

[Ksharp]

If I understood your question.

data have;
infile datalines dlm=',' dsd missover;
input Origin $ Destination $ Carrier $ Delay;
datalines;
EWR,DEL,UA,15
EWR,DEL,UA,60
NYC,DEL,CA,120
DEL,NYC,UA,180
NYC,DEL,CA,-15
MUM,NYC,B6,60
MUM,NYC,B6,.
EWR,DEL,UA,.
;
run;
proc sort data=have;
by Origin  Destination  Carrier;
run;
proc stdize data=have missing=mean reponly out=want;
by Origin  Destination  Carrier;
var delay;
run;