topic Re: SAS SQL in SAS Procedures

SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 14:44:04 GMT

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

Re: SAS SQL

RW9 — Tue, 28 Feb 2017 14:46:42 GMT

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;

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 14:49:34 GMT

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.

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 15:04:38 GMT

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

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 15:10:13 GMT

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?

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 16:16:01 GMT

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

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 17:17:52 GMT

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

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 17:18:36 GMT

But destination in different dataset

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 17:20:06 GMT

You're kidding, right?

Read your own post, please.

@Ravikumarpa4 wrote:
But destination in different dataset

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 17:21:48 GMT

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

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 17:24:46 GMT

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

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 17:26:16 GMT

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

Then post correct example data.

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 17:41:11 GMT

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

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 17:55:24 GMT

And what about dataset flightdelays?

Re: SAS SQL

Ravikumarpa4 — Tue, 28 Feb 2017 18:03:19 GMT

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

That's correct raw data which I provided earlier

Re: SAS SQL

Kurt_Bremser — Tue, 28 Feb 2017 18:14:01 GMT

Which means that flightdelays contains ALL, I repeat ALL variables you want in your query.

If you don't come up with substantually new information, I'm out of here. I've got better things to do with my time.