1 / 18

Proc Sql

Proc Sql. Kelly Hardman Aug. 9, 2007 BMTRY 789. What is Proc Sql?. SQL: Structured Query Language It is a procedure that combines the data and proc steps of traditional SAS code

Download Presentation

Proc Sql

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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?

More Related