Proc Sql

1. Proc Sql Kelly Hardman Aug. 9, 2007 BMTRY 789

2. What is Proc Sql? • SQL: Structured Query Language • It is a procedure that combines the data and proc steps of traditional SAS code • It can “sort, summarize, join, and concatenate datasets, create new variables, and print the results or create a new table to view all in one step!” • It can perform these functions quicker and with fewer steps than traditional SAS code

3. Proc Sql: The Basics • Syntax: Title ‘ ‘; Proc Sql; Select columns From table-name Where expression Group By columns Having expression Order By columns; Quit;

4. Example Data Set Data People; length Name$ 9; length City$ 9; length Occupation$ 12; input Name$ Age Gender$ City$ State$ Occupation$; datalines; Robert 55 M Annapolis MD accountant Susan 24 F Charlotte NC nurse Gary 43 M Denver CO construction Elizabeth 15 F Miami FL student Trey 33 M Seattle WA doctor Hazel 72 F Madison WI retired Amanda 41 F Cleveland OH teacher Victoria 36 F Tucson AZ secretary Dan 61 M Rochester NY fireman Cynthia 52 F Savannah GA housewife Doug 49 M Gulfport MS lawyer Charles 37 M Detroit MI banker ; run;

5. Selecting All of the Data • With traditional code, we would use proc print • In proc sql, we use: procsql; select * from People; quit; • Notice the * It is used when selecting all of the columns • People is the data set name. If we were using a permanent data set, we would use libname.People

6. All of the Data

7. Selecting Some of the Data • To select only certain columns or variables, just write the column names after the select statements • Here we just want names, cities, and states: procsql; select name, city, state from people; quit; • Notice the commas between the column names

8. Some of the Data

9. Sorting Data • With traditional code, we would use a proc sort followed by a proc print • In proc sql, we use: procsql; select name, age, gender from people order by gender, age asc; quit;

10. Sorted Data

11. Analyzing a Subset of Data • The where statement is used in proc sql the same way it is used in traditional SAS code • Here, we only want the names and occupations of the females: procsql; select name, occupation from people where gender in ('F'); quit;

12. Subset of Data

13. Creating a New Variable • Traditionally, to create a new variable, we would have to use a series of if-then statements in the data step • With proc sql: procsql; select name, age, case when age le 20 then 'young' when age le 54 then 'middle-aged' else 'old' end as Category from people order by Age asc, Category asc; quit;

14. New Variable

15. Creating a New Table • Equivalent of creating a new data set from the old data set • Here, we create the table EastCoast from the table People procsql; create table EastCoastas select name, occupation, state from people where state in ('NC','MD','FL','NY','GA'); select * from EastCoast; quit;

16. New Table

17. References • Ronk, Katie Minten, Steve First, David Beam. “An Introduction to Proc SQL” SUGI Paper 191-27 http://www2.sas.com/proceedings/sugi27/p191-27.pdf • Vecchione, Phil “Proc SQL” http://www.google.com/search?q=cache:wE8FcgdygFIJ:www.cognigencorp.com/perspective/tipsNtricks.pub/1/PROC%2520SQL%2520Talk_12_.ppt+proc+sql&hl=en&ct=clnk&cd=8&gl=us&client=firefox-a

18. Questions?