說明統(tǒng)計軟件chapter

PROCSQLSQL 1/SQL JOIN SQLSQL2/SQL

SQL

3/SQL

SQL

3/SQL

SQL

3/SQL4/SAS數(shù)據(jù)SASSASSASSAS數(shù)據(jù)SASSASSASSAS數(shù)據(jù)SASSASSASSAS數(shù)據(jù)SASSASSASSAS數(shù)據(jù)SASSASSASSQL能

SQL

5/SASSQLSAS(SASData

SQL

6/SQL7/PROCPRINTPROCSORTRUNQUITPROCPROCPRINTPROCSORTRUNQUITPROCPROCPRINTPROCSORTRUNQUITPROCPROCPRINTPROCSORTRUNQUITPROCPROCPRINTPROCSORTRUNQUITPROCSQLPAGE7/PROCPRINTPROCSORTRUNQUITPROCSQLSQL8/SELECTSAS 過8個字SELECTSAS 過8個字SELECTSAS 過8個字SELECTSAS 過8個字SELECTSAS 過8個字SELECTSAS 過8個字SELECTSAS 過8個字SQL8/SELECTSAS 過8個字SQLSQL9/5.5.FORMATheisashelp.classDATApreteenSQLSQL10/SELECTcolumn<,column>..FROMtable|view< table|view>..<WHEREexpression><GROUPBYcolumn<,column>...><HAVINGexpression><ORDERBYcolumn<,column>...SELECTFROMWHEREGROUPBYORDERBY

SQL

11/SQL12/ sqlvalidat selectSELECTSELECTSELECTselselect ... sql sqlvalidat selectSELECTSELECTSELECTselselect ... sqlSQLSQL12/ sqlvalidat selectSELECTSELECTSELECTselect .select ... sql sqlvalidat selectSELECTSELECTSELECTselect .select ... sqlSELECTFROM標(biāo)出數(shù)據(jù)來源PROCPROC FROMpreteen;SQL 13/preteenpreteen PROCSQL 14/DATAgirls;SET preteen; PROCCREATETABLEgirlsASSELECT*FROMpreteen SQLSQL15/.SQL16/PROCPROCCREATETABLEnewAS preteen;SQL 16/PROCPROCCREATETABLEsubsetASSELECTfname, ,ageFROMpreteen;SQL 17/SQLSQLPAGE18/SELECTSELECTFROMpreteenweight PROCCREATETABLEsubset2(DROP=heighweightpreteenheiDATAsubsetDATAratios;SET preteenATTRIBRatioLABEL=’Weight:Height?Ratio’;ratio=weight/height;PROCCREATETABLEratios2AS *,weight/heightASRatioFORMAT=5.2LABEL=’Weight:Height?Ratio’ preteen;;PROC;PROCCREATETABLEoverall_averages SELECTMIN(age)ASYoungestOldestMEAN(heightFROMPROCSUMMARY用GROUPBY可以實現(xiàn)按類求和等功能。 VAR hei weightOUTPUTOUT=group_averages(DROP=_type__freq_)MIN(age)=YoungestMAX(age)=OldestMEAN(height)=Avg_HeightMEAN(weight)=Avg_Weight;PROCCREATETABLEgroup_averages ,MIN(age)ASYoungest,OldestMEAN(heightFROMpreteenGROUPBYSQL 21/別。另外，GROUPBYFROM后面。PROCCREATETABLEgroup_averages MIN(age)ASYoungest,MAX(age)ASOldest,MEAN(height)ASAvg_HeightFORMAT=5.1,MEAN(weight)ASAvg_WeightFORMAT=5.1FROMpreteenGROUPBYSQLSQL22/DATAthreex3;INPUTabc@@;1.12.03.06.05.04.47.78.09.0;PROCSELECTMEAN(a,b,c)LABEL=’Mean?of?3FROMthreexPROCSELECTMEDIAN(a,b,c) LABEL=’Median?of?3’FROMthreex3SQL23/SQL23/PROCPROCSELECTMEAN(a)LABEL=’Mean?of?1FROMthreexPROCSELECTMEDIAN(a) LABEL=’Median?of?1FROMthreex可見，MEANMEDIAN只能橫向求中位MIN,MAX,NNMISS只支持縱向求值。SQL 24/SQLSQLPAGE25/PROCSQLIF-THENWHEREDATAgirls;SET preteen; PROCCREATETABLEgirlsASSELECT*FROMpreteen WHERE語句按行選擇記錄，HAVING OUTPUTMAX(height)=TallestMIN(height)=ShortestOUT=hilo(DROP=_type__freq_);PROCCREATETABLEhiloAS MAX(height)ASTallest,MIN(height)ASShortestFROMpreteenGROUPBY, OUTPUTMAX(height)=TallestMIN(height)=ShortestOUT=hilo(WHERE=(tallest? shortest>4)DROP=_type__freq_)PROCSQL;CREATETABLEhiloAS MAX(height)ASTallest,MIN(height)ASShortestFROMpreteenGROUPBY,HAVINGtallest shortest>SQL 27/SQLSQLPAGE28/PROCPROCSORTDATA=preteenOUT=age_sort;BYDESCENDINGagefname;PROCSQL;CREATETABLEage_sortASSELECT*FROMORDERBYageDESCENDING,DISCINCTPROCCREATETABLE _ageAS FROMpreteenPROCSORT BY_ALL_;PROCSQL;CREATETABLE _age_distinct SELECTDISTINCT _age;PROCPROCCREATETABLEteensSELECTnameASFName,FROMsashelp.classWHEREage>12SQL 30/SQLSQLPAGE31/SELECTSELECTfnameCOUNT(*)ASFROMteensGROUPBYageORDERBYfname;PROCCREATE detaiPROCPROCSELECT LABEL= ’Weighted’FORMAT=8.3,MEAN(DISTINCTage)LABEL=’Unweighted’FORMAT=8.3FROMteensAVG,MEANCOUNT,FREQ,MAX

SQL

33/SQLSQLPAGE34/PROCCREATETABLElargestASSELECT*FROM(SELECTagecount(*)ASManyFROMteensGROUPBY)HAVINGmany=PROCSELECTFROMsashelp.classWHEREname=’Jane’ PROC FROMsashelp.classWHERE=’Jane’ PROCSELECTFROMsashelp.classWHEREsashelp.=’Jane’ NAME沒有前綴，主要是因為SAS并不接受。

SQL

36/SQLSQLPAGE37/FROMsashelp.classFROMsashelp.classASWHEREs_h_=’Jane;s_h_c.PROCPROCSELECTLOWCASE(name)ASnameFROMsashelp.classPROCSELECTLOWCASE(name)ASlownameFROMsashelp.classWHERElowname=’jane’;PROCSELECTLOWCASE(name)ASnameFROMsashelp.classNAME，得不到想要的結(jié)果。第二個程序直接報lowname。第三個程序中用了CALCULATED后可

SQL

39/SQLSQLPAGE40/2121,22=DODATAoneDOValue1=11,12;DATAtwoDATADATAcombined;MERGEonetwo;PROCPROCCREATETABLEcombinedASSELECT*FROMoneCROSSJOINBYkey;BYkey;MERGEsorted1 sorted2;BYkey;PROCPROCSQL;SELECT*FROMu1FULLJOINONu1.key=u2.FROMu1FULLJOINFROMu1FULLJOINONu1.key=u2.keyu2.key)ASKey,PROCCREATETABLEcombinedSELECTCOALESCE(u1.key*FROMFROMu1FULLJOINONu1.key=u2.keyu2.key)ASKey,PROCCREATETABLEcombinedSELECTCOALESCE(u1.keyvalue1valueOuterJoinouterjoin。共三種，F(xiàn)ULLLEFT

SQL

46/SQLSQLPAGE47/PROCSQL;SELECT*FROMu1LEFTJOINONu1.key=u2.PROCCREATETABLEcombined_left SELECTu1.*,valueFROMu1LEFTJOINONu1.key=u2.BYBYkeyIF in1sorted2DATAcombined_leftMERGEsorted1(IN=in1)RIGHTJoinLEFT

SQL

49/PROCCREATETABLEcombined_innerASSELECTu1.*,valueFROMu1INNERJOINONu1.key=u2.DATAcombined_innerMERGEsorted1(IN=in1)BYkeyIFin1ANDin2;

sorted2(IN=in2)SQL50/由此，mergeSQL50/SQLSQLPAGE51/AA21.2A21.3C23.1CA;DATAINPUTKey$Value2A11.2B12.1BA;DATAINPUTKey$Value1MERGEm1MERGEm1BYkeyDATAmany_innerMERGEm1(IN=in1)m2(IN=in2)BYkeyIFin1andin2PROCPROCCREATETABLEmany_innerASSELECTm1.*,valueFROMm1INNERJOINONm1.key=m2.如果是一對多，MERGESQL

SQL

54/SQLSQLPAGE55/OUTERJOINFROMFROMm1FULLJOINONm1.key=m2.;m2.key)ASKeycoalesce(m1.keyvalue1valuePROCCREATETABLEone_many_outerDATAm3;INPUTKey$Value1@@;A7.1A7.2C8.1C8.2;PROCCREATETABLEsql_from3ASSELECTm1.*,m2.value2 asTenths,m3.value1 asweightFROM(m1JOINm2ONm1.key=m2.key)JOINm3ONm1.key=m3.key=SQL57/SQL57/SQLSQLPAGE58/JOINKEYON。PROCvalue1valueFROMm1FULLJOINONm1.key=m2.

,m2.key)ASKeyPROCSELECTFROMm2NATURALFULLJOINJOINONWHEREPROCSELECTm1.keym1.value1 asTenths,m2.value2, asFROM(m1JOINm2ONm1.key=m2.key)JOINm3ONm1.key=m3.key;PROCSELECTm1.keym1.value1 asTenths,m2.value2, asFROMm1,m2,

SQL

60/JOINPROCCREATETABLEclassgirlsASSELECT*FROMsashelp.class(RENAME=(name=FName)) =’F’;DATAmoregirls;INPUTFName$Age;Susan Abigail ;SQLSQL61/TRUE1WHERESQL62/SQL62/SQLSQLPAGE63/PROCPROCCREATETABLEalreadyASSELECT*FROMmoregirlsWHEREEXISTS SELECTFROMclassgirlWHEREmoregirls.fname=classgirls.)FNAMEJaneJane在兩個數(shù)據(jù)中有相同的FNAME。其實，EXISTSSELECT后的內(nèi)容并不重PROCPROCCREATETABLEalreadyASSELECT*FROMmoregirlsWHEREEXISTS(SELECT o?World’FROMclassgirlsWHEREmoregirls.fname=classgirls.)PROCCREATEPROCCREATETABLEalreadyASSELECT*FROMmoregirlsWHEREfnameIN(SELECTfnameFROMclassgirls)PROCCREATETABLEalreaPROCCREATETABLEalreadyASSELECT*FROMmoregirlsWHEREfname=ANY SELECTfnameFROMclassgirls)PROCSELECTPROCSELECTFROMmoregirlsWHEREmoregirls.fname>ANY SELECTfnameFROMclassgirl)SQL 67/PROCCREATETABLEalreaPROCCREATETABLEalreadyASSELECT*FROMmoregirlsWHEREnot(fnameNEALL SELECTfnameFROMclassgirl))SQLSQL68/PROCSELECTPROCSELECTFROMmoregirlsWHEREmoregirls.fname>ALL SELECTfnameFROMclassgirl)SQL 69/PROCPROCSELECTFROMmoregirlsORDERBYfnameIN SELECTfnameFROMclassgirls)SQLSQL70/FNAME

SQL

71/CREATECREATETABLEbehindscenes SELECTfnameIN SELECTfnameFROMclassgirls)*FROMmoregirls;SQLSQL72/

SQL

73/(JOIN)JOINFROMSELECTSELECT

SQL

74/(JOIN)JOINFROMSELECTSELECT

SQL

74/PROCCREATETABLEoneSELECTnameasFName,weight,ageFROMsashelp.classWHEREage<13andLENGTH(name)GE5ORDERBYage,RANUNI(1);CREATETABLEtwoSELECTnameasFName,age, FROMsashelp.classandLENGTH(LE5BYageRANUNI(2)SQLSQL75/PROCSELECTPROCSELECTfnameFROMSELECTfname FROMtwoSQL 76/INTERSECTPROCPROCSELECTfnameFROMSELECTfname, FROMtwo;SQL 77/FROMFROMSELECTfname, FROMtwo;PROCSELECTfnameSQLSQL78/OUTERPROCPROCSELECTfnameFROMOUTERSELECTfname, FROMtwo;SQL 79/SQL80/OUTERUNIONSELECT結(jié)果，即使是重復(fù)行也不OUTERUNIONSELECT結(jié)果，即使是重復(fù)行也不OUTERUNIONSELECT結(jié)果，即使是重復(fù)行也不OUTERUNIONSELECT結(jié)果，即使是重復(fù)行也不PROCCREATEPROCCREATESELECTSELECTfname FROMoneSELECTfname FROM;SQL 81/DATAconcatSETonetwoPROCCREATETABLEconcat1ASSELECT*FROMSELECT*FROMtwoSQL82/SQL82/SQLSQLPAGE83/ DATAinterleave;SETonetwo;BYage;PROCSQL;CREATETABLEinterleave1ASSELECT*FROMSELECT*FROMtwoORDERBYage;FROMORDERBY;PROCCREATETABLEinterleave(DROP=suborder) SELECT*,1ASsuborderFROMOUTERUNIONSELECT*,2AS

SQL

85/

SQL

85/SQL86/ ，則OUTERINTERSECTEXCEPT ，則OUTERUNION不對齊UNION,INTERSECTEXCEPT均默認(rèn)為列位置對齊，即第SELECTSELECT的首個選擇變量

SQL

87/ ，則OUTERINTERSECTEXCEPT ，則OUTERUNION不對齊UNION,INTERSECTEXCEPT均默認(rèn)為列位置對齊，即第SELECTSELECT的首個選擇變量

SQL

87/SQL88/OUTERUNION將所有的包含在結(jié)果中若有CORRESPONDING，則UNION，INTERSECTEXCEPT若無CORRESPONDING，當(dāng)兩個SELECT語句中選擇變據(jù)的地方，SAS自動填充缺失值。OUTERUNION將所有的包含在結(jié)果中