SAS SQL

The SQL procedure in SAS allows SQL queries to be run on SAS data.

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

SAS Dialect of SQL

The SQL procedure works in SQL query language with several enhancements

References

The SQL procedure has access to SAS libraries and tables.

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

Functions

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

Formats

The select command is extended with SAS formatting.

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

Deduplication

The primary method of de-duplicating data in SAS is the SORT procedure. An alternative approach is:

proc sql;
  create table LIBREF.NEWTABLE as
    select DISTINCT(KEYVAR), VAR2, VAR3
      from LIBREF.OLDTABLE
      group by KEYVAR
run;

Defining SAS Macros

SAS macros can be set in a SQL procedure. Macro labels 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