Programming with the SQL Procedure

Using SAS Data Set Options with PROC SQL

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:

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 Language Reference: Dictionary.

Top of Page