BookmarkSubscribeRSS Feed
Ravikumarpa4
Obsidian | Level 7
I've two data sets both have flight number and date as common variable. In one data set variable name as delaytype observation like no delay or delaytype
No I want display flights which are delayed, flight number date how I can do using SAS SQL
15 REPLIES 15
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As there is no test data, or how the output should look here is a guess:

proc sql;
  create table WANT as
  select  A.*,
            B.DELAYTYPE
  from    DATASET1 A
  full join DATASET2 B
  on       A.FLIGHT_NUMBER=B.FLIGHT_NUMBER
  and     A.DATE=B.DATE
  where  dELAYTYPE="DELAYED";
quit;

 

Ravikumarpa4
Obsidian | Level 7

Expected output is

flightdate

flight number

destination

delay

 

 

raw datasets;

 

 

flightdelays;
   informat Date date9.;
   input FlightNumber $ 1-3 @6 Date date9. Origin $ 17-19
         Destination $ 22-24 DelayCategory $ 27-41
         DestinationType $ 44-58 DayOfWeek 61 Delay @65;
   format date date9.;
datalines;
182  01MAR2000  LGA  YYZ  No Delay         International    4    0
114  01MAR2000  LGA  LAX  1-10 Minutes     Domestic         4    8
202  01MAR2000  LGA  ORD  No Delay         Domestic         4   -5
219  01MAR2000  LGA  LHR  11+ Minutes      International    4   18
439  01MAR2000  LGA  LAX  No Delay         Domestic         4   -4
387  01MAR2000  LGA  CPH  No Delay         International    4   -2

 

 

data sasuser.marchflights;
   informat Date date9. DepartureTime time5.;
   input FlightNumber $ 1-3 @6 Date date9. @17 DepartureTime time5.
         Origin $ 19-21 Destination $ 29-31 Distance 34-37 Mail 40-42
         Freight 45-47 Boarded 50-52 Transferred 55-56
         NonRevenue 59 Deplaned 62-64 PassengerCapacity 67-69;
   format Date date9. DepartureTime time5.;
datalines;
182  01MAR2000   8:21  LGA  YYZ   366  458  390  104  16  3  123  178
114  01MAR2000   7:10  LGA  LAX  2475  357  390  172  18  6  196  210
202  01MAR2000  10:43  LGA  ORD   740  369  244  151  11  5  157  210
219  01MAR2000   9:31  LGA  LHR  3442  412  334  198  17  7  222  250
439  01MAR2000  12:16  LGA  LAX  2475  422  267  167  13  5  185  210
387  01MAR2000  11:40  LGA  CPH  3856  423  398  152   8  3  163  250
290  01MAR2000   6:56  LGA  WAS   229  327  253   96  16  7  117  180
523  01MAR2000  15:19  LGA  ORD   740  476  456  177  20  3  185  210
982  01MAR2000  10:28  LGA  DFW  1383  383  355   49  19  2   56  180
622  01MAR2000  12:19  LGA  FRA  3857  255  243  207  15  5  227  250
821  01MAR2000  14:56  LGA  LHR  3442  334  289  205  13  4  222  250

Kurt_Bremser
Super User

Ahem...

All those columns:


@Ravikumarpa4 wrote:

Expected output is

flightdate

flight number

destination

delay

 


are already in dataset flightdelays. What for do you want to run the SQL join?

Ravikumarpa4
Obsidian | Level 7

I need output only below variables

Flight number

date

destination

DelayCategory in which  i need only flights which are delayed ex:1-10 Minutes, 11+ Minutes

Kurt_Bremser
Super User

@Ravikumarpa4 wrote:

I need output only below variables

Flight number

date

destination

DelayCategory in which  i need only flights which are delayed ex:1-10 Minutes, 11+ Minutes


So you don't need a join at all, as all those variables are in the same dataset. Just select them from flightdelays with a suitable where condition. That is a simple query that can be point-and clicked in the Query Builder.

Kurt_Bremser
Super User
proc sql;
create table want as
select
  a.*,
  b.delaytype
from
  have1 a
  left join
  have2 b /* have2 contains delaytype */
on a.flightnumber = b.flightnumber and a.date = b.date
;
quit;

For more help, post the datasets in data steps.

Ravikumarpa4
Obsidian | Level 7
But destination in different dataset
Ravikumarpa4
Obsidian | Level 7
And how to take only delaytime from delaycategory by excluding all those having no delay
Ravikumarpa4
Obsidian | Level 7
Sorry in flightdelay data set there is no destination by miss I pasted in raw dataset
Ravikumarpa4
Obsidian | Level 7

marchflights;
   informat Date date9. DepartureTime time5.;
   input FlightNumber $ 1-3 @6 Date date9. @17 DepartureTime time5.
         Distance 34-37 Mail 40-42
         Freight 45-47 Boarded 50-52 Transferred 55-56
         NonRevenue 59 Deplaned 62-64 PassengerCapacity 67-69;
   format Date date9. DepartureTime time5.;
datalines;
182  01MAR2000   8:21  366  458  390  104  16  3  123  178
114  01MAR2000   7:10  2475 357  390  172  18  6  196  210
202  01MAR2000  10:43  740  369  244  151  11  5  157  210
219  01MAR2000   9:31  3442 412  334  198  17  7  222  250
439  01MAR2000  12:16  2475 422  267  167  13  5  185  210
387  01MAR2000  11:40  3856 423  398  152   8  3  163  250
290  01MAR2000   6:56  229  327  253   96  16  7  117  180
523  01MAR2000  15:19  740  476  456  177  20  3  185  210
982  01MAR2000  10:28  1383 383  355   49  19  2   56  180
622  01MAR2000  12:19  3857 255  243  207  15  5  227  250
821  01MAR2000  14:56  3442 334  289  205  13  4  222  250
872  01MAR2000  13:02  2475 316  357  145  13  5  163  210
416  01MAR2000   9:09  229  497  235   71  18  4   90  180
132  01MAR2000  15:35  366  288  459  115  24  5  144  178
829  01MAR2000  13:38  229  487  235   75  16  5   88  180
183  01MAR2000  17:46  229  371  270   80  19  3   85  180
271  01MAR2000  13:17  3635 490  392  138  14  6  158  250
921  01MAR2000  17:11  1383 362  377  122   8  4  132  180
302  01MAR2000  20:22  229  363  273  105  24  3  128  180
431  01MAR2000  18:50  2475 403  427  153  14  6  173  210
308  01MAR2000  21:06  740  311  307  159  20  8  181  210

Ravikumarpa4
Obsidian | Level 7
##- Please type your reply above this line. Simple formatting, no
attachments. -##

That's correct raw data which I provided earlier

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 15 replies
  • 1641 views
  • 0 likes
  • 3 in conversation