topic Re: Impute Missing Values in SAS Programming

Impute Missing Values

Akshayvrata — Thu, 16 Aug 2018 11:57:35 GMT

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.

Re: Impute Missing Values

PaigeMiller — Thu, 16 Aug 2018 12:02:41 GMT

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

Re: Impute Missing Values

Astounding — Thu, 16 Aug 2018 12:15:00 GMT

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.

Re: Impute Missing Values

SuryaKiran — Thu, 16 Aug 2018 12:53:17 GMT

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 .

Re: Impute Missing Values

Akshayvrata — Thu, 16 Aug 2018 12:53:28 GMT

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!

Re: Impute Missing Values

Astounding — Thu, 16 Aug 2018 13:26:22 GMT

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.

Re: Impute Missing Values

Ksharp — Thu, 16 Aug 2018 13:47:48 GMT

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;