Proc SQL, The Data Step Killer

Proc SQL, the Data Step KillerMike AtkinsonAcko Systems Consulting IncData StepProc SQL

The Data Step It’s a complete programming language Easy to combine (or merge) datasets It allows you to perform sequential algorithm steps Some algorithms require data to be in sorted order first Can use values of variables from different observations, using retain It has the “where” statement (stolen from SQL) that allows efficient use of filtering criteria prior to other processingThere are some things the data step can do that can’t be done inProc SQL, e.g. Can create multiple datasets in one step Can easily join multiple datasets – each left, right, or full outer join in aProc SQL query can join only two datasets at a time (although innerjoins without the join keyword can bring together any number)

Proc SQL SQL is the de facto standard query language, widely used (beyond SAS even!) for retrieving andsummarizing dataProc SQL can summarize results in the same step asperforming row level calculations without Proc SQL, summarizing requires a separate procsummary step, and often a pre-sortProc SQL can sort its results in the same stepIt can perform distinct countsIt can use “like” expressionsWhile SQL isn’t a complete programming language, ithas “case” expressions, which can be very powerful

Some stuff SAS Proc SQL can do Sending (pass-through) queries to Oracle (or another DBMS)for processing, and receiving the results into a SAS dataset Administration tasks, such as managing SAS datasets andindexes Using the SQL language against SAS datasets as analternative to the Data Step Setting values of macro variables As an alternative to Proc Print

Data stepBasic syntax:data new SAS dataset;set some existing dataset;/* set some existing dataset (keep column 1column 2); to subset variables */* do stuff;run;

Proc SQL Create TableBasic syntax:proc sql;create table new SAS dataset as/* select * for all columns/variables */select column 1,column 2from some existing dataset;quit; Although it says create table, it is actually creating a SAS dataset. PROC SQL terminates with a quit; statement (not run;).

WHERE clauseCan be used in data step statement and within Proc SQL,including within a “case” expressionComparison operators , , , , , or lt, gt, eq, le, ge, neLogic operators, bracketsand, or, note.g. ((a b) and (not (c d)))IN operatorin (values, separated, by, commas)

WHERE clause “like” & “contains”While the “in (list)” has made its way to the IF statement, “like”(and “contains”) have not; they are only in the WHEREThe syntax for CONTAINS is straightforward, e.g.where name contains 'JONES'But I prefer LIKE, which is more powerfulpercent sign (%) – match zero or more charactersunderscore ( ) – match any one charactere.g.where name like 'JONES%'

ORDER BY clauseNot only does PROC SQL not require data to be sortedbeforehand, but you can ask it to sort its resulting outputsimply by adding an ORDER BY clauseThe ORDER BY clause appears last, after the GROUP BY clauseand the HAVING clause, if those are presentThe ORDER BY clause can be used on his own, without groupingThe syntax of the ORDER BY clause is slightly different than the Data Step(and other Procs’) BY statements; the BY statement separates variablesby spaces, while the ORDER BY separates them using commas.

GROUP BY clauseThe GROUP BY clause in Proc SQL lets you summarise data (similar to ProcSummary) but without requiring the data to be sorted beforehand.The GROUP BY clause (if present) follows the WHERE clauseVariables in the GROUP BY are separated by commasUsing a GROUP BY statement does not guarantee the sort order of theresults (although SAS is more likely to put the data into that order thanOracle is). Use an ORDER BY with a GROUP BY if you need to ensure thesort order of the data.Note: the variables in the ORDER BY clause need not match the variables inthe GROUP BY clause.

Summary functionsIf you have a GROUP BY in your query, then every variable youselect should either be listed in the GROUP BY, or besummarised in some way. If you select a variable that is notsummarised and not listed in the GROUP BY clause, you willalmost certainly not get the summarized results you expect.Here are some sample summary functions:sum(services)as services,max(service date)as max servdt,mean(paid amount)as avg paidamt,count(distinct PHN) as patient count

HAVING ClausThe HAVING clause applies after the GROUP BY, WHEREas the WHEREclause applies before groupingThe HAVING clause looks at summarised values, and cannot be usedwithout a GROUP BY clausee.g.proc sql;create table three or more asselect service date,count(*)as record countgroup by service datehaving count(*) 3;quit;

CASE expressionThis is PROC SQL’s closest equivalent to the IF statement. ACASE expression, however, can only return a single value.(an IF statement can use a do/end to to perform multiple actions)The CASE expression consists of a series of WHEN conditions(that use the same syntax as WHERE conditions), followedby ELSE. So it’s really more like an IF THEN/ELSE.Each WHEN condition is accompanied by a THEN expressionthat evaluates to a value.The CASE expression will use the THEN expression of the firstWHEN condition that is found to be True. If none of theWHEN conditions are true, the ELSE expression will be used.It’s good practice to always have an ELSE.

CASE expression exampleproc sql;select case when age 0when age between 1 and 5when age between 6 and 10when age between 11 and 15 else '?' endcount(distinct recipient id)from health servicesgroup by calculated age group;quit;thenthenthenthen' 0'' 1- 5'' 6-10''11-15'as age group,as person cnt

AliasesWhen joining two or more tables, it is useful to use an alias foreach table.The alias can be used as a prefix to variable names to indicatewhich table the variable comes from, which is handier thanusing the whole table name as a prefix.When a variable of the same name appears in more than onetable (being joined using a Proc SQL select statement), youmust specify which table you want to refer to each time yourefer to the variable name. Prefixing variables with the tablealias is the usual way to do this.

LEFT JOIN, RIGHT JOINThe default SQL join is an Inner Join, meaning that only rows that matchacross both tables are includedLEFT JOIN and RIGHT JOIN in Proc SQL always operate on exactly two tables,and the order the tables are listed is very significant. Imagine writingthem on the same line – the first dataset listed is the Left one, and thesecond is the Right dataset.When you use LEFT JOIN or RIGHT JOIN, you use the ON keyword (instead ofthe WHERE keyword) to indicate the join criteria.If you use the INNER JOIN syntax to perform an inner join, you will also needto use the ON keyword

Comparing Inner, Left, and Right joinsHere’s some sample data in two datasets.StudentsStudent ID345678NameGray, JaneAdams, GiselleKeppel, LenGradesStudent ID34345699SubjectMathEnglishMathFrenchGradeABC F

Inner Join (usual, without JOIN keyword)proc sql;create table after inner asselect a.*,b.*aliasfrom studentsa,gradesbwhere a.student id b.student idorder by a.student id;quit;Note: This will give a note in the log that student id already exists in thedataset. Because student id is the same in both datasets (guaranteedby the WHERE condition), this note can be safely ignored.

Okay, here’s how you could rid of the note(without listing all the columns you want)proc sql;create table after inner (drop student id2) asselect a.*,b.*from studentsa,grades(rename (student id student id2)) bwhere a.student id b.student id2order by a.student id;quit;It’s probably easier just to ignore the note in the log.

Results of (default) Inner JoinGradesStudentsStudent ID345678NameGray, JaneAdams, GiselleKeppel, LenStudent ID34345699SubjectMathEnglishMathFrenchDefaultInner Joinon student idAfter InnerStudent ID343456NameGray, JaneGray, JaneAdams, GiselleSubjectMathEnglishMathGradeABC GradeABC F

LEFT Joinproc sql;create table after left asselect a.*,b.*from studentsa left joingradesbon a.student id b.student idorder by a.student id;quit;

Results of Left JoinGradesStudentsStudent ID345678NameGray, JaneAdams, GiselleKeppel, LenStudent ID34345699SubjectMathEnglishMathFrenchLeft Joinon student idAfter LeftStudent ID34345678NameGray, JaneGray, JaneAdams, GiselleKeppel, LenSubjectMathEnglishMathGradeABC GradeABC F

RIGHT joinproc sql;create table after right asselect a.*,b.*from studentsa right joingradesbon a.student id b.student idorder by a.student id;quit;

Results of Right JoinGradesStudentsStudent ID345678NameGray, JaneAdams, GiselleKeppel, LenStudent ID34345699SubjectMathEnglishMathFrenchRight Joinon student idAfter RightStudent ID343456NameGray, JaneGray, JaneAdams, GiselleSubjectMathEnglishMathFrenchGradeABC FGradeABC F

FULL (Outer) joinproc sql;create table after full asselect coalesce(a.student id, b.student id) asstudent id,a.name,b.subject,b.gradefrom studentsa full joingradesbon a.student id b.student idorder by a.student id;quit;

Results of Full (Outer) JoinGradesStudentsStudent ID345678NameGray, JaneAdams, GiselleKeppel, LenStudent ID34345699SubjectMathEnglishMathFrenchFull Joinon student idAfter FullStudent ID3434567899NameGray, JaneGray, JaneAdams, GiselleKeppel, LenSubjectMathEnglishMathGradeABC FrenchFGradeABC F

Traditional SAS Code(Data Step needs helpers!)proc sort data prac info;by prac lha;run;proc summary data prac info;by prac lha;output out prac lha counts(drop type rename ( freq prac cnt));run;27

Proc SQL doing a “summary”proc sql;create table prac lha counts asselect prac lha,count(*) as prac cntfrom prac infogroup by prac lhaorder by prac lha;quit;28

Calculated keyword in Proc SQLThe keyword “calculated” can be used to refer to acolumn being created within a Proc SQL query byname, in a reference later within the same query.It can be used to reference a calculated column withinthe GROUP BY expression, or even in expressions tocreate other columns.There is no abbreviation for “calculated”.

Traditional SAS Codesummarize and lookup a descriptionproc sort data fitm servcd;by servcd;run;proc summary data fitm servcd;by servcd;output out servcd fitm cnts 0(drop type rename ( freq fitm cnt));run;data servcd fitm cnts;set servcd fitm cnts 0;servcd descrip put(servcd, svcd2ds.);run;30

Proc SQL Codeproc sql;create table servcd fitm cnts asselect servcd,put(servcd, svcd2ds.) as servcd descrip,count(*)as fitm cntfrom fitm servcdgroup by servcd, calculated servcd descriporder by servcd;quit;31

Partial results

Proc SQL Codewith joinproc sql;create table servcd fitm cnts asselect a.servcd,b.servcd descrip,count(*)as fitm cntfrom fitm servcdaleft joinservice codesbon a.servcd b.servcdgroup by a.servcd, b.servcd descriporder by 1, 2;quit;33

Select desired observationsusing a Data Step%let startdt sas '01apr2012'd;%let enddt sas '31mar2013'd;data data centres 2;set data centres;where efctvdt &enddt sasand cncldt &startdt sasand dt cntr status in ('D', 'P')and dt cntr typein ('C', 'P')and not ( ' ' dt cntr nm ' ' like '% HOLDINGS %'' ' dt cntr nm ' ' like '% HOSP%'' ' dt cntr nm ' ' like '%SYS%' );run;oror

Anything you can do (well, not anything, but this thing )proc sql;create table data centres with flags asselect efctvdt,cncldt,dt cntr status,dt cntr type,case when (efctvdt &enddt sas) or(cncldt &startdt sas)then '1. Outside date range'when (dt cntr status not in ('D', 'P'))then '2. Status not D or P'when (dt cntr type not in ('C', 'P'))then '3. Type not C or P'when (' ' dt cntr nm ' ' like '% HOLDINGS %'or ' ' dt cntr nm ' ' like '% HOSP%'or ' ' dt cntr nm ' ' like '%SYS%' )then '4. Computing type'else ' 'endas error typefrom data centres;quit;

Informative reportproc freq data data centres with flags;tables error type / missing;run;

Getting the goods, either wayproc sql;create table data centres 2 asselect *from data centres with flagswhere error type is null;quit;ordata data centres 2;set data centres with flags;where error type is null;run;