30 Repeatedly Accessing Data Chapter 4
SELECT "SALARY", "DEPT" FROM EMPLOYEES
WHERE(DEPT="ACC024")
Without the KEEP option, the SQL processed by the DBMS would be similar to
SELECT * FROM EMPLOYEES
WHERE(DEPT="ACC024")
which would result in all of the columns from the EMPLOYEES table being read in
to SAS.
The DROP= data set option is a parallel option that specifies columns to omit from
the output table. Keep in mind that the DROP= and KEEP= data set options are not
interchangeable with the DROP and KEEP statements. Use of the DROP and KEEP
statements when selecting data from a DBMS can result in retrieval of all column into
SAS, which can seriously impact performance.
For example, the following would result in all of the columns from the EMPLOYEES
table being retrieved into SAS. The KEEP statement would be applied when creating
the output data set.
libname mydblib db2 user=testid password=testpass database=testdb;
data temp;
set mydblib.employees;
keep salary;
run;
The following is an example of how to use the KEEP data set option to retrieve only
the SALARY column:
data temp;
set mydblib.employees(keep=salary);
run;
Repeatedly Accessing Data
CAUTION:
If you need to access the most current DBMS data, then access it directly from the database
every time; do not follow the extraction suggestions that are presented in this section.
It is sometimes more efficient to extract (copy) DBMS data to a SAS data file than to
repeatedly read the data by using a SAS view. SAS data files are organized to provide
optimal performance with PROC and DATA steps. Programs that use SAS data files are
often more efficient than SAS programs that read DBMS data directly.
Consider extracting data when you are working with a large DBMS table and you
plan to use the same DBMS data in several procedures or DATA steps during the same
SAS session.
Note: You can extract DBMS data to a SAS data file by using the OUT= option, a
DATA step, or ACCESS procedures.
