proc sql
Download
Skip this Video
Download Presentation
Proc Sql

Loading in 2 Seconds...

play fullscreen
1 / 18

Proc Sql - PowerPoint PPT Presentation


  • 149 Views
  • 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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

BMTRY 789

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;

Quit;

example data set
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;

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

procsql;

select name, city, state

from people;

quit;

  • 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:

procsql;

select name, age, gender

from people

order by gender, age asc;

quit;

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:

procsql;

select name, occupation

from people

where gender in (\'F\');

quit;

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:

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;

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

procsql;

create table EastCoastas

select name, occupation, state

from people

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

select * from EastCoast;

quit;

references
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
ad