Proc sql
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Proc Sql PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

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.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

All of the data

All of the Data

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

Some of the data

Some of the Data

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;


Sorted data

Sorted Data

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');


Subset of data

Subset of Data

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;


New variable

New Variable

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;


New table

New Table



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

  • Vecchione, Phil “Proc SQL”



  • Login