Previous Page | Next Page

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);

proc sql;
   insert into one(pw=red, col1, col3)
   values(1, 3);
proc sql;
   update one(pw=red)
      set col2 = 22
         where col2 = . ;

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.

Previous Page | Next Page | Top of Page