|Programming with the SQL Procedure|
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 that 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:
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 Language Reference: Dictionary.