SAS SQL

A major reason for SAS' staying power is the tight integration of SQL into its data model.

Contents

  1. SAS SQL
    1. Basic Usage
    2. SAS Dialect of SQL
    3. SAS Macros

Basic Usage

SQL is called from a procedure. SQL grammar is emulated by SAS, with several enhancements. Chief among these being first-class access to SAS libraries and tables. 

proc sql;
  create table LIBREF.NEWTABLE as
    select VARLIST
      from LIBREF.OLDTABLE;
run;

SAS Dialect of SQL

SAS functions, such as year(), are available within SQL procedures.

The select command is extended with SAS formatting. 

proc sql;
  select Birthdate format=mmddyy10., year(Birthdate) as Birthyear
    from LIBREF.TABLE;
run;

SAS Macros

SAS macros can be set with SQL procedures. Macros must be prefixed with a colon (:) within the procedure.

To store single values into a macro, use the INTO clause. 

proc sql noprint;
   select VAR1, VAR2
      into :V1, :V2
      from LIBREF.TABLE;
%put V1=&V1, V2=&V2 - Note the tailing whitespace on V1, and the leading whitespace on V2;

%let V1=&V1;
%let V2=&V2;
%put V1=&V1, V2=&V2 - Note that whitespace has been trimmed;

To store aggregated values into a macro, use the INTO ... SEPARATED BY clause. 

proc sql;
   select cats(OLD_NAME, "=", NEW_NAME)
      into :RENAME separated by ' '
      from LIBREF.TABLE;

CategoryRicottone