18 Using a SAS Data Set to Create a DBMS Table Chapter 2
select lname as LAST_NAME,
fname as FIRST_NAME,
salary as ANNUAL_SALARY
from mydblib.staff a,
mydblib.payroll b
where (a.idnum eq b.idnum) and
(salary gt 40000)
order by lname;
proc print noobs;
title ’Employees with Salaries over $40,000’;
run;
Output 2.7 Updating DBMS Data
Employees with Salaries over $40,000
ANNUAL_
LAST_NAME FIRST_NAME SALARY
BANADYGA JUSTIN 88606
BAREFOOT JOSEPH 43025
BRADY CHRISTINE 68767
BRANCACCIO JOSEPH 66517
CARTER-COHEN KAREN 40260
CASTON FRANKLIN 41690
COHEN LEE 91376
FERNANDEZ KATRINA 51081
Using a SAS Data Set to Create a DBMS Table
In the following example, you use a SAS data step to create a DBMS table,
College-Hires-1999, from a temporary SAS data set that has case-sensitive names. You
create the temporary data set and then define your LIBNAME statement. Because you
are using a DATA step to create the DBMS table, you must specify the table name as a
name literal and specify the PRESERVE_TAB_NAMES= and
PRESERVE_COL_NAMES= options (in this case, by using the alias
PRESERVE_NAMES=) .
options validvarname=any nodate;
data College_Hires_1999;
input IDnum $4. +3 Lastname $11. +2
Firstname $10. +2 City $15. +2
State $2.;
datalines;
3413 Schwartz Robert New Canaan CT
3523 Janssen Heike Stamford CT
3565 Gomez Luis Darien CT
;
libname mydblib oracle user=testuser password=testpass
path=’hrdata99’ schema=hrdept preserve_names=yes;
data mydblib.’College-Hires-1999’n;
