SAS SQL

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

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