Example of Popular
QUICK RESULTS
WITH
PROC SQL E-Guide
(Purchase the full version now at SASSavvy.com)
The single most concise productivity e-Guide available!

Sunil@SASSavvy.com
Download all PROC SQL Examples
E-mail Sunil Your PROC SQL Questions

Basic Usage of Proc SQL
Common examples for: content - Creating Tables or Views
- Changing Table Structure
- Updating, Adding, or Deleting Data

I. Table Access and Retrieval
*Column Definition, *Join Tables,
*Subset Table, *Sort Table

The SAS e-Guide assumes you have general knowledge of
SAS Programming.
Common features are illustrated through taskbased examples. Web browser may need to be open for links.

II. Creating Macro Variables

III. Table Structure Operations
IV. Table Content Operations
V. Connecting to Relational
Databases

Copyright  2010 by Sunil Gupta, Simi Valley, CA USA

-1-

www.SASSavvy.com

PROC SQL Basic Usage:
1. proc sql;
2.
< proc sql statement 1 > ;
3.
< proc sql statement 2 > ;
4. quit;

/* required */

/* required */

Lines 2, 3: all of the examples in the e-Guide are expected to be embedded between lines 1 and
4. SAS accepts one or more PROC SQL statements within each PROC SQL block of code.

Eight common benefits for using PROC SQL:
1. proc sql;
2.
create table mytable as select ...;
3.
create view myview as select ...;
4.
select ...;
5.
alter table mytable ...;
6.
update table mytable ...;
7.
insert table mytable ...;
8
delete from table mytable ...;
9.
drop table mytable;
10. quit;

/* required */
/* new table */
/* new view */
/* powerful query tool */
/* new table structure */
/* update table content */
/* update table content */
/* delete table content */
/* delete table */
/* required */

Line 1: six options exist to help debug – NOEXEC (to check syntax without executing the code),
FEEDBACK (displays code executed), INOBS=# of rows read, OUTOBS=# of rows written,
_METHOD (displays PROC SQL execution options), _TREE (display visual structure of logic). See
SAS On-Line reference for complete set of features and options. See SAS paper on undocumented features. Remember to set OPTIONS MSGLEVEL=I for _METHOD and _TREE options since default is MSGLEVEL=N. After the syntax is error-free, you may need to investigate your data and logic. Note that once debug options are applied, the RESET NOFEEDBACK is required, for example, to reset to default. NUMBER option displays the row number which can be also saved as a new column with the MONOTONIC() keyword. See SAS paper on the
MONOTONIC() keyword. Can add NOWARN option to prevent displaying warning messages.
Finally, the FLOW option is useful to wrap text within each cell.
Line 2: create mytable table with SELECT statement. If a table already exists, then LIKE, instead of AS, <table> option can be used to copy the table structure with zero rows.
Line 3: create myview view with SELECT statement. Views are similar to tables except that views do not require space and can not be indexed. Once created, it is useful to DESCRIBE view to display the table structure, similar to PROC CONTENTS, to the SAS log.
Line 4: query table with SELECT statement. Add VALIDATE before SELECT to check syntax without executing the code. Note that while generally PROC SQL is used for data management tasks, the results of SELECT statements may be directed to Excel, RTF, PDF or HTML files through
ODS. See SAS site for overview of SELECT statement. Also note that PROC SQL stores the # of rows returned in the &SQLOBS automatic macro variable which can be used for further macro processing. See automatic macro variable section. See SAS site for more info on automatic macro variables.
Line 5: change MYTABLE table structure by copying another table, creating columns with attributes, dropping columns, modifying column attributes or adding integrity constraints. See III
Table Structure Operations.
Line 6: update MYTABLE table content by multiplying by number or string modification. Changes can be conditionally applied with the WHERE clause. See