Differences between revisions 1 and 5 (spanning 4 versions)

SAS SQL

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

Contents

SAS SQL

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

-  ⇤ ← Revision 1 as of 2020-07-02 18:39:26 → 
  Size: 755
  Editor: DominicRicottone
  Comment:
+   ← Revision 5 as of 2023-03-30 20:09:08 → ⇥
  Size: 1804
  Editor: DominicRicottone
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 3:
-A major reason for SAS' staying power is the tight integration of SQL into its data model.
+A procedure allowing SQL queries to be run on a SAS table.
 Line 11:
-== Basic Usage ==
+== SAS Dialect of SQL ==
 Line 13:
-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.
+The `SQL` procedure works in SQL query language with several enhancements



=== References ===

The `SQL` procedure has access to SAS [[SAS/References|libraries]] and tables.
-Line 25:
+Line 31:
-== SAS Dialect of SQL ==
+=== Functions ===
-Line 28:
+Line 34:
+=== Formats ===
-Line 38:
+Line 48:
+----



== 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;
}}}

Diff for "SAS/SQL"