libname mydblib oracle user=testuser password=testpass;
title 'Total Salary by Jobcode';
proc sql;
select jobcode label='Jobcode',
sum(salary) as total
label='Total for Group'
format=dollar11.2
from mydblib.payroll
group by jobcode;
quit;
Total Salary by Jobcode
Total for
Jobcode Group
BCK $232,148.00
FA1 $253,433.00
FA2 $447,790.00
FA3 $230,537.00
ME1 $228,002.00
ME2 $498,076.00
ME3 $296,875.00
NA1 $210,161.00
NA2 $157,149.00
PT1 $543,264.00
PT2 $879,252.00
PT3 $21,009.00
SCP $128,162.00
TA1 $249,492.00
TA2 $671,499.00
TA3 $476,155.00libname mydblib oracle user=testuser password=testpass;
title 'Flights to London and Frankfurt';
proc sql;
select dates format=datetime9.,
dest from mydblib.delay
where (dest eq "FRA") or
(dest eq "LON")
order by dest;
quit;libname mydblib db2 ssid=db2;
proc sql;
title 'International Flights by Flight Number';
title2 'with More Than 200 Passengers';
select flight label="Flight Number",
dates label="Departure Date"
format datetime9.,
dest label="Destination",
boarded label="Number Boarded"
from mydblib.internat
where boarded > 200
order by flight;
quit;libname mydblib oracle user=testuser password=testpass;
title 'Employees with salary greater than $40,000';
options obs=20;
proc sql;
select a.lname, a.fname, b.salary
format=dollar10.2
from mydblib.staff a, mydblib.payroll b
where (a.idnum eq b.idnum) and
(b.salary gt 40000);
quit;
Employees with salary greater than $40,000
LNAME FNAME SALARY
--------------------------------------------
WELCH DARIUS $40,858.00
VENTER RANDALL $66,558.00
THOMPSON WAYNE $89,977.00
RHODES JEREMY $40,586.00
DENNIS ROGER $111379.00
KIMANI ANNE $40,899.00
O'NEAL BRYAN $40,079.00
RIVERS SIMON $53,798.00
COHEN LEE $91,376.00
GREGORSKI DANIEL $68,096.00
NEWKIRK WILLIAM $52,279.00
ROUSE JEREMY $43,071.00
libname mydblib db2 ssid=db2;
title "Delayed International Flights in March";
proc sql;
select distinct march.flight, march.dates format datetime9.,
delay format=2.0
from mydblib.march, mydblib.delay,
mydblib.internat
where march.flight=delay.flight and
march.dates=delay.dates and
march.flight=internat.flight and
delay>0
order by delay descending;
quit;
Delayed International Flights in March
FLIGHT DATES DELAY
------------------------
622 04MAR1998 30
219 06MAR1998 27
622 07MAR1998 21
219 01MAR1998 18
219 02MAR1998 18
219 07MAR1998 15
132 01MAR1998 14
132 06MAR1998 7
132 03MAR1998 6
271 01MAR1998 5
132 02MAR1998 5
271 04MAR1998 5
271 05MAR1998 5
271 02MAR1998 4
219 03MAR1998 4
271 07MAR1998 4
219 04MAR1998 3
132 05MAR1998 3
219 05MAR1998 3
271 03MAR1998 2libname mydblib oracle user=testuser password=testpass;
title "Payrolls 1 & 2";
proc sql;
select idnum, sex, jobcode, salary,
birth format datetime9., hired format datetime9.
from mydblib.payroll
outer union corr
select *
from mydblib.payroll2
order by idnum, jobcode, salary;
quit;Payrolls 1 and 2 IDNUM SEX JOBCODE SALARY BIRTH HIRED --------------------------------------------------- 1009 M TA1 28880 02MAR1959 26MAR1992 1017 M TA3 40858 28DEC1957 16OCT1981 1036 F TA3 39392 19MAY1965 23OCT1984 1036 F TA3 42465 19MAY1965 23OCT1984 1037 F TA1 28558 10APR1964 13SEP1992 1038 F TA1 26533 09NOV1969 23NOV1991 1050 M ME2 35167 14JUL1963 24AUG1986 1065 M ME2 35090 26JAN1944 07JAN1987 1065 M ME3 38090 26JAN1944 07JAN1987 1076 M PT1 66558 14OCT1955 03OCT1991 1076 M PT1 69742 14OCT1955 03OCT1991 1094 M FA1 22268 02APR1970 17APR1991 1100 M BCK 25004 01DEC1960 07MAY1988 1101 M SCP 18723 06JUN1962 01OCT1990 1102 M TA2 34542 01OCT1959 15APR1991 1103 F FA1 23738 16FEB1968 23JUL1992 1104 M SCP 17946 25APR1963 10JUN1991 1105 M ME2 34805 01MAR1962 13AUG1990
libname mydblib db2 ssid=db2;
proc sql;
insert into mydblib.superv
values('1588','NY','FA');
quit;
proc print data=mydblib.superv;
title "New Row in AIRLINE.SUPERV";
run;New Row in AIRLINE.SUPERV OBS SUPID STATE JOBCAT 1 1677 CT BC 2 1834 NY BC 3 1431 CT FA 4 1433 NJ FA 5 1983 NY FA 6 1385 CT ME 7 1420 NJ ME 8 1882 NY ME 9 1935 CT NA 10 1417 NJ NA 11 1352 NY NA 12 1106 CT PT 13 1442 NJ PT 14 1118 NY PT 15 1405 NJ SC 16 1564 NY SC 17 1639 CT TA 18 1401 NJ TA 19 1126 NY TA 20 1588 NY FA
libname mydblib db2 ssid=db2;
proc sql;
delete from mydblib.staff
where state='CT';
quit;
options obs=20;
proc print data=mydblib.staff;
title "AIRLINE.STAFF After Deleting Connecticut Employees";
run;
AIRLINE.STAFF After Deleting Connecticut Employees
OBS IDNUM LNAME FNAME CITY STATE HPHONE
1 1400 ALHERTANI ABDULLAH NEW YORK NY 212/586-0808
2 1350 ALVAREZ MERCEDES NEW YORK NY 718/383-1549
3 1401 ALVAREZ CARLOS PATERSON NJ 201/732-8787
4 1499 BAREFOOT JOSEPH PRINCETON NJ 201/812-5665
5 1101 BAUCOM WALTER NEW YORK NY 212/586-8060
6 1402 BLALOCK RALPH NEW YORK NY 718/384-2849
7 1479 BALLETTI MARIE NEW YORK NY 718/384-8816
8 1739 BRANCACCIO JOSEPH NEW YORK NY 212/587-1247
9 1658 BREUHAUS JEREMY NEW YORK NY 212/587-3622
10 1244 BUCCI ANTHONY NEW YORK NY 718/383-3334
11 1383 BURNETTE THOMAS NEW YORK NY 718/384-3569
12 1574 CAHILL MARSHALL NEW YORK NY 718/383-2338
13 1789 CARAWAY DAVIS NEW YORK NY 212/587-9000
14 1404 COHEN LEE NEW YORK NY 718/384-2946
15 1065 COPAS FREDERICO NEW YORK NY 718/384-5618
16 1876 CHIN JACK NEW YORK NY 212/588-5634
17 1129 COUNIHAN BRENDA NEW YORK NY 718/383-2313
18 1988 COOPER ANTHONY NEW YORK NY 212/587-1228
19 1405 DACKO JASON PATERSON NJ 201/732-2323
20 1983 DEAN SHARON NEW YORK NY 718/384-1647libname mydblib oracle user=testuser password=testpass;
proc sql;
create table mydblib.gtforty as
select lname as lastname,
fname as firstname,
salary as Salary
format=dollar10.2
from mydblib.staff a,
mydblib.payroll b
where (a.idnum eq b.idnum) and
(salary gt 40000);
quit;
options obs=20;
proc print data=mydblib.gtforty noobs;
title 'Employees with salaries greater than $40,000';
format salary dollar10.2;
run;
Employees with salaries greater than $40,000
LASTNAME FIRSTNAME SALARY
WELCH DARIUS $40,858.00
VENTER RANDALL $66,558.00
MARSHBURN JASPER $89,632.00
THOMPSON WAYNE $89,977.00
RHODES JEREMY $40,586.00
KIMANI ANNE $40,899.00
CASTON FRANKLIN $41,690.00
STEPHENSON ADAM $42,178.00
BANADYGA JUSTIN $88,606.00
O'NEAL BRYAN $40,079.00
RIVERS SIMON $53,798.00
MORGAN ALFRED $42,264.00