Introduction to Using PROC SQL - SAS

1 ,TexasStateComptroller sOffice,Austin, @System simplementationofStructuredQueryLanguage , (DATA step,PROCSORT,andPROCMEANS steps), [esinthispresentationpertaintoDB2tablesw hichareusedfortaxadministrationpurposesa ttheauthor ,eachcontainingoneormorerowsandcolumns, ,basedupontheirvalues, (SQL).Sometimes,ImayrefertoSASdatasets(S ASdatafilesandSASdataviews)as tables ,toSASvariablesas columns ,andtoobservationsas rows .Strictlyspeaking,thesearen texactlythesamethings(thereexisttableswh icharen tSASdatasets,andviewscontainnodatathemse lves)buttheunderlyingconceptsarecertainl ycomparable, ,thenwheneverIrefertoa table ,youmaysubstitute SASdatafile;andwhenIwrite column ,substitute SASvariable ;andwhenIsay row ,substitute SASobservation.]

2 StructuredQueryLanguage(SQL) ,eachRDBMS mayuseitsownparticular dialect , ,forexample,SQLallowssuchfamiliaractions asCREATE,SELECT,INSERT,UPDATE,DELETE, , , (thedesiredendresult),butnotaboutapartic ularmethod(thewhereandthehow) (ANSI) , ,whichexceedtheANSI specifications, :lretrieveandmanipulateSASdatasets,ccrea teanddeletedatasets,laddormodifydatavalu esinadataset,.add,modify,ordropcolumnsin adataset, ,flatfiles,VSAM files,databasetables,andcombinationsofth esetodoqueryoperations, ,modify,andretrievedatafromtablesandview s(andSASdatasets).

3 ,andthePRINT,SORT, , , :PROCSQL<option<option>..ALTER alter-statement;CREATE create-statement;DELETE delete-statement;1 DESCRIBEVIEW view-name;DROP drop-statement;INSERT insert-statement;RESET<option<option>..>;SELECT select-statement;UPDATE update-statement;VALIDATE query-expression;We , ,Views, , , , :PROCSQL;SELECTAUDID, 2180 ORDERBYZIPSERVE;Intheprecedingexample,th eSELECT statementspecifiesthecolumn-namesinapart iculartablefromwhichthedataaretobechosen ,itfurthersubsetsthesedataaccordingtoace rtainvaluecontainedinsomeoftherows, :SELECT column-1,column-2.

4 FROM table-a,table-b,.WHERE expressionGROUPBY column-i,column-j,.HAVING expressionORDERBY column-r,column-s,;Hereisanotherexampleo ftheuseoftheSELECT statement:PROCSQL;SELECTTPNUM,TAXID,CMPY _TY, (40,41,45,56)ORDERBYTPNUM;TheVALIDATE statementcanbeusedtocheckthevalidityofaq ueryexpression,withoutexecutingthequery: PROCSQL;VALIDATESELECTTPNUM,TAXID,CMPY_T Y, (40,41,45,56)ORDERBYTPNUM; ~) ;SELECT* ;Often,weneedtocreatenewvariables(tempor arycolumns) ;SELECTTPNUM,TAXID,lNT((TODAY()-FRSBUSDT ) ) ; , ;SELECTTPNUM,TAXID,PDOENDDT,DTLTY, P.

5 Inadditiontothecommoncomparisonoperators (LT,<,GT,>,EQ,=,LE,<=,GE,>=,NE, =, )andthelN-operator,therealsoaresomespeci aloperatorsthatcanbeusedinaWHERE clause:lCONTAINSor? selectsrowsthatincludeaspecifiedstring,l ISNULLorISMISSING--selectsrowsforwhichth evalueofaparticularcolumnismissing,lBETW EEN-AND--selectsrowsinwhichthevalueofthe columnfallswithinarangeofvalues,inchsive oftheendpoints,lLIKE--selectsrowsbycompa ringcharactervaluestospecifiedpatterns[a percentsign( 7.)replacesanynumberofcharacters,andanun derscore(Jreplacesonlyonecharacter],l=.)

6 --selectsrowsthatcontainaspellingvariati onofthespecifiedword(thisisa soundslike operator).PROCSQL;SELECTTPNUM,TAXID,PDOE NDDT,DTLTY, P ANDPDOENDDTBETWEEN 01 SEP95 DAND 31 AUG96 D; , , ,PROCSQL;SELECTDISTINCTTPNUM, , ;SELECTTPNUM,TAXID,PERIOD,GRSALS-DEDUCTS + >5000;UseanORDERBY clausetoreturntheresultsofaqueryinascend ing(thedefault),orindescendingorder, ;SELECTTPNUM,TAXID,CMPY_TY, ;PROCSQL;SELECTAUDID, 2180 ORDERBYZIPSERVEDESC; :PROCSQL;SELECTTPNUMLABEL= TAXPAYERNUMBER,TAXIDLABEL= TAXCODE ,PDOENDDTLABEL= PERIODENDDATEFORMAT=MMDDYY8.

7 ,DTLTYLABEL= RECORDTYPE,PYRNETPMLABEL= NETPAYMENT FORMAT= P; , ,FREQ,N--numberof(non-missing)values,.NM ISS numberofmissingvalues,lMAX-maximum(large st)value,lMIN-minimum(smallest)value,lSU M--sumofvalues,lAVG,MEAN-arithmeticavera gevalue,lSTD--standarddeviationofvalues, lVAR-varianceofvalues,. ;SELECTSUM(PYRNETPM) PANDPDOENDDTBETvVEEN OISEP94 DAND 31 AUG95 D;Ifmorethanonecolumn-nameisspecifiedina summaryfunction,thenthesummaryfunctionop erateslikeaDATA stepfunction, ,andatleastoneothercolumn-name,then,afte rthecalculationisperformed, ,notonthetableasawhole, ,aGROUPBY clausecanbeusedtoseparatethedataintogrou psbaseduponthedistinctvaluesinacolumn, ;SELECTTAXID,SUM(PYRNETPM) P ANDPDOENDDTBETWEEN 01 SEP94 DAND 31 AUG95 DGROUPBYTAXID.

8 (involvingasummaryfunction)thateachgroup inaquerywouldhavetosatisfy, ;SELECTTAXID,SUM(PYRNETPM) P ANDPDOENDDTBETWEEN 01 SEP94 DAND 31 AUG95 DGROUPBYTAXIDHAVINGSUM(PYRNETPM)>500000; (NestedQueries) ,alsocalledsubqueries(orinnerqueries), (theinnerquery,whichisenclosedinparenthe ses) ,theinnermostqueryisevaluatedfirst,thent henextinnermostquery,andsoforth, , :PROCSQL;SELECTDISTINCTTPNUM,CMPY_TY, ( )ORDERBYTPNUM; :PROCSQL; ( ,UM= )ORDERBYCMPY_LIC;Observethatinthistypeof subquery,theWHERE expressioninthe~queryreferstovaluesinata bleinthe~ , , :weusejoinoperationstocombinedatafromtab lesinahorizontal,orside-by-side,manner,u singakeyvalue;andweusesetoperationstocom binedatafromtablesvetilcally--thatis, ( ,inaside-by-sidemanner) ,thoughnotthesameas, (conventionaljoins) ,plusallnon-matchingrowsfromtheleft,both , :/ CARTESIANPRODUCT /PROCSQL;SELECT*FROMFIRST,SECOND.

9 Intheprecedingexample, ,inthiscase,therearenoadditionalconditio nstobesatisfied, ,we INNERJOINOFTABLES /PROCSQL;SELECTlFROMFIRST, ;Asmentionedpreviously,theresultsetforan ouferjoinwouldincludeallrowsfromtheCarte sianProductwithmatchingkeyvalues,plusall non-matchingrowsfromtheleft,both, :/ LEFTOUTERJOINOFTABLES /PROCSQL;SELECT* ;Theprecedingexample,fora/eftouferjoin,w ouldresultintheretrievalofalloftherowswh ichhavematchingvaluesinthecolumnsnamedXi nbothtables,plusallofthenon-matchingrows fromthefirst-mentioned(/eft)table,whichi snamedFIRST, , ,whichwouldincludeallrowsfromtheCartesia nProductwithmatchingkeyvalues,plusalloft henon-matchingrowsfromtherighttableinthe FROM clause:/ RIGHTOUTERJOINOFTABLES*/PROCSQL;SELECT*.

10 Whatfollowsisthecodeforafu//outerjoin,wh ichwouldincludeallrowsfromtheCartesianPr oductwithmatchingkeyvalues,plusallofthen on-matchingrowsfrombothtablesintheFROM clause:/ FULLOUTERJOINOFTABLES FULLOUTERJOIN(USINGCOALESCEFUNCTION) /PROCSQLSELECTCOALESCE( , )ASX,YI,Y2,ZI,Z2, ;Inthisexample,theSELECT clausespecifieseachofthecolumnstobeinclu dedintheresultset,somefromthetableFIRST, , :lIntersectretrievesalluniquerowswhichar ecommontobothtables,.Mretrievesallunique rowsfrombothtables,.OuterUnionretrievesa llrowsfrombothtables,bothuniqueandnon-un ique,lE&t?