= SAS SQL =

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

<<TableOfContents>>

----



== SAS Dialect of SQL ==

The `SQL` procedure works in SQL query language with several enhancements



=== References ===

The `SQL` procedure has access to SAS [[SAS/References|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 [[SAS/Sort|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|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