/* SAS PROC SQL examples. */ *libname sql 'SAS-data-library'; options ls=80 ps=60 nodate nonumber; data sql.march; input flight $3. +5 date date7. +3 depart time5. +2 orig $3. +3 dest $3. +7 miles +6 boarded +6 capacity; format date date7. depart time5.; informat date date7. depart time5.; cards; 114 01MAR94 7:10 LGA LAX 2475 172 210 202 01MAR94 10:43 LGA ORD 740 151 210 219 01MAR94 9:31 LGA LON 3442 198 250 622 01MAR94 12:19 LGA FRA 3857 207 250 132 01MAR94 15:35 LGA YYZ 366 115 178 271 01MAR94 13:17 LGA PAR 3635 138 250 302 01MAR94 20:22 LGA WAS 229 105 180 114 02MAR94 7:10 LGA LAX 2475 119 210 202 02MAR94 10:43 LGA ORD 740 120 210 219 02MAR94 9:31 LGA LON 3442 147 250 622 02MAR94 12:19 LGA FRA 3857 176 250 132 02MAR94 15:35 LGA YYZ 366 106 178 302 02MAR94 20:22 LGA WAS 229 78 180 271 02MAR94 13:17 LGA PAR 3635 104 250 114 03MAR94 7:10 LGA LAX 2475 197 210 202 03MAR94 10:43 LGA ORD 740 118 210 219 03MAR94 9:31 LGA LON 3442 197 250 622 03MAR94 12:19 LGA FRA 3857 180 250 132 03MAR94 15:35 LGA YYZ 366 75 178 271 03MAR94 13:17 LGA PAR 3635 147 250 302 03MAR94 20:22 LGA WAS 229 123 180 114 04MAR94 7:10 LGA LAX 2475 178 210 202 04MAR94 10:43 LGA ORD 740 148 210 219 04MAR94 9:31 LGA LON 3442 232 250 622 04MAR94 12:19 LGA FRA 3857 137 250 132 04MAR94 15:35 LGA YYZ 366 117 178 271 04MAR94 13:17 LGA PAR 3635 146 250 302 04MAR94 20:22 LGA WAS 229 115 180 114 05MAR94 7:10 LGA LAX 2475 117 210 202 05MAR94 10:43 LGA ORD 740 104 210 219 05MAR94 9:31 LGA LON 3442 160 250 622 05MAR94 12:19 LGA FRA 3857 185 250 132 05MAR94 15:35 LGA YYZ 366 157 178 271 05MAR94 13:17 LGA PAR 3635 177 250 114 06MAR94 7:10 LGA LAX 2475 128 210 202 06MAR94 10:43 LGA ORD 740 115 210 219 06MAR94 9:31 LGA LON 3442 163 250 132 06MAR94 15:35 LGA YYZ 366 150 178 302 06MAR94 20:22 LGA WAS 229 66 180 114 07MAR94 7:10 LGA LAX 2475 160 210 202 07MAR94 10:43 LGA ORD 740 175 210 219 07MAR94 9:31 LGA LON 3442 241 250 622 07MAR94 12:19 LGA FRA 3857 210 250 132 07MAR94 15:35 LGA YYZ 366 164 178 271 07MAR94 13:17 LGA PAR 3635 155 250 302 07MAR94 20:22 LGA WAS 229 135 180 ; data sql.delay; input flight $3. +5 date date7. +2 orig $3. +3 dest $3. +3 delaycat $15. +2 destype $15. +8 delay; informat date date7.; format date date7.; cards; 114 01MAR94 LGA LAX 1-10 Minutes Domestic 8 202 01MAR94 LGA ORD No Delay Domestic -5 219 01MAR94 LGA LON 11+ Minutes International 18 622 01MAR94 LGA FRA No Delay International -5 132 01MAR94 LGA YYZ 11+ Minutes International 14 271 01MAR94 LGA PAR 1-10 Minutes International 5 302 01MAR94 LGA WAS No Delay Domestic -2 114 02MAR94 LGA LAX No Delay Domestic 0 202 02MAR94 LGA ORD 1-10 Minutes Domestic 5 219 02MAR94 LGA LON 11+ Minutes International 18 622 02MAR94 LGA FRA No Delay International 0 132 02MAR94 LGA YYZ 1-10 Minutes International 5 271 02MAR94 LGA PAR 1-10 Minutes International 4 302 02MAR94 LGA WAS No Delay Domestic 0 114 03MAR94 LGA LAX No Delay Domestic -1 202 03MAR94 LGA ORD No Delay Domestic -1 219 03MAR94 LGA LON 1-10 Minutes International 4 622 03MAR94 LGA FRA No Delay International -2 132 03MAR94 LGA YYZ 1-10 Minutes International 6 271 03MAR94 LGA PAR 1-10 Minutes International 2 302 03MAR94 LGA WAS 1-10 Minutes Domestic 5 114 04MAR94 LGA LAX 11+ Minutes Domestic 15 202 04MAR94 LGA ORD No Delay Domestic -5 219 04MAR94 LGA LON 1-10 Minutes International 3 622 04MAR94 LGA FRA 11+ Minutes International 30 132 04MAR94 LGA YYZ No Delay International -5 271 04MAR94 LGA PAR 1-10 Minutes International 5 302 04MAR94 LGA WAS 1-10 Minutes Domestic 7 114 05MAR94 LGA LAX No Delay Domestic -2 202 05MAR94 LGA ORD 1-10 Minutes Domestic 2 219 05MAR94 LGA LON 1-10 Minutes International 3 622 05MAR94 LGA FRA No Delay International -6 132 05MAR94 LGA YYZ 1-10 Minutes International 3 271 05MAR94 LGA PAR 1-10 Minutes International 5 114 06MAR94 LGA LAX No Delay Domestic -1 202 06MAR94 LGA ORD No Delay Domestic -3 219 06MAR94 LGA LON 11+ Minutes International 27 132 06MAR94 LGA YYZ 1-10 Minutes International 7 302 06MAR94 LGA WAS 1-10 Minutes Domestic 1 114 07MAR94 LGA LAX No Delay Domestic -1 202 07MAR94 LGA ORD No Delay Domestic -2 219 07MAR94 LGA LON 11+ Minutes International 15 622 07MAR94 LGA FRA 11+ Minutes International 21 132 07MAR94 LGA YYZ No Delay International -2 271 07MAR94 LGA PAR 1-10 Minutes International 4 302 07MAR94 LGA WAS No Delay Domestic 0 ; data sql.internat; input flight $3. +5 date date7. +2 dest $3. +8 boarded; informat date date7.; format date date7.; cards; 219 01MAR94 LON 198 622 01MAR94 FRA 207 132 01MAR94 YYZ 115 271 01MAR94 PAR 138 219 02MAR94 LON 147 622 02MAR94 FRA 176 132 02MAR94 YYZ 106 271 02MAR94 PAR 172 219 03MAR94 LON 197 622 03MAR94 FRA 180 132 03MAR94 YYZ 75 271 03MAR94 PAR 147 219 04MAR94 LON 232 622 04MAR94 FRA 137 132 04MAR94 YYZ 117 271 04MAR94 PAR 146 219 05MAR94 LON 160 622 05MAR94 FRA 185 132 05MAR94 YYZ 157 271 05MAR94 PAR 177 219 06MAR94 LON 163 132 06MAR94 YYZ 150 219 07MAR94 LON 241 622 07MAR94 FRA 210 132 07MAR94 YYZ 164 271 07MAR94 PAR 155 ; data sql.schedule; input flight $3. +5 date date7. +2 dest $3. +3 idnum $4.; format date date7.; informat date date7.; cards; 132 01MAR94 YYZ 1739 132 01MAR94 YYZ 1478 132 01MAR94 YYZ 1130 132 01MAR94 YYZ 1390 132 01MAR94 YYZ 1983 132 01MAR94 YYZ 1111 219 01MAR94 LON 1407 219 01MAR94 LON 1777 219 01MAR94 LON 1103 219 01MAR94 LON 1125 219 01MAR94 LON 1350 219 01MAR94 LON 1332 271 01MAR94 PAR 1439 271 01MAR94 PAR 1442 271 01MAR94 PAR 1132 271 01MAR94 PAR 1411 271 01MAR94 PAR 1988 271 01MAR94 PAR 1443 622 01MAR94 FRA 1545 622 01MAR94 FRA 1890 622 01MAR94 FRA 1116 622 01MAR94 FRA 1221 622 01MAR94 FRA 1433 622 01MAR94 FRA 1352 132 02MAR94 YYZ 1556 132 02MAR94 YYZ 1478 132 02MAR94 YYZ 1113 132 02MAR94 YYZ 1411 132 02MAR94 YYZ 1574 132 02MAR94 YYZ 1111 219 02MAR94 LON 1407 219 02MAR94 LON 1118 219 02MAR94 LON 1132 219 02MAR94 LON 1135 219 02MAR94 LON 1441 219 02MAR94 LON 1332 271 02MAR94 PAR 1739 271 02MAR94 PAR 1442 271 02MAR94 PAR 1103 271 02MAR94 PAR 1413 271 02MAR94 PAR 1115 271 02MAR94 PAR 1443 622 02MAR94 FRA 1439 622 02MAR94 FRA 1890 622 02MAR94 FRA 1124 622 02MAR94 FRA 1368 622 02MAR94 FRA 1477 622 02MAR94 FRA 1352 132 03MAR94 YYZ 1739 132 03MAR94 YYZ 1928 132 03MAR94 YYZ 1425 132 03MAR94 YYZ 1135 132 03MAR94 YYZ 1437 132 03MAR94 YYZ 1111 219 03MAR94 LON 1428 219 03MAR94 LON 1442 219 03MAR94 LON 1130 219 03MAR94 LON 1411 219 03MAR94 LON 1115 219 03MAR94 LON 1332 271 03MAR94 PAR 1905 271 03MAR94 PAR 1118 271 03MAR94 PAR 1970 271 03MAR94 PAR 1125 271 03MAR94 PAR 1983 271 03MAR94 PAR 1443 622 03MAR94 FRA 1545 622 03MAR94 FRA 1830 622 03MAR94 FRA 1414 622 03MAR94 FRA 1368 622 03MAR94 FRA 1431 622 03MAR94 FRA 1352 132 04MAR94 YYZ 1428 132 04MAR94 YYZ 1118 132 04MAR94 YYZ 1103 132 04MAR94 YYZ 1390 132 04MAR94 YYZ 1350 132 04MAR94 YYZ 1111 219 04MAR94 LON 1739 219 04MAR94 LON 1478 219 04MAR94 LON 1130 219 04MAR94 LON 1125 219 04MAR94 LON 1983 219 04MAR94 LON 1332 271 04MAR94 PAR 1407 271 04MAR94 PAR 1410 271 04MAR94 PAR 1094 271 04MAR94 PAR 1411 271 04MAR94 PAR 1115 271 04MAR94 PAR 1443 622 04MAR94 FRA 1545 622 04MAR94 FRA 1890 622 04MAR94 FRA 1116 622 04MAR94 FRA 1221 622 04MAR94 FRA 1433 622 04MAR94 FRA 1352 132 05MAR94 YYZ 1556 132 05MAR94 YYZ 1890 132 05MAR94 YYZ 1113 132 05MAR94 YYZ 1475 132 05MAR94 YYZ 1431 132 05MAR94 YYZ 1111 219 05MAR94 LON 1428 219 05MAR94 LON 1442 219 05MAR94 LON 1422 219 05MAR94 LON 1413 219 05MAR94 LON 1574 219 05MAR94 LON 1332 271 05MAR94 PAR 1739 271 05MAR94 PAR 1928 271 05MAR94 PAR 1103 271 05MAR94 PAR 1477 271 05MAR94 PAR 1433 271 05MAR94 PAR 1443 622 05MAR94 FRA 1545 622 05MAR94 FRA 1830 622 05MAR94 FRA 1970 622 05MAR94 FRA 1441 622 05MAR94 FRA 1350 622 05MAR94 FRA 1352 132 06MAR94 YYZ 1333 132 06MAR94 YYZ 1890 132 06MAR94 YYZ 1414 132 06MAR94 YYZ 1475 132 06MAR94 YYZ 1437 132 06MAR94 YYZ 1111 219 06MAR94 LON 1106 219 06MAR94 LON 1118 219 06MAR94 LON 1425 219 06MAR94 LON 1434 219 06MAR94 LON 1555 219 06MAR94 LON 1332 132 07MAR94 YYZ 1407 132 07MAR94 YYZ 1118 132 07MAR94 YYZ 1094 132 07MAR94 YYZ 1555 132 07MAR94 YYZ 1350 132 07MAR94 YYZ 1111 219 07MAR94 LON 1905 219 07MAR94 LON 1478 219 07MAR94 LON 1124 219 07MAR94 LON 1434 219 07MAR94 LON 1983 219 07MAR94 LON 1332 271 07MAR94 PAR 1410 271 07MAR94 PAR 1777 271 07MAR94 PAR 1103 271 07MAR94 PAR 1574 271 07MAR94 PAR 1115 271 07MAR94 PAR 1443 622 07MAR94 FRA 1107 622 07MAR94 FRA 1890 622 07MAR94 FRA 1425 622 07MAR94 FRA 1475 622 07MAR94 FRA 1433 622 07MAR94 FRA 1352 ; data sql.payroll; input idnum $4. +3 sex $1. +4 jobcode $3. +9 salary 5. +2 birth date7. +2 hired date7.; informat birth date7. hired date7.; format birth date7. hired date7.; cards; 1919 M TA2 34376 12SEP60 04JUN87 1653 F ME2 35108 15OCT64 09AUG90 1400 M ME1 29769 05NOV67 16OCT90 1350 F FA3 32886 31AUG65 29JUL90 1401 M TA3 38822 13DEC50 17NOV85 1499 M ME3 43025 26APR54 07JUN80 1101 M SCP 18723 06JUN62 01OCT90 1333 M PT2 88606 30MAR61 10FEB81 1402 M TA2 32615 17JAN63 02DEC90 1479 F TA3 38785 22DEC68 05OCT89 1403 M ME1 28072 28JAN69 21DEC91 1739 M PT1 66517 25DEC64 27JAN91 1658 M SCP 17943 08APR67 29FEB92 1428 F PT1 68767 04APR60 16NOV91 1782 M ME2 35345 04DEC70 22FEB92 1244 M ME2 36925 31AUG63 17JAN88 1383 M BCK 25823 25JAN68 20OCT92 1574 M FA2 28572 27APR60 20DEC92 1789 M SCP 18326 25JAN57 11APR78 1404 M PT2 91376 24FEB53 01JAN80 1437 F FA3 33104 20SEP60 31AUG84 1639 F TA3 40260 26JUN57 28JAN84 1269 M NA1 41690 03MAY72 28NOV92 1065 M ME2 35090 26JAN44 07JAN87 1876 M TA3 39675 20MAY58 27APR85 1037 F TA1 28558 10APR64 13SEP92 1129 F ME2 34929 08DEC61 17AUG91 1988 M FA3 32217 30NOV59 18SEP84 1405 M SCP 18056 05MAR66 26JAN92 1430 F TA2 32925 28FEB62 27APR87 1983 F FA3 33419 28FEB62 27APR87 1134 F TA2 33462 05MAR69 21DEC88 1118 M PT3 111379 16JAN44 18DEC80 1438 F TA3 39223 15MAR65 18NOV87 1125 F FA2 28888 08NOV68 11DEC87 1475 F FA2 27787 15DEC61 13JUL90 1117 M TA3 39771 05JUN63 13AUG92 1935 F NA2 51081 28MAR54 16OCT81 1124 F FA1 23177 10JUL58 01OCT90 1422 F FA1 22454 04JUN64 06APR91 1616 F TA2 34137 01MAR70 04JUN93 1406 M ME2 35185 08MAR61 17FEB87 1120 M ME1 28619 11SEP72 07OCT93 1094 M FA1 22268 02APR70 17APR91 1389 M BCK 25028 15JUL59 18AUG90 1905 M PT1 65111 16APR72 29MAY92 1407 M PT1 68096 23MAR69 18MAR90 1114 F TA2 32928 18SEP69 27JUN87 1410 M PT2 84685 03MAY67 07NOV86 1439 F PT1 70736 06MAR64 10SEP90 1409 M ME3 41551 19APR50 22OCT81 1408 M TA2 34138 29MAR60 14OCT87 1121 M ME1 29112 26SEP71 07DEC91 1991 F TA1 27645 07MAY72 12DEC92 1102 M TA2 34542 01OCT59 15APR91 1356 M ME2 36869 26SEP57 22FEB83 1545 M PT1 66130 12AUG59 29MAY90 1292 F ME2 36691 28OCT64 02JUL89 1440 F ME2 35757 27SEP62 09APR91 1368 M FA2 27808 11JUN61 03NOV84 1369 M TA2 33705 28DEC61 13MAR87 1411 M FA2 27265 27MAY61 01DEC89 1113 F FA1 22367 15JAN68 17OCT91 1704 M BCK 25465 30AUG66 28JUN87 1900 M ME2 35105 25MAY62 27OCT87 1126 F TA3 40899 28MAY63 21NOV80 1677 M BCK 26007 05NOV63 27MAR89 1441 F FA2 27158 19NOV69 23MAR91 1421 M TA2 33155 08JAN59 28FEB90 1119 M TA1 26924 20JUN62 06SEP88 1834 M BCK 26896 08FEB72 02JUL92 1777 M PT3 109630 23SEP51 21JUN81 1663 M BCK 26452 11JAN67 11AUG91 1106 M PT2 89632 06NOV57 16AUG84 1103 F FA1 23738 16FEB68 23JUL92 1477 M FA2 28566 21MAR64 07MAR88 1476 F TA2 34803 30MAY66 17MAR87 1379 M ME3 42264 08AUG61 10JUN84 1104 M SCP 17946 25APR63 10JUN91 1009 M TA1 28880 02MAR59 26MAR92 1412 M ME1 27799 18JUN56 05DEC91 1115 F FA3 32699 22AUG60 29FEB80 1128 F TA2 32777 23MAY65 20OCT90 1442 F PT2 84536 05SEP66 12APR88 1417 M NA2 52270 27JUN64 07MAR89 1478 M PT2 84203 09AUG59 24OCT90 1673 M BCK 25477 27FEB70 15JUL91 1839 F NA1 43433 29NOV70 03JUL93 1347 M TA3 40079 21SEP67 06SEP84 1423 F ME2 35773 14MAY68 19AUG90 1200 F ME1 27816 10JAN71 14AUG92 1970 F FA1 22615 25SEP64 12MAR91 1521 M ME3 41526 12APR63 13JUL88 1354 F SCP 18335 29MAY71 16JUN92 1424 F FA2 28978 04AUG69 11DEC89 1132 F FA1 22413 30MAY72 22OCT93 1845 M BCK 25996 20NOV59 22MAR80 1556 M PT1 71349 22JUN64 11DEC91 1413 M FA2 27435 16SEP65 02JAN90 1123 F TA1 28407 31OCT72 05DEC92 1907 M TA2 33329 15NOV60 06JUL87 1436 F TA2 34475 11JUN64 12MAR87 1385 M ME3 43900 16JAN62 01APR86 1432 F ME2 35327 03NOV61 10FEB85 1111 M NA1 40586 14JUL73 31OCT92 1116 F FA1 22862 28SEP69 21MAR91 1352 M NA2 53798 02DEC60 16OCT86 1555 F FA2 27499 16MAR68 04JUL92 1038 F TA1 26533 09NOV69 23NOV91 1420 M ME3 43071 19FEB65 22JUL87 1561 M TA2 34514 30NOV63 07OCT87 1434 F FA2 28622 11JUL62 28OCT90 1414 M FA1 23644 24MAR72 12APR92 1112 M TA1 26905 29NOV64 07DEC92 1390 M FA2 27761 19FEB65 23JUN91 1332 M NA1 42178 17SEP70 04JUN91 1890 M PT2 91908 20JUL51 25NOV79 1429 F TA1 27939 28FEB60 07AUG92 1107 M PT2 89977 09JUN54 10FEB79 1908 F TA2 32995 10DEC69 23APR90 1830 F PT2 84471 27MAY57 29JAN83 1882 M ME3 41538 10JUL57 21NOV78 1050 M ME2 35167 14JUL63 24AUG86 1425 F FA1 23979 28DEC71 28FEB93 1928 M PT2 89858 16SEP54 13JUL90 1480 F TA3 39583 03SEP57 25MAR81 1100 M BCK 25004 01DEC60 07MAY88 1995 F ME1 28810 24AUG73 19SEP93 1135 F FA2 27321 20SEP60 31MAR90 1415 M FA2 28278 09MAR58 12FEB88 1076 M PT1 66558 14OCT55 03OCT91 1426 F TA2 32991 05DEC66 25JUN90 1564 F SCP 18833 12APR62 01JUL92 1221 F FA2 27896 22SEP67 04OCT91 1133 M TA1 27701 13JUL66 12FEB92 1435 F TA3 38808 12MAY59 08FEB80 1418 M ME1 28005 29MAR57 06JAN92 1017 M TA3 40858 28DEC57 16OCT81 1443 F NA1 42274 17NOV68 29AUG91 1131 F TA2 32575 26DEC71 19APR91 1427 F TA2 34046 31OCT70 30JAN90 1036 F TA3 39392 19MAY65 23OCT84 1130 F FA1 23916 16MAY71 05JUN92 1127 F TA2 33011 09NOV64 07DEC86 1433 F FA3 32982 08JUL66 17JAN87 1431 F FA3 33230 09JUN64 05APR88 1122 F FA2 27956 01MAY63 27NOV88 1105 M ME2 34805 01MAR62 13AUG90 ; data sql.payroll2; input idnum $4. +3 sex $1. +4 jobcode $3. +9 salary 5. +2 birth date7. +2 hired date7.; informat birth date7. hired date7.; format birth date7. hired date7.; cards; 1639 F TA3 42260 26JUN57 28JAN84 1065 M ME3 38090 26JAN44 07JAN87 1561 M TA3 36514 30NOV63 07OCT87 1221 F FA3 29896 22SEP67 04OCT91 1447 F FA1 22123 07AUG72 29OCT92 1998 M SCP 23100 10SEP70 02NOV92 1036 F TA3 42465 19MAY65 23OCT84 1106 M PT3 94039 06NOV57 16AUG84 1129 F ME3 36758 08DEC61 17AUG91 1350 F FA3 36098 31AUG65 29JUL90 1369 M TA3 36598 28DEC61 13MAR87 1076 M PT1 69742 14OCT55 03OCT91 ; data sql.staff; input idnum $4. +3 lname $15. +2 fname $15. +2 city $15. +2 state $2. +5 hphone $12.; cards; 1919 ADAMS GERALD STAMFORD CT 203/781-1255 1653 ALIBRANDI MARIA BRIDGEPORT CT 203/675-7715 1400 ALHERTANI ABDULLAH NEW YORK NY 212/586-0808 1350 ALVAREZ MERCEDES NEW YORK NY 718/383-1549 1401 ALVAREZ CARLOS PATERSON NJ 201/732-8787 1499 BAREFOOT JOSEPH PRINCETON NJ 201/812-5665 1101 BAUCOM WALTER NEW YORK NY 212/586-8060 1333 BANADYGA JUSTIN STAMFORD CT 203/781-1777 1402 BLALOCK RALPH NEW YORK NY 718/384-2849 1479 BALLETTI MARIE NEW YORK NY 718/384-8816 1403 BOWDEN EARL BRIDGEPORT CT 203/675-3434 1739 BRANCACCIO JOSEPH NEW YORK NY 212/587-1247 1658 BREUHAUS JEREMY NEW YORK NY 212/587-3622 1428 BRADY CHRISTINE STAMFORD CT 203/781-1212 1782 BREWCZAK JAKOB STAMFORD CT 203/781-0019 1244 BUCCI ANTHONY NEW YORK NY 718/383-3334 1383 BURNETTE THOMAS NEW YORK NY 718/384-3569 1574 CAHILL MARSHALL NEW YORK NY 718/383-2338 1789 CARAWAY DAVIS NEW YORK NY 212/587-9000 1404 COHEN LEE NEW YORK NY 718/384-2946 1437 CARTER DOROTHY BRIDGEPORT CT 203/675-4117 1639 CARTER-COHEN KAREN STAMFORD CT 203/781-8839 1269 CASTON FRANKLIN STAMFORD CT 203/781-3335 1065 COPAS FREDERICO NEW YORK NY 718/384-5618 1876 CHIN JACK NEW YORK NY 212/588-5634 1037 CHOW JANE STAMFORD CT 203/781-8868 1129 COUNIHAN BRENDA NEW YORK NY 718/383-2313 1988 COOPER ANTHONY NEW YORK NY 212/587-1228 1405 DACKO JASON PATERSON NJ 201/732-2323 1430 DABROWSKI SANDRA BRIDGEPORT CT 203/675-1647 1983 DEAN SHARON NEW YORK NY 718/384-1647 1134 DELGADO MARIA STAMFORD CT 203/781-1528 1118 DENNIS ROGER NEW YORK NY 718/383-1122 1438 DABBOUSSI KAMILLA STAMFORD CT 203/781-2229 1125 DUNLAP DONNA NEW YORK NY 718/383-2094 1475 ELGES MARGARETE NEW YORK NY 718/383-2828 1117 EDGERTON JOSHUA NEW YORK NY 212/588-1239 1935 FERNANDEZ KATRINA BRIDGEPORT CT 203/675-2962 1124 FIELDS DIANA WHITE PLAINS NY 914/455-2998 1422 FUJIHARA KYOKO PRINCETON NJ 201/812-0902 1616 FUENTAS CARLA NEW YORK NY 718/384-3329 1406 FOSTER GERALD BRIDGEPORT CT 203/675-6363 1120 GARCIA JACK NEW YORK NY 718/384-4930 1094 GOMEZ ALAN BRIDGEPORT CT 203/675-7181 1389 GOLDSTEIN LEVI NEW YORK NY 718/384-9326 1905 GRAHAM ALVIN NEW YORK NY 212/586-8815 1407 GREGORSKI DANIEL MT. VERNON NY 914/468-1616 1114 GREENWALD JANICE NEW YORK NY 212/588-1092 1410 HARRIS CHARLES STAMFORD CT 203/781-0937 1439 HASENHAUER CHRISTINA BRIDGEPORT CT 203/675-4987 1409 HAVELKA RAYMOND STAMFORD CT 203/781-9697 1408 HENDERSON WILLIAM PRINCETON NJ 201/812-4789 1121 HERNANDEZ ROBERTO NEW YORK NY 718/384-3313 1991 HOWARD GRETCHEN BRIDGEPORT CT 203/675-0007 1102 HERMANN JOACHIM WHITE PLAINS NY 914/455-0976 1356 HOWARD MICHAEL NEW YORK NY 212/586-8411 1545 HERRERO CLYDE STAMFORD CT 203/781-1119 1292 HUNTER HELEN BRIDGEPORT CT 203/675-4830 1440 JACKSON LAURA STAMFORD CT 203/781-0088 1368 JEPSEN RONALD STAMFORD CT 203/781-8413 1369 JONSON ANTHONY NEW YORK NY 212/587-5385 1411 JOHNSON JACKSON PATERSON NJ 201/732-3678 1113 JONES LESLIE NEW YORK NY 718/383-3003 1704 JONES NATHAN NEW YORK NY 718/384-0049 1900 KING WILLIAM NEW YORK NY 718/383-3698 1126 KIMANI ANNE NEW YORK NY 212/586-1229 1677 KRAMER JACKSON BRIDGEPORT CT 203/675-7432 1441 LAWRENCE KATHY PRINCETON NJ 201/812-3337 1421 LEE RUSSELL MT. VERNON NY 914/468-9143 1119 LI JEFF NEW YORK NY 212/586-2344 1834 LEBLANC RUSSELL NEW YORK NY 718/384-0040 1777 LUFKIN ROY NEW YORK NY 718/383-4413 1663 MARKS JOHN NEW YORK NY 212/587-7742 1106 MARSHBURN JASPER STAMFORD CT 203/781-1457 1103 MCDANIEL RONDA NEW YORK NY 212/586-0013 1477 MEYERS PRESTON BRIDGEPORT CT 203/675-8125 1476 MONROE JOYCE STAMFORD CT 203/781-2837 1379 MORGAN ALFRED STAMFORD CT 203/781-2216 1104 MORGAN CHRISTOPHER NEW YORK NY 718/383-9740 1009 MORGAN GEORGE NEW YORK NY 212/586-7753 1412 MURPHEY JOHN PRINCETON NJ 201/812-4414 1115 MURPHY ALICE NEW YORK NY 718/384-1982 1128 NELSON FELICIA BRIDGEPORT CT 203/675-1166 1442 NEWKIRK SANDRA PRINCETON NJ 201/812-3331 1417 NEWKIRK WILLIAM PATERSON NJ 201/732-6611 1478 NEWTON JAMES NEW YORK NY 212/587-5549 1673 NICHOLLS HENRY STAMFORD CT 203/781-7770 1839 NORRIS DIANE NEW YORK NY 718/384-1767 1347 O'NEAL BRYAN NEW YORK NY 718/384-0230 1423 OSWALD LESLIE MT. VERNON NY 914/468-9171 1200 OVERMAN MICHELLE STAMFORD CT 203/781-1835 1970 PARKER ANNE NEW YORK NY 718/383-3895 1521 PARKER JAY NEW YORK NY 212/587-7603 1354 PARKER MARY WHITE PLAINS NY 914/455-2337 1424 PATTERSON RENEE NEW YORK NY 212/587-8991 1132 PEARCE CAROL NEW YORK NY 718/384-1986 1845 PEARSON JAMES NEW YORK NY 718/384-2311 1556 PENNINGTON MICHAEL NEW YORK NY 718/383-5681 1413 PETERS RANDALL PRINCETON NJ 201/812-2478 1123 PETERSON SUZANNE NEW YORK NY 718/383-0077 1907 PHELPS WILLIAM STAMFORD CT 203/781-1118 1436 PORTER SUSAN NEW YORK NY 718/383-5777 1385 RAYNOR MILTON BRIDGEPORT CT 203/675-2846 1432 REED MARILYN MT. VERNON NY 914/468-5454 1111 RHODES JEREMY PRINCETON NJ 201/812-1837 1116 RICHARDS CASEY NEW YORK NY 212/587-1224 1352 RIVERS SIMON NEW YORK NY 718/383-3345 1555 RODRIGUEZ JULIA BRIDGEPORT CT 203/675-2401 1038 RODRIGUEZ MARIA BRIDGEPORT CT 203/675-2048 1420 ROUSE JEREMY PATERSON NJ 201/732-9834 1561 SANDERS RAYMOND NEW YORK NY 212/588-6615 1434 SANDERSON EDITH STAMFORD CT 203/781-1333 1414 SANDERSON NATHAN BRIDGEPORT CT 203/675-1715 1112 SAYERS RANDY NEW YORK NY 718/384-4895 1390 SMART JONATHAN NEW YORK NY 718/383-1141 1332 STEPHENSON ADAM BRIDGEPORT CT 203/675-1497 1890 STEPHENSON ROBERT NEW YORK NY 718/384-9874 1429 THOMPSON ALICE STAMFORD CT 203/781-3857 1107 THOMPSON WAYNE NEW YORK NY 718/384-3785 1908 TRENTON MELISSA NEW YORK NY 212/586-6262 1830 TRIPP KATHY BRIDGEPORT CT 203/675-2479 1882 TUCKER ALAN NEW YORK NY 718/384-0216 1050 TUTTLE THOMAS WHITE PLAINS NY 914/455-2119 1425 UNDERWOOD JENNY STAMFORD CT 203/781-0978 1928 UPCHURCH LARRY WHITE PLAINS NY 914/455-5009 1480 UPDIKE THERESA NEW YORK NY 212/587-8729 1100 VANDEUSEN RICHARD NEW YORK NY 212/586-2531 1995 VARNER ELIZABETH NEW YORK NY 718/384-7113 1135 VEGA ANNA NEW YORK NY 718/384-5913 1415 VEGA FRANKLIN NEW YORK NY 718/384-2823 1076 VENTER RANDALL NEW YORK NY 718/383-2321 1426 VICK THERESA PRINCETON NJ 201/812-2424 1564 WALTERS ANNE NEW YORK NY 212/587-3257 1221 WALTERS DIANE NEW YORK NY 718/384-1918 1133 WANG CHIN NEW YORK NY 212/587-1956 1435 WARD ELAINE NEW YORK NY 718/383-4987 1418 WATSON BERNARD NEW YORK NY 718/383-1298 1017 WELCH DARIUS NEW YORK NY 212/586-5535 1443 WELLS AGNES STAMFORD CT 203/781-5546 1131 WELLS NADINE NEW YORK NY 718/383-1045 1427 WHALEY CAROLYN MT. VERNON NY 914/468-4528 1036 WONG LESLIE NEW YORK NY 212/587-2570 1130 WOOD DEBORAH NEW YORK NY 212/587-0013 1127 WOOD SANDRA NEW YORK NY 212/587-2881 1433 YANCEY ROBIN PRINCETON NJ 201/812-1874 1431 YOUNG DEBORAH STAMFORD CT 203/781-2987 1122 YOUNG JOANN NEW YORK NY 718/384-2021 1105 YOUNG LAWRENCE NEW YORK NY 718/384-0008 ; /*'*/ data sql.superv; input supid $4. +8 state $2. +5 jobcat $2.; label supid='Supervisor Id' jobcat='Job Category'; cards; 1677 CT BC 1834 NY BC 1431 CT FA 1433 NJ FA 1983 NY FA 1385 CT ME 1420 NJ ME 1882 NY ME 1935 CT NA 1417 NJ NA 1352 NY NA 1106 CT PT 1442 NJ PT 1118 NY PT 1405 NJ SC 1564 NY SC 1639 CT TA 1401 NJ TA 1126 NY TA ; /* Example query. With calculation. */ proc sql outobs=15; title 'Percentage of Capacity for Each Flight'; select flight, date, (boarded/capacity)*100 as pctfull from sql.march; quit; /* Example query. With case. */ proc sql outobs=15; title 'Percentage of Capacity for Each Flight'; select flight, date, (boarded/capacity)*100 as pctfull, case when calculated pctfull<60 then '****' else ' ' end from sql.march; quit; /* Example query. With format and label. */ proc sql outobs=15; title 'Percentage of Capacity for Each Flight'; select flight, date, boarded/capacity*100 as pctfull format=4.1 label='Percent Full' from sql.march; quit; /* Example query. With ORDER BY. */ proc sql outobs=15; title 'Percentage of Capacity for Each Flight'; select flight, date, boarded/capacity*100 as pctfull format=4.1 label='Percent Full' from sql.march order by pctfull; proc sql; title; select avg(boarded/capacity*100) as avgfull format=4.1 label='Average Percent Full' from sql.march; quit; /* Example query. Using count(*) for number of rows. */ proc sql; title; select count(*) label='Number of Rows' from sql.march; quit; /* Example query. Using min() and max() with no GROUP BY. */ proc sql; title; select min(boarded) label='Fewest Passengers', max(boarded) label='Most Passengers' from sql.march; quit; /* Example query. Using sum() with no GROUP BY. */ proc sql; title; select sum(boarded) label='Total' from sql.march; quit; /* Example query. Using avg() with GROUP BY. */ proc sql; title 'Average Percentage of Capacity Per Day - All Flights'; select date, avg(boarded/capacity*100) as avgfull format=4.1 label='Avg Pct' from sql.march group by date; quit; /* Example query. Using count() with GROUP BY. */ proc sql; title 'Number of Flights Each Date'; select date, count(date) from sql.march group by date; quit; /* Example query. Using avg() with GROUP BY and HAVING. */ proc sql; title 'Dates that Average over 70% Capacity for All Flights'; select date, avg((boarded/capacity)*100) as avgfull format=4.1 label='Avg Pct' from sql.march group by date having avgfull>70; quit; /* Example query. Selecting one flight. */ proc sql; title 'Percentage of Capacity for Flight 132'; select flight, date, (boarded/capacity)*100 as pctfull format=4.1 label='Percent Full' from sql.march where flight='132' order by pctfull; quit; /* Example query. Using avg() and GROUP BY, HAVING, excluding one flight. */ proc sql; title 'Dates that Average over 70% Capacity for All Flights Except Flight 622'; select date, avg(boarded/capacity*100) as avgfull format=4.1 label='Avg Pct' from sql.march where flight ^= '622' group by date having avgfull>70; quit; /* Example query. Two-tiered select. */ proc sql; title; select flight, avg(pctfull) format=4.1 label='Avg Pct of Capacity' from (select flight, date, boarded/capacity*100 as pctfull format=4.1 label='Percent Full' from sql.march) group by flight; quit; /* Example query. Using INTERSECT */ proc sql; title; select supid as idnum from sql.superv intersect select idnum from sql.schedule where date='01MAR94'd; quit; /* Example query. Using EXCEPT */ proc sql; title 'The SAS System'; select flight, dest from sql.march except select flight, dest from sql.internat; quit; /* Example query. Using EXCEPT ALL */ proc sql outobs=15; title; select idnum from sql.staff except all select supid as idnum from sql.superv; quit; /* Example query. Using UNION */ proc sql; title; select 'Average Salary for Pilots: ', avg(salary) format=dollar7. from sql.payroll where jobcode contains 'PT' union select 'Average Salary for Mechanics: ', avg(salary) format=dollar7. from sql.payroll where jobcode contains 'ME'; quit; /* Example query. Using OUTER UNION */ data sql.me1; input idnum $4. +3 jobcode $3. +9 salary; cards; 1400 ME1 29769 1403 ME1 28072 1120 ME1 28619 1121 ME1 29112 1412 ME1 27799 1200 ME1 27816 1995 ME1 28810 1418 ME1 28005 ; data sql.me2; input idnum $4. +3 jobcode $3. +9 salary; cards; 1653 ME2 35108 1782 ME2 35345 1244 ME2 36925 1065 ME2 35090 1129 ME2 34929 1406 ME2 35185 1356 ME2 36869 1292 ME2 36691 1440 ME2 35757 1900 ME2 35105 1423 ME2 35773 1432 ME2 35327 1050 ME2 35167 1105 ME2 34805 ; data sql.me3; input idnum $4. +3 jobcode $3. +9 salary; cards; 1499 ME3 43025 1409 ME3 41551 1379 ME3 42264 1521 ME3 41526 1385 ME3 43900 1420 ME3 43071 1882 ME3 41538 ; proc sql; title; select * from sql.me1 outer union select * from sql.me2 outer union select * from sql.me3; quit; /* Example query. Using OUTER UNION CORR */ proc sql; title; select * from sql.me1 outer union corr select * from sql.me2 outer union corr select * from sql.me3; quit; /* Creating table from query. */ proc sql; title 'Percentage of Capacity for Each Flight'; create table sql.capacity as select flight, date, dest, boarded/capacity*100 as pctfull format=4.1 label='Percent Full' from sql.march; reset outobs=15; /* Used to modify SQL options */ select * from sql.capacity; quit; /* Create table LIKE another table */ proc sql; title; create table sql.newintl like sql.internat; quit; /* INSERT INTO */ proc sql; insert into sql.newintl select flight, date, dest, boarded from sql.march where flight in ('132','219','271','622'); select * from sql.newintl(obs=10); quit; /* INSERT INTO */ proc sql; title; insert into sql.newintl set flight='501', date='01MAR94'd, dest='MXC', boarded=150 set flight='501', date='02MAR94'd, dest='MXC', boarded=109; select * from sql.newintl(firstobs=19 obs=28); quit; /* INSERT INTO */ proc sql; title; insert into sql.newintl values ('779','02MAR94'd,'SJU',123) values ('779','03MAR94'd,'SJU',144); select * from sql.newintl(firstobs=21 obs=30); quit; /* UPDATE */ proc sql; create table new as select * from sql.payroll; update new set salary=salary*1.03; reset outobs=5; title 'Salaries in SQL.PAYROLL After the Update'; select * from new; quit; /* UPDATE */ proc sql; create table new as select * from sql.payroll; update new set salary=salary*1.03 where jobcode like '__1'; update new set salary=salary*1.05 where jobcode in ('BCK','SCP'); reset outobs=5; title 'Salaries in SQL.PAYROLL After the Update'; select * from new where jobcode like '__1' or jobcode in ('BCK','SCP'); quit; /* ALTER TABLE */ proc sql; title; create table new as select * from sql.payroll; reset outobs=15; alter table new add age_hir num label='Age When Hired' format=2.; select * from new; quit; /* ALTER TABLE and UPDATE*/ proc sql; title; create table new as select * from sql.payroll; reset outobs=15; alter table new add age_hir num label='Age at Hire' format=2.; update new set age_hir=int(hired-birth)/365.25; select * from new; quit; /* ALTER TABLE */ proc sql; title; create table new as select * from sql.payroll; reset outobs=5; alter table new modify birth format=ddmmyy8., hired format=ddmmyy8.; select birth, hired from new; quit; /* ALTER TABLE and UPDATE */ proc sql; title; create table new as select * from sql.payroll; reset outobs=5; alter table new modify idnum char(5); update new set idnum=substr(jobcode,1,1)||idnum; select idnum, jobcode from new; quit; /* Example query. COUNT() and GROUP BY. CREATE VIEW. */ proc sql; title 'Current Summary Information for Each Job Category'; create view sql.jobs as select jobcode, count(jobcode) as number label='Number', avg(int((today()-birth)/365.25)) as avgage format=2. label='Average Age', avg(salary) as avgsal format=dollar8. label='Average Salary' from payroll group by jobcode; select * from sql.jobs; quit; title 'The SAS System'; proc means data=sql.jobs mean maxdec=2; var avgage avgsal; run; /* Example query. INNER JOIN. */ proc sql; title; select lname, fname, salary format=dollar8. from sql.staff, sql.payroll where staff.idnum=payroll.idnum and salary>70000; quit; /* Example query. INNER JOIN. */ proc sql; title; select march.date, march.flight, miles, delaycat from sql.delay, sql.march where delay.flight=march.flight and delay.date=march.date and delay.date='01MAR94'd; quit; /* LEFT JOIN */ data sql.payroll2; input idnum $4. +3 sex $1. +4 jobcode $3. +9 salary 5. +2 birth date7. +2 hired date7.; informat birth date7. hired date7.; format birth date7. hired date7.; cards; 1639 F TA3 42260 26JUN57 28JAN84 1065 M ME3 38090 26JAN44 07JAN87 1561 M TA3 36514 30NOV63 07OCT87 1221 F FA3 29896 22SEP67 04OCT91 1447 F FA1 22123 07AUG72 29OCT92 1998 M SCP 23100 10SEP70 02NOV92 1036 F TA3 42465 19MAY65 23OCT84 1106 M PT3 94039 06NOV57 16AUG84 1129 F ME3 36758 08DEC61 17AUG91 1350 F FA3 36098 31AUG65 29JUL90 1369 M TA3 36598 28DEC61 13MAR87 1076 M PT1 69742 14OCT55 03OCT91 ; title 'The SAS System'; proc sql outobs=10; select p.idnum, p.jobcode, p.salary, p2.jobcode label='New Jobcode', p2.salary label='New Salary' from sql.payroll as p left join sql.payroll2 as p2 on p.idnum=p2.idnum; quit; /* LEFT JOIN */ proc sql outobs=16; select p.idnum, p.jobcode, p.salary, p2.jobcode label='New Jobcode', p2.salary label='New Salary' from sql.payroll p left join sql.payroll2 p2 on p.idnum=p2.idnum where p2.jobcode contains 'TA'; quit; /* LEFT JOIN with COALESCE() */ proc sql outobs=10; select p.idnum, coalesce(p2.jobcode,p.jobcode) label='Current Jobcode', coalesce(p2.salary,p.salary) label='Current Salary' from sql.payroll p left join sql.payroll2 p2 on p.idnum=p2.idnum; quit; /* Using data step MERGE BY */ data fltsupe; input flight $ supe $; cards; 145 Kang 150 Miller 155 Evanko ; data fltdest; input flight $ dest $; cards; 145 Brussels 150 Paris 155 Honolulu ; data merged; merge fltsupe fltdest; by flight; run; proc print data=merged noobs; title 'Table MERGED'; run; /* Data step MERGE BY, non-identical set */ data fltsupe; input flight $ supe $; cards; 145 Kang 150 Miller 155 Evanko 157 Lei ; data fltdest; input flight $ dest $; cards; 145 Brussels 150 Paris 165 Seattle ; data merged; merge fltsupe fltdest; by flight; run; proc print data=merged noobs; title 'Table MERGED'; run; /* Data step MERGE BY. Non-identical set */ data fltsupe; input flight $ supe $; cards; 145 Kang 145 Ramirez 150 Miller 150 Picard 155 Evanko 157 Lei ; data fltdest; input flight $ dest $; cards; 145 Brussels 145 Edmonton 150 Paris 150 Madrid 165 Seattle ; data merged; merge fltsupe fltdest; by flight; run; proc print data=merged noobs; title 'Table MERGED'; run; /* Using PROC SQL vs. MERGE BY */ data fltsupe; input flight $ supe $; cards; 145 Kang 145 Ramirez 150 Miller 150 Picard 155 Evanko 157 Lei ; data fltdest; input flight $ dest $; cards; 145 Brussels 145 Edmonton 150 Paris 150 Madrid 165 Seattle ; title; proc sql; select * from fltsupe s, fltdest d where s.flight=d.flight; quit; /* Sub-query. */ title; proc sql outobs=10; select flight, date, dest, boarded from sql.march where flight in (select flight from sql.delay where destype='International'); quit; proc sql outobs=10; select * from sql.march where 'International' in (select destype from sql.delay where march.flight=delay.flight); quit; /* Sub-query. */ proc sql outobs=10; select * from sql.march where exists (select * from sql.delay where march.flight=delay.flight and destype='International'); quit; /* Sub-query */ proc sql; select * from sql.payroll where idnum in (select supid from sql.superv where supid in (select idnum from sql.schedule where schedule.flight='132' and schedule.date='01MAR94'd)); quit; /* Library DICTIONARY. */ title 'All the Permanent Tables and Views in this Book'; proc sql; select libname, memname, memtype, nobs from dictionary.tables where libname='SQL'; quit; title; title 'All Tables that Contain a DEST Column'; proc sql; select libname, memname from dictionary.columns where name='DEST' and libname='SQL'; quit; title; /* PROC REPORT */ title 'Number of SAS Files In Each Library'; proc report data=sashelp.vtable nowindows headskip headline; column libname (n); define libname / group; run; title; /* Query on slide NO. 3,5: Performing Queries Using PROC SQL (1) */ proc sql outobs=10; select idnum, jobcode, salary, salary*.06 as bonus from sql.payroll where salary<32000 order by jobcode; quit; /* Query on slide NO. 7: Performing Queries Using PROC SQL (5) Querying multiple tables (2) */ proc sql; select payroll.idnum, payroll.salary, payroll2.salary as newsalary from sql.payroll, sql.payroll2 where payroll.idnum=payroll2.idnum order by payroll.idnum; quit; /* Query on slide NO. 8: Performing Queries Using PROC SQL (6) Summarizing groups of data */ proc sql; title "Passengers carried in each day"; select date, sum(boarded) as DayCarry from sql.march group by date; quit; title; /* Query on slide NO. 11: Performing Queries Using PROC SQL (9) Creating output tables (2) */ proc sql; create table marchdc as select date, sum(boarded) as DayCarry from sql.march group by date; quit; /* Query on slide NO. 12: Performing Queries Using PROC SQL (10) Additional features Using GROUP BY, HAVING */ proc sql; select jobcode, avg(salary) as Avg from sql.payroll group by jobcode having avg(salary)>40000 order by jobcode; quit; /* Query on slide NO. 14: Performing Advanced Queries Using PROC SQL (2) Displaying all columns */ proc sql; select * from sql.payroll2; quit; /* FEEDBACK option: transform SELECT * to detailed column list. */ proc sql feedback; select * from sql.payroll2; quit; /* Query on slide NO. 15: Performing Advanced Queries Using PROC SQL (3) Limiting the number of rows displayed */ proc sql outobs=10; select idnum, salary from sql.payroll; quit; /* Query on slide NO. 16: Performing Advanced Queries Using PROC SQL (4) Eliminating duplicate rows from output */ proc sql; select distinct flight, miles from sql.march order by 1; quit; /* Query on slide NO. 17: Performing Advanced Queries Using PROC SQL (5) Subsetting rows by using conditional operators */ proc sql outobs=10; select idnum, jobcode, salary from sql.payroll where salary between 60000 and 80000 order by salary desc; select distinct jobcode from sql.payroll where jobcode contains "TA"; select idnum, jobcode, salary from sql.payroll where jobcode in ("TA1", "PT1"); quit; data new; set sql.payroll; if birth in ("15OCT1964"d, "30MAR1961"d) then birth=.; run; proc sql; select idnum, salary from new where birth is null quit; /* Query on slide NO. 18: Performing Advanced Queries Using PROC SQL (6) Subsetting rows by using conditional operators (2) Using the LIKE Operator to Select a Pattern */ proc sql; select * from sql.staff where lname like '%SON'; quit; /* Query on slide NO. 19: Performing Advanced Queries Using PROC SQL (7) Subsetting rows by using calculated values */ proc sql outobs=10; select idnum, jobcode, salary, salary*.06 as bonus from sql.payroll where calculated bonus < 1000 order by jobcode; quit; /* Query on slide NO. 20: Performing Advanced Queries Using PROC SQL (8) Enhancing query output */ proc sql outobs=15; title 'Current Bonus Information'; title2 'Employees with Salaries > $75,000'; select idnum label='Employee ID', jobcode label='Job Code', salary, 'bonus is:', salary * .10 format=dollar12.2 from sql.payroll where salary>75000 order by salary desc; quit; title; /* Query on slide NO. 23: Performing Advanced Queries Using PROC SQL (11) Points to remember (3) */ proc sql; select * from sql.payroll2 group by jobcode; quit; /* Query on slide NO. 26: Generating a Cartesian Product No WHERE clause. */ data one; input X A $; datalines; 1 a 2 b 4 d ; data two; input X B $; datalines; 2 x 3 y 5 v ; proc sql; title 'Cartesian product'; select * from one, two; quit; /* Note the duplicated column "X" */ /* Query on slide NO. 27: Using Inner Joins */ proc sql; title 'Inner join'; select * from one, two where one.x = two.x; quit; /* Note also the duplicated column "X" */ /* Queries on slide NO. 29: Using Outer Joins (2) */ proc sql; /* Left join */ title 'Left join'; select * from one LEFT JOIN two ON one.x=two.x; /* Right join */ title 'Right join'; select * from one RIGHT JOIN two ON one.x=two.x; /* Full join */ title 'Full join'; select * from one FULL JOIN two ON one.x=two.x; quit; /* Queries on slide NO. 31: Comparing SQL Joins and DATA Step Match-Merges (2) When all of the values match */ data one; input X A $; datalines; 1 a 2 b 3 c ; data two; input X B $; datalines; 1 x 2 y 3 z ; data merged; merge one two; by x; run; proc print;run; /* Inner Join */ proc sql; select one.x, a, b from one, two where one.x=two.x order by x; quit; /* Queries on slide NO. 32: Comparing SQL Joins and DATA Step Match-Merges (3) When only some of the values match */ data three; input X A $; datalines; 1 a1 2 b2 4 d4 ; data four; input X B $; datalines; 2 x2 3 y3 5 v5 ; title 'Data step full outer join: BY column overlayed'; data merged; merge three four; by x; run; proc print;run; title 'PROC SQL full outer join: common column not overlayed'; proc sql; select three.x, a, b from three FULL JOIN four on three.x=four.x order by x; quit; /* Queries on slide NO. 33: Comparing SQL Joins and DATA Step Match-Merges (4) When only some of the values match: using the COALESCE function */ proc sql; title 'PROC SQL full join: using the COALESCE function'; select coalesce(three.x, four.x) as X, a, b from three full join four on three.x = four.x; quit; /* Extra: using data step for inner join, left join, right join */ title 'Data step inner join'; data merged; merge three(in=three) four(in=four); by x; if three and four; run; proc print;run; title 'Data step left outer join: BY column overlayed'; data merged; merge three(in=three) four(in=four); by x; if three; run; proc print;run; title; /* Queries on slide NO. 39: Using the EXCEPT Set Operator (1) Using the EXCEPT operator alone (1) */ data one; input X A $; datalines; 1 a 1 a 1 b 2 c 3 v 4 c 6 g ; data two; input X B $; datalines; 1 x 2 y 3 z 3 v 5 w ; run; proc sql; title 'EXCEPT: Unique rows except those in second table'; select * from one except select * from two; quit; /* Queries on slide NO. 44: Using the EXCEPT Set Operator (6) Using the keyword ALL with the EXCEPT operator (1) */ proc sql; title 'EXCEPT ALL: one pass'; select * from one EXCEPT ALL select * from two; quit; /* Queries on slide NO. 46: Using the EXCEPT Set Operator (8) Using the keyword CORR with the EXCEPT operator (1) Only same named variables remain. */ proc sql; title 'EXCEPT CORR: Unique rows, column name matching'; select * from one EXCEPT CORR select * from two; quit; /* Queries on slide NO. 48: Using the EXCEPT Set Operator (10) Using the keywords ALL and CORR with the EXCEPT operator (1) */ proc sql; title 'EXCEPT ALL CORR: column name matching and only one pass'; select * from one EXCEPT ALL CORR select * from two; quit; /* Queries on slide NO. 50: Using the INTERSECT Set Operator (1) Using the INTERSECT operator Alone */ proc sql; title 'INTERSECT: unique rows, overlaid columns'; select * from one INTERSECT select * from two; quit; /* Queries on slide NO. 51: Using the INTERSECT Set Operator (2) Using the keyword ALL with the INTERSECT operator */ proc sql; title 'INTERSECT ALL: one pass, overlaid columns'; select * from one INTERSECT ALL select * from two; quit; /* Queries on slide NO. 52: Using the INTERSECT Set Operator (3) Using the keyword CORR with the INTERSECT operator */ proc sql; title 'INTERSECT CORR: Matched columns, unique rows'; select * from one INTERSECT CORR select * from two; quit; /* Queries on slide NO. 53: Using the INTERSECT Set Operator (4) Using the keywords ALL and CORR with the INTERSECT operator */ proc sql; title 'INTERSECT CORR: Matched columns, one pass'; select * from one INTERSECT ALL CORR select * from two; quit; /* Queries on slide NO. 54: Using the UNION Set Operator (1) Using the UNION operator alone */ proc sql; title 'UNION: overlaid columns, unique rows'; select * from one UNION select * from two; quit; /* Queries on slide NO. 55: Using the UNION Set Operator (2) Using the keyword ALL with the UNION operator Note the duplicated "1 a" and "3 v". */ proc sql; title 'UNION ALL: overlaid columns, one pass'; select * from one UNION ALL select * from two; quit; /* Queries on slide NO. 56: Using the UNION Set Operator (3) Using the keyword CORR with the UNION operator */ proc sql; title 'UNION CORR: matched columns, unique rows'; select * from one UNION CORR select * from two; quit; /* Queries on slide NO. 57: Using the UNION Set Operator (4) Using the keywords ALL and CORR with the UNION operator */ proc sql; title 'UNION ALL CORR: matched columns, one pass'; select * from one UNION ALL CORR select * from two; quit; /* Queries on slide NO. 59: Using the OUTER UNION Set Operator (2) Using the OUTER UNION operator alone */ proc sql; title 'OUTER UNION: all columns(not overlaid), all rows'; select * from one OUTER UNION select * from two; quit; /* Queries on slide NO. 60: Using the OUTER UNION Set Operator (3) Using the keyword CORR with the OUTER UNION operator */ proc sql; title 'OUTER UNION CORR: all columns(matched), all rows'; select * from one OUTER UNION CORR select * from two; quit; /* Queries on slide NO. 61: Data step SET and SQL OUTER UNION CORR. */ title 'UNION: using SQL'; proc sql; create table three as select * from one outer union corr select * from two; select * from three; quit; title 'UNION: using data step'; data three; set one two; run; proc print data=three noobs; run; title; /* Queries on slide NO. 63: Two sided set difference. */ proc sql; title 'Two sided set difference'; (select * from one except select * from two ) union (select * from two except select * from one ); quit;