Proc Sql

Kelly HardmanAug. 9, 2007BMTRY 789

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

Proc Sql: The Basics

• Syntax:

Title ‘ ‘;Proc Sql;

Select columns From table-nameWhere expressionGroup By columnsHaving expressionOrder By columns;

Quit;

Example Data SetData People;

length Name$ 9; length City$ 9; length Occupation$ 12; input Name$ Age Gender$ City$ State$ Occupation$;datalines;Robert 55 M Annapolis MD accountantSusan 24 F Charlotte NC nurseGary 43 M Denver CO constructionElizabeth 15 F Miami FL studentTrey 33 M Seattle WA doctorHazel 72 F Madison WI retiredAmanda 41 F Cleveland OH teacherVictoria 36 F Tucson AZ secretaryDan 61 M Rochester NY firemanCynthia 52 F Savannah GA housewifeDoug 49 M Gulfport MS lawyerCharles 37 M Detroit MI banker;run;

Selecting All of the Data

• With traditional code, we would use proc print• In proc sql, we use:

proc sql; 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

All of the DataProc Sql – All of the Data

Name City Occupation Age Gender State

Robert Annapolis accountant 55 M MD

Susan Charlotte nurse 24 F NC

Gary Denver construction 43 M CO

Elizabeth Miami student 15 F FL

Trey Seattle doctor 33 M WA

Hazel Madison retired 72 F WI

Amanda Cleveland teacher 41 F OH

Victoria Tucson secretary 36 F AZ

Dan Rochester fireman 61 M NY

Cynthia Savannah housewife 52 F GA

Doug Gulfport lawyer 49 M MS

Charles Detroit banker 37 M MI

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:

proc sql; select name, city, state from people;

quit;• Notice the commas between the column

names

Some of the DataProc Sql - Selecting Certain Variables

Name City State

Robert Annapolis MD

Susan Charlotte NC

Gary Denver CO

Elizabeth Miami FL

Trey Seattle WA

Hazel Madison WI

Amanda Cleveland OH

Victoria Tucson AZ

Dan Rochester NY

Cynthia Savannah GA

Doug Gulfport MS

Charles Detroit MI

Sorting Data

• With traditional code, we would use a proc sort followed by a proc print

• In proc sql, we use:proc sql;

select name, age, genderfrom peopleorder by gender, age asc;

quit;

Sorted DataProc Sql - Sorting Data

Name Age Gender

Elizabeth 15 F

Susan 24 F

Victoria 36 F

Amanda 41 F

Cynthia 52 F

Hazel 72 F

Trey 33 M

Charles 37 M

Gary 43 M

Doug 49 M

Robert 55 M

Dan 61 M

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:

proc sql;select name, occupationfrom peoplewhere gender in ('F');

quit;

Subset of Data

Proc Sql - Subset of Data

Name Occupation

Susan nurse

Elizabeth student

Hazel retired

Amanda teacher

Victoria secretary

Cynthia housewife

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:

proc sql;select name, age,case

when age le 20 then 'young'when age le 54 then 'middle-aged'else 'old'

end as Categoryfrom peopleorder by Age asc, Category asc;

quit;

New VariableProc Sql - Creating a New, Conditional Variable

Name Age Category

Elizabeth 15 young

Susan 24 middle-aged

Trey 33 middle-aged

Victoria 36 middle-aged

Charles 37 middle-aged

Amanda 41 middle-aged

Gary 43 middle-aged

Doug 49 middle-aged

Cynthia 52 middle-aged

Robert 55 old

Dan 61 old

Hazel 72 old

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

proc sql;create table EastCoast asselect name, occupation, statefrom peoplewhere state in

('NC','MD','FL','NY','GA');

select * from EastCoast;quit;

New Table

Proc Sql - Creating a New Table

Name Occupation State

Robert accountant MD

Susan nurse NC

Elizabeth student FL

Dan fireman NY

Cynthia housewife GA

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

Questions?