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

Proc Sql PowerPoint PPT Presentation


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

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


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:

    procsql;

    select name, city, state

    from people;

    quit;

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

    procsql;

    select name, age, gender

    from people

    order by gender, age asc;

    quit;


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:

    procsql;

    select name, occupation

    from people

    where gender in ('F');

    quit;


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:

    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;


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

    procsql;

    create table EastCoastas

    select name, occupation, state

    from people

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

    select * from EastCoast;

    quit;


New table

New Table


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


Questions

Questions?


  • Login