SQLMarch2007.docx
《SQLMarch2007.docx》由会员分享，可在线阅读，更多相关《SQLMarch2007.docx（61页珍藏版）》请在冰豆网上搜索。
SQLMarch2007
TopicPROCSQL
1.Introduction
2.GeneratereportsusingPROCSQLandtheappropriateoptions
3.ConstructsubquerieswithinaPROCSQLstep
4.ComparesolvingaproblemusingtheSQLprocedureversususingtraditionalSASprogrammingtechniques
5.AccessDictionaryTablesusingtheSQLprocedure
6.CombiningTablesverticallyusingSetOperationsinPROCSQL
7.JoiningTableshorizontally
8.CreatingandManagingTablesusingPROCSQL
9.CreatingIndexusingPROCSQL
10.DATAView
11.CreatingMacro
12.IntegrityConstraints
13.SQLProceduresPass-ThroughFacility
1.Introduction
SQL,anacronymforStructuredQueryLanguage,isanANSI（AmericanNationalStandardsInstitute）standarddatabaseprogramming/querylanguage.SQLisastandardized,widelyusedlanguagethatretrievesandupdatesdataintablesandviewsbasedonthosetables.
TheSASSystem'sSQLprocedureenablesyouto
∙retrieveandmanipulatedatathatarestoredintablesorviews.
∙createtables,views,andsoforth.
∙createSASmacrovariablesthatcontainvaluesfromrowsinaquery'sresult.
∙addormodifythedatavaluesinatable'scolumnsorinsertanddeleterows.Youcanalsomodifythetableitselfbyadding,modifying,ordroppingcolumns.
∙createtablesandview
∙joinmultipletables
∙generatereports
∙sendDBMS-specificSQLstatementstoadatabasemanagementsystem（DBMS）andtoretrieveDBMSdata.
1.1WhatisSQLtable?
APROCSQLtableissynonymouswithaSASdatafileandhasamembertypeofDATA.YoucanusePROCSQLtablesasinputintoDATAstepsandprocedures.ArowinatableisthesameasanobservationinaSASdatafile.Acolumnisthesameasavariable.
1.2Table-ComparisonofSQLterms,BaseSAS,andDataprocessingterminology:
SQLBaseSASDataProcessing
_______________________________________________________________________
tableSASdatafilefile
rowobservationrecord
columnvariablefield
_______________________________________________________________________
1.3WhyuseSQLorlearnit?
∙Powerful-candomanythings
∙Flexible-justsaywhatyouwant
∙Compatible-youcanusetheSQLyoulearnlaterinotherapplications
∙Required-forSASadvancedprogrammer
1.4SASDataStepandSQL
SASSQL
CreatedatasetCreatetables
UpdatevaluesUpdatevalues
DeleterecordsDeleterecords
AppendnewrecordsUnion/Insert
SortdataSortdata
MergedatasetsJointables
1.5SQLProcedureCodingConventions
SQLstatementsaredividedintoclauses.Forexample,themostbasicSELECTstatementcontainstheSELECTandFROMclauses.ItemswithinclausesareseparatedwithcommasinSQL,notwithblanksasintheSASSystem.
TheSELECTstatement,whichisusedtoretrievedata,alsooutputsthedataautomaticallyunlessyouspecifytheNOPRINToptioninthePROCSQLstatement.ThismeansyoucandisplayyouroutputorsendittoalistfilewithoutspecifyingthePRINTprocedure.
TheORDERBYclausesortsdatabycolumns.Inaddition,tablesdonotneedtobepresortedbyavariableforusewithPROCSQL.Therefore,youdonotneedtousetheSORTprocedurewithyourPROCSQLprograms
APROCSQLstatementrunswhenyousubmitit;youdonothavetospecifyaRUNstatement.IfyoufollowaPROCSQLstatementwithaRUNstatement,theSASSystemignorestheRUNstatementandsubmitsthestatementsasusual.ThisprocedurealwaysstartswithaPROCSQLstatement,andendwithaQUIT（notrun;）statement.
1.6HowPROCSQLIsUnique
PROCSQLdiffersfrommostotherSASproceduresinseveralways:
∙UnlikeotherPROCstatements,manystatementsinPROCSQLarecomposedofclauses.
∙ThePROCSQLstepdoesnotrequireaRUNstatement.PROCSQLexecuteseachqueryautomatically.IfyouuseaRUNstatementwithaPROCSQLstep,SASignorestheRUNstatement,executesthestatementsasusual,andgeneratesthenoteshownbelowintheSASlog.
∙UnlikemanyotherSASprocedures,PROCSQLcontinuestorunafteryousubmitastep.WhenyousubmitaPROCSQLstepwithoutendingit,thestatuslinedisplaysthemessagePROCSQLrunning.
1.7GeneralSyntax
Asmentionedabove,thisprocedurealwaysstartswithaPROCSQLstatement,andendwithaQUIT（notrun;）statement.The*isoftenusedinSQLtomean“everythingavailable”orselectallofthevariablesinthetable.Theorderoftheclausesisnotflexible.SQLcodeobeystherulesoftheSASsystemregardingmaxlengthsofvariablenames,reservedwordsetal.
PROCSQL;
ALTERTABLEinstruction-alter;
CREATEinstruction-create;
DELETEinstruction-delete;
DROPinstruction-drop/view,table,indexfrom;
INSERTinstruction-insert;
RESET;
SELECTinstruction-select;
Distinctobject-item
INTOmacro-variable-specification
FROMfrom-list
WHEREsql-expression
GROUPBYgroup-by-item
HAVINGsql-expression
ORDERBY
UnlikeotherSASprocedures,theorderofclauseswithinaSELECTstatementinPROCSQLisimportant.Clausesmustappearintheordershownabove.
TodothisUsethisstatement
_________________________________________________________
Modify,add,ordropcolumnsALTERTABLE
EstablishaconnectionwithaDBMSCONNECT
CreateanindexonacolumnCREATEINDEX
CreateaPROCSQLtableCREATETABLE
CreateaPROCSQLviewCREATEVIEW
DeleterowsDELETE
DisplayadefinitionofatableorviewDESCRIBE
TerminatetheconnectionwithaDBMSDISCONNECT
Deletetables,views,orindexesDROP
SendaDBMS-specificnonquery
SQLstatementtoaDBMSEXECUTE
AddrowsINSERT
SelectandexecuterowsSELECT
QueryaDBMSCONNECTIONTO
ModifyvaluesUPDATE
_____________________________________________________________
2.GeneratereportsusingPROCSQLandtheappropriateoptions
Besidesusingdifferentstatementsyntax,anobviousdistinctionbetweenPROCPRINTandPROCSQListhataQUITstatementisspecifiedforthelatter,ratherthanaRUNstatement,toterminateprocessing.
2.1ThePROCSQLstatementillustratedbelowproducesadetail-orientedreport（similartoPROCPRINT）consistingofallobservationsandvariables.
datapeople;
inputpersoniddateagerace$gender$@@;
formatdatemmddyy10.;
informatdatemmddyy8.;
cards;
00109/18/8723CaucasM00201/23/9055AsianM
00306/05/9316HispanicF00411/14/9321Afr-AmM
00503/27/9533Afr-AmF00608/28/9645CaucasF
00702/01/9827CaucasM00804/20/8819CaucasM
00907/12/9224HispanicF01012/06/9430AsianF
;
procsqldouble;/*nodouble,feedback,noprint,INOBS=n,OUTOBS=n,number*/;
title'Thisistheentiresubjects';
select*frompeople;
quit;
TheSELECTstatementisthesimplestandmostcommonlyusedinSQL.
INOBS=nrestrictsthenumberofrows（observations）thatPROCSQLretrievesfromanysinglesource.
OUTOBS=nrestrictsthenumberofrows（observations）intheoutput.ItissimilartoOBS.
FEEDBACKoptioninthePROCSQLstatementwritestheexpandedlistofcolumnstotheSASlog.ItisadebuggingtoolthatletsyouseeexactlywhatisbeingsubmittedtotheSQLprocessor.
NOEXECoptiondoesnotexecutethisprocedure.
/*toselectuniquevaluesofoneormorecolumnsthedistinctkeywordisused*/;
procsql;
createtablepatidas
selectdistinctpatient
fromalec.vitals;
quit;
procsqlnoexec;
selectage,race
frompeople;
quit;
TheSELECTStatement
TheSELECTstatement,whichfollowsthePROCSQLstatement,retrievesanddisplaysdata.Itiscomposedofclauses,eachofwhichbeginswithakeywordandisfollowedbyoneormorecomponents.TheSELECTstatementinthefollowingsamplecodecontainsfourclauses:
therequiredclausesSELECTandFROM,andtheoptionalclausesWHEREandORDERBY.Theendofthestatementisindicatedbyasemicolon.
procsql;
|-selectempid,jobcode,salary,
|salary*.06asbonus
|----fromsasuser.payrollmaster
|----wheresalary<32000
|----orderbyjobcode;
quit;
APROCSQLqueryproducesaresultsetthatcanbeoutputasareport,atable,oraPROCSQLview.
TypeofOutput
PROCSQLStatements
report
SELECT
table
CREATETABLEandSELECT
Selectcolumnsandcreatenewcolumns
datasumy;
inputIdNumberJobcode$Salary@@;
cards;
1845BCK259961673CK1254771834BCK268961387NF225028
1100BCK250041677BCK260071663PA3264521353NF125823
1704NF1254651981FA1224131634BCK268961389NF225028
1100GK1250041977BCK260121363PA3264881083PT123823
1965PT1274651154FA122613
;
procsql;
createtablesyas
selectIdNumber,Jobcode,Salary,
avg（salary）asAvgSalary,count（idnumber）asN,
sum（salary）astotal_sumformat=dollar10.2,freq（jobcode）
ascountfromsumy（obs=10）
groupbyjobcode;
quit;
ThekeywordFROMspecifiesthetable.
WHEREclausecanbesubsetdatabasedonaconditionintheSELECTstatement.
Tocreateanewtablefromtheresultsofaquery,useaCREATETABLEstatementthatincludesthekeywordASandtheclausesthatareusedinaPROCSQLquery.TheCREATETABLEstatementstoresyourqueryresultsinatableinsteadofdisplayingtheresultsasareport.
2.2ThePROCSQLstatementproducessummaryreport
/*Tocreateatablewithaddinganewvariables*/;
seeaboveexample.
/*INcondition*/;
sql-expressionIN（constant<,constant>…）
sql-expressionIN（query-expresssion）
procsql;
title'InformationforCertainEmployeesOnly';
selectIdNumber,Salary,Jobcode
fromsumy
whereidnumbernotin（1363,1387,1704）;
quit;
*whereidnumbernotin（selectsalaryfromsumywheresalarygt26000）;
/*IScondition*/;
ISNULLandISMISSINGarepredicatesthattestforamissingvalue.ISNULLandISMISSINGareusedintheWHERE,ON,
升级会员 