topic problem in proc sql over a query in SAS Programming

problem in proc sql over a query

deltaskipper — Tue, 04 Sep 2018 18:09:23 GMT

I have a dataset flight_demo which had 4 column namely origin, dest, carrier ,dep_time

I want to find the bussiest time in a day for each carrier from dep_time column

My code is:

PROC SQL;
CREATE TABLE xyz AS
SELECT * FROM(SELECT DISTINCT DEP_TIME,CARRIER,n(DEP_TIME) AS N
FROM CASE_1.FLIGHTM
GROUP BY CARRIER,DEP_TIME
)
ORDER BY N DESC
;
QUIT;
RUN;

The problem with the output is that it gives for each time how many flight are departed from carrier

what I want is only the bussiest time by each carrier like for example

18:59 DL 167

8:54 UA 166

7:55 B6 160

5:55 EV 158

i am not able to rectify how to do it using proc sql.

Is there any other method to do such stuff?

Any help will appreciate.

Re: problem in proc sql over a query

Reeza — Tue, 04 Sep 2018 18:35:45 GMT

You won't want to consider time as shown, you'd want to group it by hour or half hour.

You should calculate that new variable first, within your query or not, and then test your results with a grouping by hour.

Re: problem in proc sql over a query

PGStats — Tue, 04 Sep 2018 20:23:17 GMT

Finding the mode (maximum density) of a distribution from a sample is not a trivial problem. If you don't mind using the magic of statistical procedures, the following will do the job:

proc sort data=sasforum.Flight_demo out=fd; by carrier; run;
 
proc kde data=fd;
by carrier;
univar departuretime / out=fdDist plots=none;
run;

proc sql;
create table busyTime as
select carrier, value as busyTime format=time.
from fdDist
group by carrier
having density=max(density);
quit;

This method might miss departure peaks situated around midnight on rare occasions.