cda11期二sas初級數(shù)據(jù)分析

第1章12.2.SAS33SASSAS 國際上的標準軟件系統(tǒng)，被學術(shù)期及研 341.1.SAS2.2.SAS33SAS

Base

6PROCSETINIT;77Base89 1.1.SAS2.2.SASSAS命令行命令行

例：PROCPRINTDATA=A;的SAS任務(wù)： SASSASSAS據(jù)集”。在命令行中輸入KEYS第2SAS2.2.SAS3.3.SAS

方法1：用菜單操作；LIBNAMElibref<engine>'SAS-data-library'LibrefSAS-data-library邏輯庫對應(yīng)的物理地址Engine引擎名稱（缺失時為默認引擎）例用LIBNAME語句創(chuàng)建SAS邏輯庫。LibnameResDat‘d:\ResDat’；Libnameaf代表邏輯庫名代表該邏輯 work.employee與employee等同

1.1.SAS22SAS3.3.SASSAS SAS

方法2：用PROCPRINT語句。PROCPRINTDATA=SASHELP.CLASS;方法：用PROCCONTENTSPROCCONTENTSDATA=SASHELP.CLASS; DATASQL Data數(shù)據(jù)集名稱/view=數(shù)據(jù)集名稱SAS語句DatasetPROCSQLproccreatevieweasselect*from 1.1.SAS2.2.SASDATAPROC OPTIONSoption(s); （可以用PROCOPTIONS）例Dataone;Setclass;

+加-+加-減*乘/除 Datak.r;putX=;putY=;put /*Y也是缺失值

==>< ifx<ythenz＝7；elsez＝12;Wherex>=10;結(jié)果為真賦值1，假賦值字符值從左到右逐個按ASCII＆與|＆與|或^非ifx<yand elsez＝12;Ifz<8 else

thenm=1;

：1.1e11，1.2e-例1,–5,1.23,1.2E23,2E4, SAS日期常數(shù)以數(shù)值形式，數(shù)值為與01JAN1960日的差值。如下圖

if then DATASET =“”; Dataa;使用INPUTDatab;Inputx$;;使用FORMAT或INFORMATDatab;Setb;Informaty以類似的方式創(chuàng)建變量的還有LENGTH語句，ATTIRB Data

/*X/*YX=Y;/*將字符變量賦值給一個數(shù)值變量時，自動將字符變量的值轉(zhuǎn)換為數(shù)值賦給數(shù)值變run Data

/*X/*Y/*例3DataifX<Y;

/*X/*Y/* Data /*Y換為數(shù)值*/

dataX=’10’;/*XY=10;/*YX=Y;/*自動將數(shù)值變量Y /*YZ=’ROOM’||Y;/* put data /*YZ=SUBSTR(Y,7,6);/*putdatadatasetsashelp.class;keep_numeric_;/* 11 PROC Excel97,2000,2002,2003,or2007spreadsheetusingtheLIBNAMEstatement.Delimitedfile(defaultdelimiterisDelimitedfilewithcomma-Delimitedfile(tab-delimitedSPSSAccess2000,2002,2003,or2007tableusingtheLIBNAMEStatadBASE5.0,IV,III+,andIII 例導(dǎo)入EXELL 例procimportout=classdbms=TABre 例導(dǎo)入CSV 1to2則為則默認為1to 1to21toSheet 11 datadata-set-<infileinputfiled1filed2$;

datadata-set-name;inputfiled1filed2$;;INPUTvariable<$>start-column<-end-column> ces>|INPUT<pointer-control>variableinformat.<@|INPUT<pointer-control>(variable-list)(informat-list)<@|INPUT<pointer-control>(variable-list)(<n*>informat.)<@|INPUT<pointer-control>variable<$><&><@|INPUT<pointer-control>variable<:|&|~><informat.><@|INPUT<pointer-control>variable=<$><@|INPUTvariable=<$>start-column<-end-column><@|INPUT<pointer-control>variable=informat.<@| 2477195177243122021324561731662412135125@$informat-listINPUTvariable<$>start-column<-end-column><.decimal ces><@|@@>;<- datainputname$1- $11age12-15 F12 M; INPUT<pointer-control>variable<$><&><@| inputUniv:$12.Plc$Zip;MITBostonTsinghuaUnivBeijing;不是預(yù)先要讀入的TsinghuaUniv，這是因為SAS默認的字符變量的長1.datainputname&$12.age;Jiang Annie I. ;例中，第一個觀測name中JiangZhu(包含一個空格)，第二個觀測為AnnieZheng（包含一個空格)。分隔符為兩個空格。例DSD和’~’的作用，讀入字符值時保留引號?！瘇’只在INFILE語句中與選datainfiledatalinesinputspeakers:$15.title~$40.location&$10.;Song,"CreditDerivatives",RoomZhu,"CreditRiskManagement",Room;procprint;而觀測值得長度都不到40，仍然可以得到正確結(jié)果。所以“~”包含“:”的功datainfiledatalinesinputspeakers~$15.title~$40.location&$10.;Song,"CreditDerivatives",RoomZhu,"CreditRiskManagement",Room;procprint;1"CreditRoom2"CreditRiskRoom但是，若把title后成的“~”“:”，引號就因為dsd選項的存在被datainfiledatalinesinputspeakers:$15.title:$40.location&$10.;Song,"CreditDerivatives",RoomZhu,"CreditRiskManagement",Room;procprint;2CreditRoom329CreditRiskManagementRoomINPUT<pointer-control>variableinformat.<@|@@>;INPUT<pointer-control>(variable-list)(informat-list)<@|INPUT<pointer-control>(variable-list)(<n*>informat.)<@|@@>;datainputname$12.+4score1comma5.+6score2comma5.;;INPUT<pointer-control>variable=<$><@|@@>;INPUTvariable=<$>start-column<-end-<.decimals><@|INPUT<pointer-control>variable=informat.<@|datainputname=$score1=score2=;;第4章SAS(dataset) IF DATAIF_N_LE5;

DATAIF_N_LE5; WHEREWHEREwhere-Same-IsMissing|IsNu ①Wherehstocdbetween600000and②Wheretaxesbetweensalary*0.30and③Wheretaxesnotbetweensalary*0.30and⑤Wherenameis 擇模式與結(jié)果如下：①Like②Like ③Like ④Like

/*選擇 /*/*Diana,Diane,Dianna,Dyan⑤Notlike'D_an%';/*Wherex;Wherexandy;Wherex/y;Wherec^='wherecisnotWherexbetween1and5;where1<=x<=5;data where_n_<100;*錯誤語句，必須用if*/data if_n_<100;WHERE語句是在執(zhí)行數(shù)據(jù)集連接（SET） 沒有選項

將當前觀測輸出到DATA語句中命名的所有數(shù)據(jù)集中規(guī)定輸出當前觀測的數(shù)據(jù)集例datainputID$score1-score3;dropscore1-score3;score=score1;output;score=score2;output; 021269996021288990;procprint;dataAB; ifStktype=’A’thenoutputAstk;elseifStktype=’B’thenoutputbstk; procsortdata=lstkinfoout=lstkinfo;bystktype;datasetlstkinfo;bystktype;iffirst.StktypethenNum=0;keepstktypenumiflast.stktypethenoutput; 例data ifStktype=’B’thendelete;/*等價于IFStktype^=’B’*/data 例STOPdata if_n_ ge4 thenstop (dataset)(dataset) datasetsashelp.class; procprint; data lstknm=' procprint; 例datalengthlstknm$12/*規(guī)定變量lstknm的長度為12*/lstknm='大 procprint; dataa(keep=nameheights_h); class;procprint; datab(keep=nameheights_h);setclass;s_h=sum(s_h,height,retains_h0;

dataa(keep=date Dret(where;/ nmis+sector^='';1.1. SAS數(shù)據(jù)集都適用。data keep ;/*keepprocprintdata=a(obs=10);dataa(keep= procprintdata=a(obs=10);DROPvariable-data age;/*dropprocprint;dataa procprint; 語句和DROP語句會首先起作用。即，在KEEP語句或 例RENAME語句和KEEP語句一起使用時，KEEP語句使用舊變data keep datasetkeepnamegender;/* 1.1. 沒有選項規(guī)定用INPUT語句或賦值語句創(chuàng)建的所有變量值沒有選項規(guī)定用INPUT語句或賦值語句創(chuàng)建的所有變量值從DATA步的這次執(zhí)行到下一次重復(fù)時被保留Element-規(guī)定變量名字，變量列表或數(shù)組名列出RETAIN語句所規(guī)定變量的初始值規(guī)定初始值列表例沒有選項時，規(guī)定用INPUT語句或賦值語句創(chuàng)建的所有變量值從datainputid@@;ifid=1thentest='pass';ifid=2thentest='fail';122235153;procprintnoobs; datainputidifid=1thentest='pass';ifid=2thentest='fail';122235153;procprintnoobs;

122235153112223515311.1. 55 ARRAYarray-

規(guī)定數(shù)組中每一維的array

array下規(guī)定數(shù)組中每一維的array

由規(guī)定數(shù)組中變量的

arrayrain(*)xyzgreen,yellow. Arrayx{1:5,1:3}scorel-score15；ArrayC{3}Cl-ArrayA{*}_NUMERIC_;ArrayA{*}_CHARACTER_; dataa; arrayt(3)dataa; arrayt(3)_temporary_ Height F5F5F5F5F5F5FFFFFFarraytest(3)t1t2t3(9080arrayab(5)(54arrayrain{5}x1-x5;arrayab(*)xyz;arraytest(3)_temporary_(908070);arraydays{7}d1-d7;arrayx{2:5}greenreddenatoarraytest(3:4,3:7)test1-例通過下 datainputqa1-qa10qb1-arraytest{10}qa1-qa5qb1-qb5;puttest{4}=test{6}=;1 222222; qa4,數(shù)組元素test(6)引 datainputqa1-qa10qb1-arraytest{10}qa1-qa5qb1-puttest{4}=qa4=6NOTEWORK.NEW120NOTE:“DATA語句”所用時間（總處理時間CPU

0.010.01461arrayday(7)d1-d7;doi=2to4;doi=1to7by2;doi=1,3;arrayday(*)d1-d4;doi=1todim(day); Arrayyrs{76:85}year76-doi=76to85ifyrs(i)=9thenyrs(i)=.;doi=lbound(yrs)tohbound(yrs);ifyrs(i)=9thenyrs(i)=.;1. 1. $例datainputa1-4b6.;lengthdefault=3; 54456;3datatwo;setone;if 而數(shù)據(jù)集ONE中變量A按LENGTH語句規(guī)定只有3個字節(jié),因此A不會等于1..81字符變量的缺由它的第一個觀測值決定，這樣，例LENGTH語句必須放在INPUT語句前面才能起作用。dataa；lengthname$20;inputname$1-10;;例中，INPUT語句隱含指定變量NAME的長度為10。LENGTH語句例datainputxy1125ifx=1xy112515; 例dataa;datab; datac;setalengthlstknm$12;procprint; ’ 大datadatab; datac;lengthlstknm$12; /*length語句放在set*/setab;procprint;

ATTRIB語句永久地為變量規(guī)定了屬性。可以用FORMATattribscodeattribdateinformat=mmddyy.attribS1S2S3length=$4attribSlength=$4label='SCORE'dateFormat=worddate.label='TESTLABEL語句用于為變量加 。變量 是對變量的進 所用代碼|stockcodebyexchange" 代碼|stockcode"shrout="觀測到的b股股本|bsharesoutstanding";label 所用代碼|stockcodebylabel 代碼|stocklabelshrsdt="觀測日|sharesoutstandingobservationdate“;labelshrout="觀測到的b股股本|bsharesoutstanding";例刪除變量的 Labelscode=‘'hstocd=‘'shrsdt=‘'shrout=‘';dataa(keep=name procprintdata=a(obs=3); PROCSORT<option(s)><collating-sequence-BY<DESCENDING>variable-1<...<DESCENDING>variable-PROCSORT中必須使用BY語句，BY語句中可以規(guī)定任data procsortdata=aout=b;byyearqtrmonth;procsortdata=a600001out=a;bydescendingclpr;procprintdata=a(obs=3)noobs;vardateclpr;對class文件，新建數(shù)據(jù)集class_sort,按 PROCTRANSPOSE<DATA=input-data-<LABEL=label><NAME=name><OUT=output-data-set>BY<DESCENDING>variable-<...<DESCENDING>variable-COPYvariable(s);IDvariable;IDLABELvariable;VARvariable(s);VARvariable-IDproctransposedata=zhaibiaovarsale;idmonth;byid;proctransposevarmonth1-month6;byid;proctransposedata=duct_line_salesvar_2007q1_2007q2_2007q3proctransposedata=classout=alet;idname;procprint;proctransposedata=classout=alet;idprocFM123proctransposedata=classout=alet; procprint;FFM1F.2F.3F.4M.5M.6M. PROCCONTENTS輸入的邏輯庫成員，默認為SAS規(guī)定不輸出CONTENTS規(guī)定輸出的SAS規(guī)定變量列表按照它們在SASproccontentsdata=sashelp._all_;proccontentsdata=sashelp.class;例proccontentsdata=sashelp.class PROCCOPYOUT=libref-1IN=libref-<MOVE<ALTER=alter-password>>;EXCLUDESAS-file(s)</SELECTSAS-</移走IN=移走IN=規(guī)定被拷貝的規(guī)定被拷貝的SAS例兩個SAS邏輯庫之 proccopyin=lib1out=lib2MTYPE=(datacatalog);proccopyin=lib1out=lib2move; PROCDATASETS<COMMANDS><options>;

procdatasetslibrary=work;saveaa; procdatasetslibrary=work;deletebb;procdatasetslibrary=work Move將所有數(shù)據(jù)集移動

procdatasetsprocdatasetslibrary=work; proccopyin=lib1out=lib2

22PROCAPPENDBASE=SAS-data- >創(chuàng)建的SAS強迫PROCAPPENDprocappendbase=adata= SAS-data-set(s)可為一個或多個SASdata-set-options(s)指定需保留的變量（組）keep=變量（組）保留指定的變量（組drop=變量（組rename=

where=in=變量創(chuàng)建一個標識變量，如果當前觀測數(shù)據(jù)某數(shù)據(jù)集，標識為1，否firstobs=常數(shù)如常數(shù)=2，表示從第2個觀測開 obs=常數(shù)如常數(shù)=20，表示讀到的最后一個觀測是第20Nobs=變量把一個setPoint=變量觀 End=變量每一個set語句后數(shù)據(jù)集文件結(jié)束的標志，結(jié)束為1,否Key=索引名創(chuàng)建新自動變量_iorc_,顯示最近的I/O操作序號，Key=unique 1122SAS-data-set-2<(data-set-options)> 第9章數(shù)據(jù)展現(xiàn) PROCPRINT VAR

procprintdata=sashelp.class; printdata=sashelp.classnoobs;PROCPRINTdata=SASHELP.CLASS;VARNAMEHEIGHT;PROCPRINTdata=SASHELP.CLASS(OBS=3)NOOBS;VARHEIGHTWEIGHT;IDNAME;例optionsnodatepageno=1linesize=70pagesize=60;procprintdata=sashelp.classdouble;varnameheightweight;procsort procprintdata=class_by;sumheight; 例sum的用法procprintvarnameheight PROCTABULATE classvartable命令用來定義表格的形式。(1)按照：頁變量，行變量，列變PROC VARheightTABLESage*(heightweight)*(n proctabulatedata=sashelp.class;classage;vartablesage,*height*(nmean);proctabulate varheight;tables ,height*(nproctabulate varheight;tables ,height*(n PROCREPORTdata=SAS-data-setoptions;COLUMNSvariable_1….variable_n;DEFINEvariable_1;DEFINECOMPUTEblocksBREAK…;RBREAK…;breakrbreakoptionsnowindowsheadlineprocreportdata=sashelp.classnowindowsheadlinecolumnname height,(meann)weight,(meann); breakafter /summarizeolskip;rbreakafter/summarizeolskip;1 2IFexpressionTHEN<ELSE3例DATASETIFMARRIED=.ORGENDER=“”THENDELETE;IFGENDER=“M”THENX=1;ELSEX=2;例嵌套的IFDATAB2;SETA;IFAGE<30THENELSEIFAGE<50THENX=2;ELSEX=3;4 5WHEN(when-expression-1<,…when-expression-n>)<…WHEN(when-expression-1<,…when-expression-n>)<OTHERWISEstatement;>

6 data select(obs);when(1)whenwhen(3,5,7,13,15,17)

7例沒有SELECT表達式的SELECTdata_null_;when(monin('jun','jul','aug'))put'summer'when(monin('mar','apr','may'))put'spring'mon=;otherwiseput'fallorwinter'mon=;8例when(x=2)puttwo';/*錯誤用法when(2)put'two';

when(x=2)put'two';

9 DOUNTIL語句dataa; =“F”thendo; =“F”then =“F”thenStart<TOstop><BY規(guī)定下標變量的起始值To規(guī)定下標變量的終止值Increment規(guī)定步長While表達式在每次循環(huán)執(zhí)行前計算Until表達式在每次循環(huán)執(zhí)行以后計算例doI=1to1000;doI=1toy+3;doI=1toexit;例dodoi=1tondoi=nto1by–1;doi=k+1ton-1;doi=1tok-1,k+1ton;doi=2,3,5,7,11,13,17;doi=0.1to0.9by0.1,1to10by1,20to100by10;doi='saturday','sunday';doi='01jan99'd,doi='01jan99'dto'01jan2000'dbydoi=1to10doi=2to20by2doi=10to0by–1datadon=1to100;dataa;don=1to100;DOWHILE選項expressionDO組內(nèi)的執(zhí)行dataa;n=0;DOUNTILdataa;doDOOVER158<=HEIGHT23

11.311.3

x=1 i

ns2= (x-x)2

n-

i

s n(x-2in (xi

sm

CV=s·100x SK

nx-x(n-1)(n-2)i=1 n(n

nx-x

3(n-K (n-1)(n-2)(n-3)i=1

(n-2)(n-VAR變量名列表;BY分組變量名;量NSTDDEV|procmeansdata=sashelp.classmaxmin; varHeightoutputout=class_1mean(heightweight)=hei_meanwei_meanmax=h_maxw_max;procsortdata=sashelp.class procmeansdata=classmax varHeightoutputout=class_1mean(heightweight)=hei_meanwei_meanmax=h_maxVAR變量名列表;BY分組變量名;例procunivariatevarHeightoutputout=rmean=hmeanwmeanq1=h_q1w_q1;11.211.311.3PROCFREQDATA=<選項列表BY分組變量名;TABLES<選項列表OUTPUTOUT=輸出文件名稱 <選項列表>;By指定分組變量，對byTableS用來定義頻數(shù)表或交叉表，可以有多個tables語句。當頻 是命令為tablesJOB1，生成一維表；而命令為：tablesJOB1*good_bad；變Tables后的選項為norownocolnopercent：若只需要頻數(shù)，不需要各例procfreq norownocolnopercent：有時我們只需要頻數(shù)，不需要各行各列的百分比，例procfreq *age/nocolnorow

procvalueprocfreq *height/nocolnorownopercent;formatheightheight_ctg.;11.211.311.3PROCGPLOTDATA=數(shù)據(jù)集名PLOT縱軸變量名*橫軸變量名<=第三個變量名></選項>;SYMBOL<選項>;AXIS<選項SYMBOL語句為可選語句，procgplotplot圖（dot）等等。的確很強大，但是就是各種設(shè)置。比如colour：optionscolor=PROCSGPLOT<BANDX=variable|Y=UPPER=numeric-value|numeric-variableLOWER=numeric-|numeric-BUBBLEX=variableY=variableSIZE=numeric-variable</DENSITYresponse-variableDOTcategory-variableprocsgplotdata=sashelp.class;scatterx=heighty=weight/group= procsgplotdata=sashelp.stocks(where=(date>="01jan2000"dandstock="IBM"));title"StockTrend";seriesx=datey=close;seriesx=datey=low;seriesx=datey=high;procsgplottitle'Average hbartype/response=mpg_highwaystat=mean;procsgplotdata=sashelp.cars;title'AverageHighway vbartype/response=mpg_highwaystat=mean;procsgplotdata=sashelp.class;title'DistributionofWeight';histogramweight/showbins;procsgplotbubblex=heighty=weightsize=age/group=attrid=Alineattrs=(color=black);procsgplottitle"CholesterolDistributionbyWeightClass";hboxcholesterol/category=weight_status; 333PROCPROCGCHARTDATA=數(shù)據(jù)集名procgchartvbarheight/type=freqspace=0;第12ODS ODS即outputdeliverysystem),最主要功能是控制SAS的輸 RTFODSODSODSODSODSODSODSODSODS ODS打開LISTING目標：ODSLISTING關(guān)閉LISTING目標：ODSLISTING例PROCPRINT

例ODSPROCPRINTDATA=A;ODS 論壇ODSODSHTMLbody='HTML-file-specification'<options>;ODSHTMLCLOSE;例輸出一個HTMLODSLISTINGODSHTMLbody=“d:\study\html1_means.html”;PROCMEANSDATA=SASHELP.CLASS;VARAGE;ODSHTMLCOLSE;ODSLISTING;例輸出一個HTMLODSLISTINGODSHTMLbody=“d:\study\html1_print.html”;PROCPRINTDATA=SASHELP.CLASS;ODSHTMLCOLSE;ODSLISTING;ODSODS （1）ODSODSTRACEON</選項>；ODSTRACEOFF;其中：ODSTRACEON表示打開輸出對象的 ODSTRACEOFF表示關(guān)閉輸出對象的 ODSTRACEON;PROCUNIVARIATEVARHEIGHT;ODSTRACE例LABEL/LISTINGODSTRACEPROCUNIVARIATEDATA=SASHELP.CLASS;VARHEIGHT;ODSTRACE ODSLISTINGCLOSE;ODSOUTPUT ;PROCUNIVARIATEDATA=SASHELP.CLASS;VARHEIGHT;ODSODSODS<ODS-destination>SELECTODSLISTINGODSHTMLODSSELECTUNIVARIATE.HEIGHT. PROCUNIVARIATEDATA=SASHELP.CLASS;VARHEIGHT;ODSSELECTALL;ODSHTMLCLOSE;ODSLISITNG;ODSODSODS-destinationEXCLUDEexclusion(s)|ALL|NONE;ODSLISTINGODSHTMLODSEXCLUDEUNIVARIATE.HEIGHT. PROCUNIVARIATEDATA=SASHELP.CLASS;VARHEIGHT;ODSSELECTALL;ODSHTMLCLOSE;ODSLISITNG;

13.213.2 經(jīng)濟 經(jīng)濟PROCPROCREGDATA=<選項MODEL因變量名=</選項PLOT縱軸變量名*<=符號></選項>;BY分組變量名;13.113.1回歸診斷的主要任務(wù)是檢驗回歸假設(shè)是否成立，回歸模型的形式是否恰當，如PROCPROCREGDATA=<選項MODEL因變量名=自變量名列表</選項>;PLOT縱軸變量名*<=符號></選項>;

SQLSQL的英文全稱是StructuredQueryLanguage,翻譯成漢語空值(Null RUN語句在PROCSQL SELECT<DISTINCT>object-item<,...object-FROMfrom-<WHEREsql-<GROUPBYgroup-by-item<ORDERBYorder-by-itemCOLUMN-NAME=*(asterisk)select*fromfrom-listPROCSELECT*FROMSelectcolumn-namefromfrom-例PROCSELECTNAME,HEIGHTFROM 例PROCSELECTDISTINCTAGEFROMPROCSQLSELECT'初三二班',NAME,"的 是",AGEFROMCalculatingValues=CalculatingExpressionofPROCSELECTNAME,WEIGHT/HEIGHTASINDEFORMAT=5.3FROMSASHELP.CLASS; 并將Calculated放在列名稱之前，以此告知PROCSQL這個列是經(jīng)計PROCSELECTNAME,WEIGHT/HEIGHTASINDEFORMAT=5.3FROMSASHELP.CLASSWHERECALCULATEDINDEG1.4;CASE<case-WHENwhen-conditionTHENresult-<...WHENwhen-conditionTHENresult-例Case表達式, PROCSELECTNAME,AGE,WHENAGELE13THENWHENAGEGE14ANDAGELE15THENELSEENDASAGE_GROUPFROMSASHELP.CLASS;ORDERBYorder-by-item<ASC|DESC><,...order-by-PROCSELECT*FROMSASHELP.CLASSORDERBYHEIGHT;

PROCSELECT*FROMSASHELP.CLASSORDERBY4;WHEREsql-例用WHERE語句選 PROCSELECT*FROMSASHELP.CLASSWHEREAGELE12;PROCSELECT*FROMSASHELP.CLASSWHEREAGEIN(12,14);PROCSELECT*FROMSASHELP.CLASSWHEREAGEBETWEEN12AND14;PROCSELECT*FROMSASHELP.CLASSWHERENAMELIKE"%斯%";使用匯總函數(shù)（aggregatefunctionorsummaryPROCSELECTSUM(HEIGHT)ASSHFROMSelectcount(distinct<column-name>)as<newcolumn-proctitle'NumberoftotalRows';selectcount(*)asnumberfromNumberoftotalRowsNumberoftotalRows<GROUPBYgroup-by-<,...group-by-PROCSELECTAGE,AVG(HEIGHT)ASAVE_HEIGHTFROMSASHELP.CLASSGROUPBY<HAVINGsql-PROCSELECTAGE,AVG(HEIGHT)ASAVE_HEIGHTFROMSASHELP.CLASSGROUPBYHAVINGAVE_HEIGHTGE60;PROCSELECT*FROMWHERENAMEIN(SELECTNAMEFROMSCOREWHERESCOREGT80);按照age升序，height JOIN）（FULLJOIN）ProcSelectobject-itemfromtable-name<<AS>alias>,table-name<<AS>alias>Wheresql- 123

PROCSELECT*FROM Fromtable-name<as>table-alias,<othertable-namesprocselect*from asa,usaasbwherea.level=b.level; Fromtable-name<INNER>JOINtable-nameONsql-expressionINNERJOIN用于連接多個表的數(shù)據(jù)。 procselect*from ainnerjoinUSAbona.level=b.level;左連接：Fromtable-name LEFTJOIN右連接：Fromtable-name RIGHTJOIN全連接：Fromtable-name FULLJOINPROCSELECT*FROM LEFTJOINUSA 配的行，不匹配行中Tableusa的列為缺失值。PROCSELECT*FROM RIGHTJOINUSA 不匹配行中Table的列都是缺失值。PROCSELECT*FROM FULLJOINUSA 例BYTablecode

Table datamergeab;bycode;

procprintdata=merge1noobs;title'TableMERGE1';TableTableproctitle'Tableselecta.code,a.manager,b.Assitantfroma,b當用來連接兩個表的列變量或者BY組中有重復(fù)值時，Merge和Procsql的BYTablenewonecode

Table Table procprintdata=merge3noobs;title'TableMERGE3';ProcTitle'TableSelecta.code,a.manager,b.assistantFromafulljoinbTablecode TABLEXy1223

TABLExz124AUNIONAUNION title'AUNIONB';select*fromAselect*fromB;注意比較，UINONALL與UINONAEXCEPTAEXCEPTx3title'AEXCEPTB';select*fromAselect*fromB;AINTERSECTAINTERSECTxtitle'AINTERSECTselect*fromAselect*fromB;proctitle'AOUTERUNIONAOUTERUNIONxAOUTERUNIONx x1 22.2.3..1.2.4 16.2CREATETABLEtable-nameCREATETABLEtable-nameASquery-<ORDERBYorder-by-item<,...order-by-用LIKE創(chuàng)建表：CREATETABLEtable-nameLIKEtable-PROCCREATETABLE snochar(8)notnullsnamechar(10), char(2),sidchar(18),sageNUMPROCDESCRIBETABLE例用CreateTable語句從CLASSPROCCREATETABLECLASS_M SELECT*FROMSASHELP.CLASS 例用describeTablePROCDESCRIBETABLECLASS_M例用LIKEPROCCREATETABLECLASS_WLIKE例用describeTablePROCDESCRIBETABLEINSERTINTOtable-SETcolumn=sql-expression<,...column=sql-<SETcolumn=sql-expression<,...column=sql-例PROCINSERTINTOSETNAME=" ",AGE=14, SETNAME=" ",AGE=12, SELECT*FROMCLASS_W;INSERTINTOtable-nameVALUES(value<,...value>)<...VALUES(value<,...例用VALUESPROCINSERTINTOCLASS_W SELECT*FROMINSERTINTOtable-<Query PROCCREATETABLECLASS_FLIKEPROCSQL;INSERTINTOSELECT*FROMSASHELP.CLASS ="男AND13LEAGELE15;PORCUPDATEtable-SETcolumn=sql-expression<...column=sql-<SETcolumn=sql-expression<...column=sql-<WHEREsql- datainputstkcd$stknm$inv;000001S000002萬科 600601;

procupdateinvestsetinv=inv*1.1;DATAINVEST;SETINVEST;procupdateInvestsetinv=inv*1.5wherestkcd='000001’;updateInvestsetinv=inv*1.4wherestkcd='000002’;updateInvestsetinv=inv-10000wherestkcd='600601’;select*frominvest;procdeletefrominvestwherestkcd='600601';PROCSQL<option(s)>;ALTERTABLEtable-name<ADD<CONSTRAINT>c