In PROC SQL, you can apply
most of the SAS data set options, such as KEEP= and DROP=, to tables
or
SAS/ACCESS views any
time you specify a table or
SAS/ACCESS view. In the SQL procedure, SAS data set options
that are separated by spaces are enclosed in parentheses. The data
set options immediately follow the table or
SAS/ACCESS view name. In the following PROC SQL step, the RENAME=
data set option renames LNAME to LASTNAME for the STAFF1 table. The
OBS= data set option restricts the number of rows that are read from
STAFF1 to 15:
proc sql;
create table
staff1(rename=(lname=lastname)) as
select *
from staff(obs=15);
SAS data set options
can be combined with SQL statement arguments. In the following PROC
SQL step, the PW= data set option assigns a password to the TEST table,
and the ALTER= data set option assigns an ALTER password to the STAFF1
table:
proc sql;
create table test
(a character, b numeric, pw=cat);
create index staffidx on
staff1 (lastname, alter=dog);
In this PROC SQL step,
the PW= data set option assigns a password to the ONE table. The password
is used when inserting a row and updating the table.
proc sql;
create table one(pw=red, col1 num, col2 num, col3 num);
quit;
proc sql;
insert into one(pw=red, col1, col3)
values(1, 3);
quit;
proc sql;
update one(pw=red)
set col2 = 22
where col2 = . ;
quit;
You cannot use SAS data
set options with DICTIONARY tables because DICTIONARY tables are read-only
objects.
The only SAS data set
options that you can use with PROC SQL views are data set options
that assign and provide SAS passwords: READ=, WRITE=, ALTER=, and
PW=.
For more information
about SAS data set options, see
SAS Data Set Options: Reference.