proc sql phil vecchione. sql structured query language developed by ibm in the early 1970’s from...

PROC SQL

Phil Vecchione

SQL• Structured Query Language• Developed by IBM in the early 1970’s• From the 70’s to the late 80’s there

were different types of SQL, based on different databases.

• In 1986 the first unified SQL standard (SQL-86) was created.

• Today the SQL parser that is used by most databases are bases on SQL-92 standards.

Proc SQL• Added to the Base SAS package in

version 6

• Implemented to allow people familiar with database to use SQL features within SAS

• A “language within a language”

Anatomy of A PROC SQL Statement

proc SQL;

select study, patient, age, race, gender

from work.demographics

where gender=‘M’

group by race;

quit;

But The SAS Data Step Already Does That….

• Create dataset• Update values• Delete Records• Append new records• Create New

variables• Sort data• Merge datasets

• Create tables• Update values • Delete Records• Insert New Records• Create New Variables• Sort Data• Join tables

SAS SQL

So what’s so cool about proc SQL?

The Power Of SQL• SQL looks at datasets differently from SAS

– SAS looks at a dataset one record at a time, using an implied loop that moves from the first record to the last

– SQL looks at all the records, as a single object

• Because of this difference SQL can easily do a few things that are more difficult to do in SAS

Power of SQL: SQL Functions• There are a number of built in functions in

SQL that can be used in a select statement • Because of how SQL handles a dataset,

these functions work over the entire dataset• Functions:

– Count: Counts Values– Sum: Sums Values – Max: Identifies the largest value– Min: Identifies the smallest value– Mean: Averages the values

SQL Functions: Example

12 proc sql;13 select count(*) as Records14 from orcl.pat_survey115 quit;

RECORDS-------- 19

Power of SQL: Group By• Similar to the BY parameter

used in SAS

• Groups the SQL observations by the variable defined

• When used with the SQL functions allows summary information on groupings rather then the entire dataset

Group By: Example21 proc sql;22 select site_n, count(*) as Records23 from orcl.pat_survey124 group by site_n;25 quit;

SITE_N RECORDS ---------------- 107 1 998 1 2310 2 2344 1

Loading Macro Variables• A great feature of Proc SQL is

that you can load a value or values from a SQL statement into a macro variable

• Can put a specific value into a macro variable for use throughout your program

• Coupled with the SQL functions, you can load calculated values into a macro variable

Loading Macro Variables: Example

43 proc sql;44 select mean(rhin_age)45 into: meanage46 from orcl.pat_survey147 where rhin_age is not null;48 quit;50 %put The mean age is: &meanage;

AVG------------ 33.35294

The mean age is: 33.35294

Power of SQL: Merging Between Two Values

• A merge using a SAS data step requires that the variable described in the BY parameter have an EXACT match

• SQL joins can contain NON-EXACT parameters for a join

• Thus, allowing for joins to occur between values

Merging Between Two Values: Example

Patient Visit Date Conc

1 4/14/2003 12

2 4/10/2003 3

3 4/4/2003 99

Patient Start Drug End Drug

1 4/13/2003 4/15/2003

1 4/19/2003 4/21/2003

2 3/22/2003 3/25/2003

2 4/9/2003 4/11/2003

3 3/1/2003 3/3/2003

3 5/9/2003 5/11/2003

Drug Concentrations Drug Dosing

Merging Between Two Values: Example

Patient Visit Date Conc

1 4/14/2003 12

2 4/10/2003 3

3 4/4/2003 99

Patient Start Date End Date

1 4/13/2003 4/15/2003

1 4/19/2003 4/21/2003

2 3/22/2003 3/25/2003

2 4/9/2003 4/11/2003

3 3/1/2003 3/3/2003

3 5/9/2003 5/11/2003

Drug Dosing

proc sql; select c.patient, c.visit_date, c.conc,d.start_date, d.end_date from drug_conc c, drug_dosing d

where c.patient=d.patient;quit;

Drug Concentrations

Merging Between Two Values: Example

Patient Visit Date Conc

1 4/14/2003 12

2 4/10/2003 3

3 4/4/2003 99

Patient Start Date End Date

1 4/13/2003 4/15/2003

1 4/19/2003 4/21/2003

2 3/22/2003 3/25/2003

2 4/9/2003 4/11/2003

3 3/1/2003 3/3/2003

3 5/9/2003 5/11/2003

Drug Concentrations Drug Dosing

select c.patient, c.visit_date, c.conc,d.start_date, d.end_date from drug_conc c, drug_dosing d where c.patient=d.patient and (d.start_date le c.visit_date le d.end_date);

References

• Books– SAS Guide to the SQL Procedure– SQL for Dummies

• Papers– SQL for People Who Don’t Think They

Need SQL: Erin Christen (PharmaSUG 2003)

Thank You

Any Questions?