PROC SQL

1. PROC SQL Phil Vecchione

2. 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.

3. 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”

4. Anatomy of A PROC SQL Statement proc SQL; select study, patient, age, race, gender from work.demographics where gender=‘M’ group by race; quit;

5. 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 But The SAS Data Step Already Does That…. SAS SQL So what’s so cool about proc SQL?

6. 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

7. 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

8. SQL Functions: Example 12 proc sql; 13 select count(*) as Records 14 from orcl.pat_survey1 15 quit; RECORDS -------- 19

9. 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

10. Group By: Example 21 proc sql; 22 select site_n, count(*) as Records 23 from orcl.pat_survey1 24 group by site_n; 25 quit; SITE_N RECORDS ---------------- 107 1 998 1 2310 2 2344 1

11. 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

12. Loading Macro Variables: Example 43 proc sql; 44 select mean(rhin_age) 45 into: meanage 46 from orcl.pat_survey1 47 where rhin_age is not null; • quit; • %put The mean age is: &meanage; AVG ------------ 33.35294 The mean age is: 33.35294

13. 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

14. Merging Between Two Values: Example Drug Dosing Drug Concentrations

15. Merging Between Two Values: Example Drug Dosing Drug Concentrations 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;

16. Merging Between Two Values: Example 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);

17. 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)

18. Thank You Any Questions?