246 Using the SQL Procedure with DBMS Data Chapter 13
Output 13.4 Creating a SAS Data Set with DBMS Data by Using the UPDATE Statement
Updated Payroll Data 1
Obs IDNUM SEX JOBCODE SALARY BIRTH HIRED
1 1009 M TA1 28880 02MAR1959 26MAR1992
2 1017 M TA3 40858 28DEC1957 16OCT1981
3 1036 F TA3 42465 19MAY1965 23OCT1984
4 1037 F TA1 28558 10APR1964 13SEP1992
5 1038 F TA1 26533 09NOV1969 23NOV1991
6 1050 M ME2 35167 14JUL1963 24AUG1986
7 1065 M ME3 38090 26JAN1944 07JAN1987
8 1076 M PT1 69742 14OCT1955 03OCT1991
9 1094 M FA1 22268 02APR1970 17APR1991
10 1100 M BCK 25004 01DEC1960 07MAY1988
11 1101 M SCP 18723 06JUN1962 01OCT1990
12 1102 M TA2 34542 01OCT1959 15APR1991
13 1103 F FA1 23738 16FEB1968 23JUL1992
14 1104 M SCP 17946 25APR1963 10JUN1991
15 1105 M ME2 34805 01MAR1962 13AUG1990
16 1106 M PT3 94039 06NOV1957 16AUG1984
17 1107 M PT2 89977 09JUN1954 10FEB1979
18 1111 M NA1 40586 14JUL1973 31OCT1992
19 1112 M TA1 26905 29NOV1964 07DEC1992
20 1113 F FA1 22367 15JAN1968 17OCT1991
Using the SQL Procedure with DBMS Data
Rather than performing operations on your data in SAS, you can perform operations
on data directly in your DBMS by using the SQL procedure. The following examples
use the SQL procedure to query, update, and create DBMS tables.
Querying a DBMS Table
This example uses the SQL procedure to query the Oracle table Payroll. The PROC
SQL query retrieves all job codes and provides a total salary amount for each job code.
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;
Output for this example is shown here.
