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;
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
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?
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
@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.
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.
You're kidding, right?
Read your own post, please.
@Ravikumarpa4 wrote:
But destination in different dataset
@Ravikumarpa4 wrote:
Sorry in flightdelay data set there is no destination by miss I pasted in raw dataset
Then post correct example data.
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
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 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.