Renu Gehring - The Power of PROC SQL

Tutorials in SAS Programming: The Power of PROC SQL By Renu Gehring
Copyright RenuGehring 2014 SAS and all other SAS Institute Inc. product or servicenames are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. indicates USA registration.
For my husband andchildren
Contents

SQL stands for Structured Query Language, a way to querydatabases. There are many flavors of SQL in the industry.

For example, Oracleand Microsoft have their own separate versions. PROC SQL is the procedurebased on SASs implementation of SQL. This tutorial is useful to new as well as experiencedusers of SAS. If you are a seasoned data step programmer, you will appreciatethe compact and elegant way of querying and summarizing data via PROC SQL. Ifyou are new to SAS, PROC SQL is an easy way to query, manage, and report datawithout learning extensive SAS specific syntax. This tutorial demonstrates the power of PROC SQL thoughexamples.

It uses mock hospital discharge and payer tables, which can bedownloaded at the authors website www.ace-cube.com. For an optimal learning experience, use the sample data to recreate theexamples developed in the tutorial.

Two tables are used in this tutorial. Figure 2.1 showsyou a few records from the hospital discharge table. Figure 2.1: Hospital Discharge Table The Discharge table contains identifying columns such as ID,HospID, Dates of Birth, Admission, and Discharge as well as pertinentinformation about the discharge itself. Columns in SAS are either character or numeric.

Dates inSAS are stored as numeric columns, specifically as the number of days fromJanuary 1, 1960. Any date prior to January 1, 1960 is a negative numberwhereas a date after January 1, 1960 is stored as a positive number. Thereason that Dates of Admit and Discharge appear as regular calendar dates isthat they have a SAS format applied to them. SAS formats do not change the waythe variable is stored, only the way it is displayed. Date of Birth does nothave a format applied to it, so it appears as a numeric, confounding ourconcept of what a date should be like. Drg is Diagnosis Related Group.

To those of youunfamiliar with medical data, think of these as disease categories. AdmitCodeis Source of Admission. This numeric variable relates to how the patient wasadmitted. 1=Through the ED 3=Physician Referral. DischgCode is Mode ofDischarge. 1= Regular Discharge 2=Discharge to another facility. 1= Regular Discharge 2=Discharge to another facility.

Figure 2.2 shows you some select records from the Payer table,which contains the insurance information about each patient in Dischargetable. Figure 2.2: Payer Table A patients insurance information has been collapsed into anumeric column, instype. 1=Private Insurance 2=Medicare 3=Medicaid 0=AllOthers. As a patient may have more than one discharge record in theDischarge table and have more than one insurance type in the Payer table, thejoin relationship between the two tables is many to many. For example, patientID 1295424 has two discharges and two insurance types. When the two tables arejoined, you will expect to see four records for patient ID 1295424.

The two tables are stored in a SAS library called class,whose libname statement is shown below. A libname statement connects the libref(in this case class) to the physical location of the tables. libname class 'C:\Tutorials in SAS\Data' ;

Lets dive right into SQL programming. Examine the codebelow. Example 3.1: Querying Data with SQL procsql outobs = ; select DateOfDischarge, DischgCode, AdmitCode, Drg from class.discharge where AdmitCode= and DateOfBirth> '01Feb1970'd ; quit ; The keywords PROC SQL invoke the procedure. At this time,you can specify additional options.

In Example 3.1, the OUTOBS option is setto ten, which means that only ten observations are displayed. The invocationis followed by a semicolon, as is customary for SAS statements. Example 3.1 contains a SELECT clause, which is comprised ofSELECT, FROM, and WHERE statements. The SELECT clause has a semicolon at theend of the WHERE statement and NOT at the end of each of the three statementsit contains. The SELECT statement lists the columns being queried. The FROMstatement lists the source table.

The WHERE statement contains filters on thesource table. Why is the date value 01Feb1970 enclosed in quotes and thenfollowed by d? Dates are stored as number of days since Jan 1, 1960 in SAS. Adate inputted by the user will be translated into its numeric equivalent only ifit is specified a certain way. That way is a ddMONYYYY value enclosed inquotes and followed by the letter d. The SQL procedure is ended with the QUIT statement. The logfrom Example 3.1 is shown below.

The WARNING tells you that ten observationswere outputted due to the OUTOBS option. 3 proc sql outobs=10; 4 selectDateOfDischarge, DischgCode, AdmitCode, Drg 5 fromclass.discharge 6 where AdmitCode=1and DateOfBirth>'01Feb1970'd; NOTE: Writing HTML Bodyfile: sashtml.htm WARNING: Statementterminated early due to OUTOBS=10 option. 7 quit; NOTE: PROCEDURE SQL used(Total process time): real time 1.52 seconds cpu time 0.32 seconds Figure 3.1: Results of Example 3.1 Example 3.2 shows you a quick way of selecting allcolumns. This is done by typing SELECT *. Example 3.2: Selecting AllColumns procsql outobs = ; select * from class.discharge; quit ; Example 3.3 creates a table called Discharge2 which is populatedby query results. Example 3.3: Creating a Table procsql ; create table Discharge2 as select DateOfDischarge, DischgCode, AdmitCode, Drg from class.discharge where AdmitCode= and DateOfBirth> '01Feb1970'd ; quit ; Example 3.4 creates new columns in the SELECT statement.

Length of Stay (Los) is calculated as one plus the difference between the datesof discharge and admission. Counter is set to 1 for each row. Example 3.4: Create New Columns procsql ; create table Discharge2 as select *, (DateOfDischarge-DateOfAdmit)+ as los, as counter from class.discharge; quit ; Example 3.5 shows that a new computed column may bereferenced in the WHERE statement as long as the keyword CALCULATED precedesit. This is an important shortcut. The WHERE statement is executed first sothe keyword CALCULATED alerts it to the computed item in the SELECT statement. *Example 6; *Unique combination of HospID and DischgCode; procsql ; select distinct hospid, DischgCode from class.discharge; quit ; If you only need to see a uniquecombination of your columns and do not require any further statistics, then youwill find that the code in Example 3.6 is less resource intense than the PROCFREQ alternative shown below. *PROC FREQ yields a unique combination of HospID and DischgCode; *More resource intense than PROC SQL; procfreq data =class.discharge; tables hospid*DischgCode; run ;

In the previous section, you learnt how to retrieve rowsfrom a table. *PROC FREQ yields a unique combination of HospID and DischgCode; *More resource intense than PROC SQL; procfreq data =class.discharge; tables hospid*DischgCode; run ;
Next page