SAS SQL

A procedure allowing SQL queries to be run on a SAS table.

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