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.
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 .
You could use the average of the delays for each Origin/Destination/Carrier combination in place of the missing values.
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.
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!
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.
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 .
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;
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.