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

3 >;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,.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 ;Inadditiontothecommoncomparisonoperator s(LT,<,GT,>,EQ,=,LE,<=,GE,>=,NE, =, )andthelN-operator,therealsoaresomespeci aloperatorsthatcanbeusedinaWHERE clause:lCONTAINSor?

4 Selectsrowsthatincludeaspecifiedstring,l ISNULLorISMISSING--selectsrowsforwhichth evalueofaparticularcolumnismissing,lBETW EEN-AND--selectsrowsinwhichthevalueofthe columnfallswithinarangeofvalues,inchsive oftheendpoints,lLIKE--selectsrowsbycompa ringcharactervaluestospecifiedpatterns[a percentsign( 7.)replacesanynumberofcharacters,andanun derscore(Jreplacesonlyonecharacter],l=.- -selectsrowsthatcontainaspellingvariatio nofthespecifiedword(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.)

5 ,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; (involvingasummaryfunction)thateachgroup inaquerywouldhavetosatisfy, ;SELECTTAXID,SUM(PYRNETPM) P ANDPDOENDDTBETWEEN 01 SEP94 DAND 31 AUG95 DGROUPBYTAXIDHAVINGSUM(PYRNETPM)>500000.

6 (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;Inthepr ecedingexample, ,inthiscase,therearenoadditionalconditio nstobesatisfied, ,we INNERJOINOFTABLES /PROCSQL;SELECTlFROMFIRST, ;Asmentionedpreviously,theresultsetforan ouferjoinwouldincludeallrowsfromtheCarte sianProductwithmatchingkeyvalues,plusall non-matchingrowsfromtheleft,both, :/ LEFTOUTERJOINOFTABLES /PROCSQL;SELECT*.

7 Theprecedingexample,fora/eftouferjoin,wo uldresultintheretrievalofalloftherowswhi chhavematchingvaluesinthecolumnsnamedXin bothtables,plusallofthenon-matchingrowsf romthefirst-mentioned(/eft)table,whichis namedFIRST, , ,whichwouldincludeallrowsfromtheCartesia nProductwithmatchingkeyvalues,plusalloft henon-matchingrowsfromtherighttableinthe FROM clause:/ RIGHTOUTERJOINOFTABLES*/PROCSQL;SELECT* ;Whatfollowsisthecodeforafu//outerjoin,w hichwouldincludeallrowsfromtheCartesianP roductwithmatchingkeyvalues,plusallofthe non-matchingrowsfrombothtablesintheFROM clause:/ FULLOUTERJOINOFTABLES FULLOUTERJOIN(USINGCOALESCEFUNCTION) /PROCSQLSELECTCOALESCE( , )ASX,YI,Y2,ZI,Z2, ;Inthisexample,theSELECT clausespecifieseachofthecolumnstobeinclu dedintheresultset,somefromthetableFIRST, , :lIntersectretrievesalluniquerowswhichar ecommontobothtables.

8 Mretrievesalluniquerowsfrombothtables,.O uterUnionretrievesallrowsfrombothtables, bothuniqueandnon-unique,lE&t?!retrievesa lluniquerowswhichareinthefirst-mentioned table,butwhicharenotalsocontainedinthese cond-mentionedtable(thisisa difference operator). ,thiswillproducearesultsetwhichdoesn tmakeanysenseatall(whencolumnsinthesameo rdinalpositionofthetwotablesrepresentdis similaritems).However,thereisanoptionalk eyword,CORRESPONDING,whichisusedinconjun ctionwiththesetoperatorkeywords, ,thecolumnsinthetablesarelined-upvertica llyaccordingtocolumn-name, :/ INTERSECTSETOPERATIONOFTABLES /PROCSQL;SELECT*FROMFIRSTINTERSECTCORRES PONDINGSELECT*FROMSECOND;/ OUTERUNIONSETOPERATIONOFTABLES /PROCSQL;SELECT*FROMFIRSTOUTERUNIONCORRE SPONDINGSELECT*FROMSECOND;Setoperationso riginatedinmathematicalsettheory,butthey don tseemtobeasusefulasjoins, (andSASDataFilesandViews)Thereareseveral methodsbywhichPROCSQL canbeusedtocreatenewtables(orSASdatafile s)andviews(virtualtables).

9 Themostcommonmethodistocreatetablesorvie wsusingalready-existingtablesorviews, ,usingothertablesorviews:CREATEVIEW view-nameASquery-expression;orCREATETABL E table-nameASquery-expression;wherequery- expressionisoftheform:SELECT column-1,column-2,.FROM table-a,table-b,.WHERE expressionORDERBY column-r,column-s,Hereisatypicalexampleo faninnerjoinforaSASviewPROCSQL; , , , , , , , , , , , , , , ;Thisjoinwouldcreateatemporaryviewwhichc ombinessalestaxpayeroutletgeneralinforma tionwithoutletpermitinformation, (SASdatafile).Anotherwaytocreateanewtabl ewithPROCSQL wouldbetodefinethecolumnsandthentofillin therowsofdatalater, ,5youcanaddnewcolumns,youcanchangethefor matswhichareusedtodisplaycolumnvalues, ;ifyoudon t,# , ,howtocreatetemporarycolumnsfromexisting columns,howtosorttherowswhicharedisplaye dintheresultset,andhowtoassociatelabels, , Ssubstantialdatamanipulationandsummariza tioncapabilitiescouldbeusedtoreplacemany DATA step,PROCSORT, ,andthePROCSQL codegenerallywillexecuteinlesstime, ,fromaSASdatafilewhichcontainedadvancere gistrationinformationforthe1996 South-CentralRegionalSASU sers :DATAA; ;AMTDUE=TOTAL-AMTPAID;KEEPCITYCOMPANYSTA TUSAMTPAID;PROCSORTDATA=A.

10 BYCITYCOMPANYPAID;PROCMEANSDATA=ANNOPRIN ~VARAMTDUE;BYCITYCOMPANYSTATUS;OUTPUTOUT =STATSSUM(AMTDUE)=;PROCPRINTDATA=STATS;R UN;ThefollowingPROCSQL stepproducesessentiallythesamereportasth eprecedingtraditionalcode:PROCSQLSELECTC ITY,COMPANY,STATUS,FREQ(STATUS)ASNUMTYP, SUM(TOTAL-AMTPAID) ,COMPANY,STATUSORDERBYCITY,COMPANY;Inthe precedingexamples,thetraditionalSASprogr amcontainsmorelinesofcode, , , , ,whenyouareselectingdatafrommultipletabl es,theDBMS generallyisabletoreturntheresultsetmoreq uicklythanifyoudideverythingfromwithinth eSASS ystem, :PROCSQL;CONNECTTODB2(SSID=DSNT);CREATEV IEWCOUNTYASSELECT*FROMCONNECTIONTODB2(SE LECT* );O/oPUT DISCONNECTFROMDB2;QUIT;PROCPRINTDATA=COU NTY;RUN;ObservetheSELECT clausewhichisenclosedinparentheses,follo wingthe SELECT*FROMCONNECTIONTODB2 , (DATA step,PROCSORT,andPROCMEANS steps).