Topic PROC SQL 1. Introduction 2. Generate reports using PROC SQL and the appropriate options ...

125 Pages·2008·1.62 MB·English

Topic PROC SQL

Introduction
1.
Generate reports using PROC SQL and the appropriate options
2.
Construct subqueries within a PROC SQL step
3.
Compare solving a problem using the SQL procedure versus
4.
using traditional SAS programming techniques
Access Dictionary Tables using the SQL procedure
5.
Combining Tables vertically using Set Operations in PROC SQL
6.
Joining Tables horizontally
7.
Creating and Managing Tables using PROC SQL
8.
Creating Index using PROC SQL
9.
DATA View
10.
Creating Macro
11.
Integrity Constraints
12.
SQL Procedures Pass-Through Facility
13.

1
1. Introduction
SQL, an acronym for Structured Query Language, is an ANSI (American National
Standards Institute) standard database programming/query language. SQL is a
standardized, widely used language that retrieves and updates data in tables and views
based on those tables.
The SAS System's SQL procedure enables you to
• retrieve and manipulate data that are stored in tables or views.
• create tables, views, and so forth.
• create SAS macro variables that contain values from rows in a query's result.
• add or modify the data values in a table's columns or insert and delete rows. You
can also modify the table itself by adding, modifying, or dropping columns.
• create tables and view
• join multiple tables
• generate reports
• send DBMS-specific SQL statements to a database management system (DBMS)
and to retrieve DBMS data.

1.1 What is SQL table?
A PROC SQL table is synonymous with a SAS data file and has a member type of
DATA. You can use PROC SQL tables as input into DATA steps and procedures. A row
in a table is the same as an observation in a SAS data file. A column is the same as a
variable.

1.2 Table-Comparison of SQL terms, Base SAS, and Data processing terminology:

SQL Base SAS Data Processing
_______________________________________________________________________
table SAS data file file
row observation record
column variable field
_______________________________________________________________________

1.3 Why use SQL or learn it?
• Powerful - can do many things
• Flexible - just say what you want
• Compatible - you can use the SQL you learn later in other applications
• Required - for SAS advanced programmer
2
1.4 SAS Data Step and SQL
SAS SQL
Create dataset Create tables
Update values Update values
Delete records Delete records
Append new records Union/Insert
Sort data Sort data
Merge datasets Join tables

1.5 SQL Procedure Coding Conventions
SQL statements are divided into clauses. For example, the most basic SELECT statement
contains the SELECT and FROM clauses. Items within clauses are separated with
commas in SQL, not with blanks as in the SAS System.
The SELECT statement, which is used to retrieve data, also outputs the data
automatically unless you specify the NOPRINT option in the PROC SQL statement. This
means you can display your output or send it to a list file without specifying the PRINT
procedure.
The ORDER BY clause sorts data by columns. In addition, tables do not need to be
presorted by a variable for use with PROC SQL. Therefore, you do not need to use the
SORT procedure with your PROC SQL programs
A PROC SQL statement runs when you submit it; you do not have to specify a RUN
statement. If you follow a PROC SQL statement with a RUN statement, the SAS System
ignores the RUN statement and submits the statements as usual. This procedure always

starts with a PROC SQL statement, and end with a QUIT (not run; ) statement.

1.6 How PROC SQL Is Unique
PROC SQL differs from most other SAS procedures in several ways:
• Unlike other PROC statements, many statements in PROC SQL are composed of
clauses.
• The PROC SQL step does not require a RUN statement. PROC SQL executes
each query automatically. If you use a RUN statement with a PROC SQL step,
SAS ignores the RUN statement, executes the statements as usual, and generates
the note shown below in the SAS log.
• Unlike many other SAS procedures, PROC SQL continues to run after you
submit a step. When you submit a PROC SQL step without ending it, the
status line displays the message PROC SQL running.

1.7 General Syntax
As mentioned above, this procedure always starts with a PROC SQL statement, and end

with a QUIT (not run; ) statement. The * is often used in SQL to mean “everything
available” or select all of the variables in the table. The order of the clauses is not flexible.
SQL code obeys the rules of the SAS system regarding max lengths of variable names,
reserved words et al.
PROC SQL <liste-option> ;
ALTER TABLE instruction-alter ;
CREATE instruction-create ;
DELETE instruction-delete ;
3
DROP instruction-drop/view, table, index from ;
INSERT instruction-insert ;
RESET <list-option> ;
SELECT instruction-select ;<options>
Distinct object-item
INTO macro-variable-specification
FROM from-list
WHERE sql-expression
GROUP BY group-by-item
HAVING sql-expression
ORDER BY

Unlike other SAS procedures, the order of clauses within a SELECT statement in
PROC SQL is important. Clauses must appear in the order shown above.

To do this Use this statement
_________________________________________________________
Modify, add, or drop columns ALTER TABLE
Establish a connection with a DBMS CONNECT
Create an index on a column CREATE INDEX
Create a PROC SQL table CREATE TABLE
Create a PROC SQL view CREATE VIEW
Delete rows DELETE
Display a definition of a table or view DESCRIBE
Terminate the connection with a DBMS DISCONNECT
Delete tables, views, or indexes DROP
Send a DBMS-specific nonquery
SQL statement to a DBMS EXECUTE
Add rows INSERT
Select and execute rows SELECT
Query a DBMS CONNECTION TO
Modify values UPDATE
_____________________________________________________________

2. Generate reports using PROC SQL and the appropriate options
Besides using different statement syntax, an obvious distinction between PROC PRINT
and PROC SQL is that a QUIT statement is specified for the latter, rather than a RUN
statement, to terminate processing.
2.1 The PROC SQL statement illustrated below produces a detail-oriented report (similar
to PROC PRINT) consisting of all observations and variables.
data people;
input personid date age race $ gender $ @@;
format date mmddyy10.;
informat date mmddyy8.;
cards;
001 09/18/87 23 Caucas M 002 01/23/90 55 Asian M
003 06/05/93 16 Hispanic F 004 11/14/93 21 Afr-Am M
005 03/27/95 33 Afr-Am F 006 08/28/96 45 Caucas F
007 02/01/98 27 Caucas M 008 04/20/88 19 Caucas M
009 07/12/92 24 Hispanic F 010 12/06/94 30 Asian F
;
proc sql double;/*nodouble, feedback, noprint, INOBS=n, OUTOBS=n,
number*/;
title 'This is the entire subjects';
select * from people;
4
quit;
The SELECT statement is the simplest and most commonly used in SQL.
INOBS=n restricts the number of rows (observations) that PROC SQL retrieves from
any single source.
OUTOBS=n restricts the number of rows (observations) in the output. It is similar to
OBS.
FEEDBACK option in the PROC SQL statement writes the expanded list of columns to
the SAS log. It is a debugging tool that lets you see exactly what is being submitted to the
SQL processor.
NOEXEC option does not execute this procedure.

/* to select unique values of one or more columns the distinct keyword
is used*/;
proc sql;
create table patid as
select distinct patient
from alec.vitals;
quit;

proc sql noexec;
select age, race
from people;
quit;

The SELECT Statement
The SELECT statement, which follows the PROC SQL statement, retrieves and displays
data. It is composed of clauses, each of which begins with a keyword and is followed by
one or more components. The SELECT statement in the following sample code contains
four clauses: the required clauses SELECT and FROM, and the optional clauses WHERE
and ORDER BY. The end of the statement is indicated by a semicolon.

proc sql;
|-select empid,jobcode,salary,
| salary*.06 as bonus
|----from sasuser.payrollmaster
|----where salary<32000
|----order by jobcode;
quit;
A PROC SQL query produces a result set that can be output as a report, a table, or a
PROC SQL view.
Type of Output PROC SQL Statements
report SELECT
table CREATE TABLE and
SELECT

Select columns and create new columns
data sumy;
input IdNumber Jobcode $ Salary@@;
5
cards;
1845 BCK 25996 1673 CK1 25477 1834 BCK 26896 1387 NF2 25028
1100 BCK 25004 1677 BCK 26007 1663 PA3 26452 1353 NF1 25823
1704 NF1 25465 1981 FA1 22413 1634 BCK 26896 1389 NF2 25028
1100 GK1 25004 1977 BCK 26012 1363 PA3 26488 1083 PT1 23823
1965 PT1 27465 1154 FA1 22613
;
proc sql ;
create table sy as
select IdNumber, Jobcode, Salary,
avg(salary) as AvgSalary, count(idnumber) as N,
sum (salary) as total_sum format=dollar10.2, freq(jobcode)
as count from sumy (obs=10)
group by jobcode;
quit;

The keyword FROM specifies the table.
WHERE clause can be subset data based on a condition in the SELECT statement.
To create a new table from the results of a query, use a CREATE TABLE statement
that includes the keyword AS and the clauses that are used in a PROC SQL query. The
CREATE TABLE statement stores your query results in a table instead of displaying the
results as a report.

2.2 The PROC SQL statement produces summary report
/* To create a table with adding a new variables*/;
see above example.

/*IN condition*/;
sql-expression <NOT> IN (constant <,constant>…)
sql-expression <NOT> IN (query-expresssion)

proc sql;
title 'Information for Certain Employees Only';
select IdNumber, Salary, Jobcode
from sumy
where idnumber not in (1363, 1387, 1704);
quit;
*where idnumber not in (select salary from sumy where salary gt 26000);

/*IS condition*/;
IS NULL and IS MISSING are predicates that test for a missing value. IS NULL and IS
MISSING are used in the WHERE, ON, and HAVING expressions.
*where var is missing;

2.3 The PROC SQL –Order, Group, Having et al
/* to sort values of one or more columns, order by is used*/;
proc sql;
create table visits as
select distinct patient, date format=date7.
from alec.vitals
order by patient, date descending (desc);
quit;

6
Order By clause specifies a sort order. By default, the ORDER BY clause sorts rows in
ascending order. To sort rows in descending order, specify the keyword DESC
following the column name. For example, the preceding ORDER BY clause could be
modified as follows:
In the ORDER BY clause, you can alternatively reference a column by its position in the
SELECT clause list rather than by name. Use an integer to indicate the column's position.
The ORDER BY clause in the preceding PROC SQL query has been modified, below, to
specify the column JobCode by its position in the SELECT clause list (2) rather than by
name.
proc sql;
select empid,jobcode,salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary<32000
order by 2;
quit;
You can mix the two types of column references, names and numbers, in the ORDER BY
clause. For example, the preceding ORDER BY clause could be rewritten as follows:
order by 2,empid;
In some situations, you might want to display only the unique values in the SELECT
clause. You can eliminate duplicate rows from your query results by using the keyword
DISTINCT in the SELECT clause.
/*grouping is specific using group by and calculated usage */;
proc sql;
create table highbpp1 as
select patient, count(patient) as N, date format=date7.,
bpd, max(bpd) as bpdhigh,
round(bpd/(calculated bpdhigh)*100, 0.01) format=6.2
as bpdpct
from alec.vitals
group by patient;
quit;

CALCULATED
The CALCULATED keyword informs PROC SQL that the value is calculated within the
query and enables you to use the results of an expression in the same SELECT clause or
in the WHERE clause. It is valid only when used to refer to columns that are calculated in
the immediate query expression.

/*having condition is true, similar to where statement*/;
proc sql;
create table highbpp1 as
select patient, count(patient) as N, date format=date7.,
bpd, max(bpd) as bpdhigh
from alec.vitals
group by patient
having bpd =calculated bpdhigh
order by calculated bpdhigh; quit;

7
Using the VALIDATE Keyword
You specify the VALIDATE keyword just before a SELECT statement; it is not used
with any other PROC SQL statement.
proc sql;
validate
select empid, jobcode, salary
from sasuser.payrollmaster
where jobcode contains 'NA'
order by salary;
quit;

Note that the VALIDATE keyword is not followed by a semicolon.
Summary Functions
To summarize data, you can use the following summary functions with PROC SQL.
Notice that some functions have more than one name to accommodate both SAS and
SQL conventions. Where multiple names are listed, the first name is the SQL name.

AVG, MEAN mean or average of values
COUNT, FREQ, N number of nonmissing values
CSS corrected sum of squares
CV coefficient of variation (percent)
MAX largest value
MIN smallest value
NMISS number of missing values
PRT probability of a greater absolute value of Student's t
RANGE range of values
STD standard deviation
STDERR standard error of the mean
SUM sum of values
T student's t value for testing the hypothesis that the population mean
is zero
USS uncorrected sum of squares
VAR variance

ANY values that meet a specified where dateofbirth < any
condition with respect to any one of (select dateofbirth
from
the values returned by a subquery
sasuser.payrollmaster
where
jobcode='FA3')
ALL values that meet a specified where dateofbirth < all
condition with respect to all the (select dateofbirth
from
values returned by a subquery
sasuser.payrollmaster
where
jobcode='FA3')

8
3. Construct Subqueries Within a PROC SQL Step
One of powerful features in SQL procedure is subquery, which provides great flexibility
in manipulating and querying data in multiple tables simultaneously. However, subquery
is the subtlest part of the SQL procedure. Users have to understand the correct way to use
subqueries in a variety of situations. We will explore how subqueries work properly with
different components of SQL such as WHERE, HAVING, FROM, SELECT clause using
the following examples:

3.1 SUBQUERY IN THE WHERE CLAUSE
/*Example 1: Find patients who have records in table DEMO but not in
table VITAL.*/;
Proc SQL;
Create table chek as
Select patient from sl.demo
Where patient not in (select patient from sl.vitals);
Quit;

/*Example 2: Find patients who didn’t have their blood pressures
measured twice at a visit.*/;
Proc SQL;
Select patient from sl.vitals T1
where 2>(select count(*)from sl.vitals T2
where T1.patient=T2.patient and T1.date=T2.date);
Quit;

/*Example 3: Find patients whose age is in the average age +/- 10
years.*/;
Proc SQL;
create table agetab as
Select patient
from sl.demo2
where age between (select Avg(age) from sl.demo2)-10
and (select avg(age) from sl.demo2)+10;
Quit;

3.2 SUBQUERY IN THE HAVING CLAUSE
/*Example: Find patients who didn’t have maximum number of visits.*/;
proc freq data=sl.vitals noprint ;
tables date/out=vist;
by patient;
run;
Proc SQL;
create table cnt as
Select patient
from vist
Group by patient
Having count(count) < (select max(cnt) from (select count(count)
as cnt from vist group by patient));
Quit;

3.3 SUBQUERY IN THE SELECT CLAUSE
9
/*Example: Compute the difference between recorded mean of SYSBP from
VITMEAN table and calculated mean of SYSBP from VITAL for each patient
and visit.*/
Proc SQL;
Select a.patient, a.date,
a.bps -(select avg(bps) from sl.vital as b
where a.patient=b.patient and a.date=b.date)
as diff from vitmean as a;
Quit;

4. Compare Solving a Problem using the SQL Procedure versus using Traditional
SAS Programming Techniques
Example 1
With our example database in mind, how would one create a table with all the men in the
database?
SQL approach
proc sql;
create table men as
select * from cblood.persons ( where =(sex = 1));
quit;
SAS approach
data men;
set cblood.persons;
where sex=1;
run;

What if we wanted a table with all men and their birthdates; sorted ascending by birthdate?
Example 2
SQL approach
proc sql;
create table men as
select idnr, birthdate from cblood.persons( where =(sex = 1))
order by birthdate;
quit;

SAS approach
data men;
set cblood.persons;
where sex=1;
keep idnr birthdate;
run;
proc sort data=men;
by birthdate;
run;

Example 3
• Well then, that was simple (?), what if we wanted a list of all the people that ever received
blood?

SQL approach
proc sql;
create table patient as
select distinct idnr
from cblood.transfusion;
quit;

SAS approach
data patient;
set cblood.transfusion;
keep idnr;
10