SAS SQL

[Ravikumarpa4]

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

[RW9]

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]

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]

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]

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]

---

@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]

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]

But destination in different dataset

[Kurt_Bremser]

You're kidding, right?

Read your own post, please.

---

@Ravikumarpa4 wrote:
But destination in different dataset

---

[Ravikumarpa4]

And how to take only delaytime from delaycategory by excluding all those having no delay

[Ravikumarpa4]

Sorry in flightdelay data set there is no destination by miss I pasted in raw dataset

[Kurt_Bremser]

---

@Ravikumarpa4 wrote:
Sorry in flightdelay data set there is no destination by miss I pasted in raw dataset

---

Then post correct example data.

[Ravikumarpa4]

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

[Kurt_Bremser]

And what about dataset flightdelays?

[Ravikumarpa4]

##- Please type your reply above this line. Simple formatting, no
attachments. -##

That's correct raw data which I provided earlier