proc sql
Skip this Video
Download Presentation
Proc Sql

Loading in 2 Seconds...

play fullscreen
1 / 18

Proc Sql - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Proc Sql' - kevyn-thomas

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
proc sql

Proc Sql

Kelly Hardman

Aug. 9, 2007


what is proc sql
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
Proc Sql: The Basics
  • Syntax:

Title ‘ ‘;

Proc Sql;

Select columns

From table-name

Where expression

Group By columns

Having expression

Order By columns;


example data set
Example Data Set

Data People;

length Name$ 9;

length City$ 9;

length Occupation$ 12;

input Name$ Age Gender$ City$ State$ Occupation$;


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



selecting all of the data
Selecting All of the Data
  • With traditional code, we would use proc print
  • In proc sql, we use:


select *

from People;


  • 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
selecting some of the data
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:


select name, city, state

from people;


  • Notice the commas between the column names
sorting data
Sorting Data
  • With traditional code, we would use a proc sort followed by a proc print
  • In proc sql, we use:


select name, age, gender

from people

order by gender, age asc;


analyzing a subset of data
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:


select name, occupation

from people

where gender in (\'F\');


creating a new variable
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:


select name, age,


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;


creating a new table
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


create table EastCoastas

select name, occupation, state

from people

where state in (\'NC\',\'MD\',\'FL\',\'NY\',\'GA\');

select * from EastCoast;


  • Ronk, Katie Minten, Steve First, David Beam. “An Introduction to Proc SQL” SUGI Paper 191-27
  • Vecchione, Phil “Proc SQL”