asadv63題解析和機經(jīng)63題答案解析

1.Whenattemptingtominimizememory

usage,

themostefficientwaytodogroupprocessingwhenusingtheMEANSprocedureisto

use:

A.theBYstatement.

B.GROUPBYwiththeNOTSORTEDspecification.

C.theCLASSstatement.

D.multipleWHEREstatements.

ShapeoftheDataMostEfficientTools

arelativelysmallnumberofdistinctcombinationsofvalues?PROCMEANS(orPROC

oftheclassvariable-thesummarizeddatacontains10%SUMMARY)

orfeweroftheobservationsintheoriginaldataset?PROCREPORT

?PROCTABULATE

arelativelylargenumberofdistinctcombinationsofvalues?PROCSQL

oftheclassvariable-thesummarizeddatacontainsmore?DATAstepandPROCSORT

than10%oftheobservationsintheoriginaldataset

由UsingaGROUPBYstatementin

PROCSQLissimilarinCPUusageto

aPROCSORTst叩followedbya

DATAstep.

ComparingResourceUsageacrossThreeTechniquesforUsingPROCMEANS

Thethreetechniquesforsummarizingdataforspecificcombinationsofclass

variables(allbutthebasicPROCMEANSstep)differinresourceusageasfollows:

?TheTYPESstatementinaPROCMEANSstepusesthefewestresources.

?AprogramthatcontainstheNWAYoptioninmultiplePROCMEANSstepsuses

themostresourcesbecauseSASmustreadthedatasetseparatelyforeachPROC

MEANSstep.

?TheWHERE=datasetoptioninaPROCMEANSstepusesmoreresourcesthanthe

TYPESstatementinPROCMEANSbecauseSASmustcalculateallpossible

combinationsofclassvariablesbeforesubsetting.However,theWHERE=data

setoptioninPROCMEANSusesfewerresourcesthantheNWAYoptioninmultiple

PROCMEANSsteps.

2.TheSASdatasetWORK.CHECKhasa

variablenamedId_Codeinit.

WhichSQLstatementwouldcreateanindexonthisvariable?

A.createindexIdCodeonWORK.CHECK;

B.createindex(ld_Code)onWORK.CHECK;

C.makeindex=IdCodefromWORK.CHECK;

1).defineindex(Id_Code)inWORK.CHECK;

關于在SQL語句中創(chuàng)建INDEX的語句格式如下：

Generalform,CREATEINDEXstatement:

CREATE<UN1QUE>INDEXindex-nameONtable-name(column-name-l<,,,,column-name-n>);

示例語句：

/*creatingasimpleindex*/

datacrackman;

inputid_codename$@;

datalines;

1java

2c++

3dephi

4asp

5c#.net

r

procsql;

createindexid_codeonwork.crackman;

quit;

log提示簡單索引id_code已經(jīng)建立

或者使用：

procsql;

createindexidcodeonwork.crackman(id_code);

quit;

之所以括號內(nèi)的column-name是可選項，是應為在建立Simpleindex時，

index-name必須與column-name是同一個名字。

datacrackman;

inputid_codename

datalines;

1java

2c++

3dephi

4asp

4c#.net

r

procsql;

createuniqueindexid_codeonwork.crackman;

quit;

LOG:提示ERROR:文件CRACKMAN的索弓Iid_code不允許使用重復的值。

/*dropingindexse*/

procsql;

dropindexdaily

fromwork.marchflights;

quit;

3.GiventheSASdatasets:

WORK.EMPLOYEEWORK.NEWEMPLOYEE

NameDeptNamesSalary

AlanSalesMichelle50000

MichelleSalesParesh60000

ASASprogramissubmittedand

thefollowingiswrittentotheSASlog:

101procsql;

102selectdept,name

103fromWORK.EMPLOYEE

104wherename=(selectnamesfromnewemployeewheresalary>40000)

ERROR:Subqueryevaluatedtomorethanonerow.

105;

106quit;

Whatwouldallowtheprogramto

successfullyexecutewithouterrors?

A.Replacethewhereclausewith:

whereEMPLOYEE.Name=(selectNamesdelimitedwith

fromWORK.NEWEMPLOYEE

whereSalary>40000);

B.Replaceline104with:

whereEMPLOYEE.Name=ANY(selectNamesseparatedwith

fromWORK.NEWEMPLOYEE

whereSalary>40000);

C.ReplacetheequalsignwiththeINoperator.

D.Qualifythecolumnnameswiththetablenames.

首先分析這個原程序第104行，中wherename=(selectnamesfromnewemployeewhere

salsry>40000)

原程序在執(zhí)行過程，應先是執(zhí)行子查詢，selectnamesfromnewemployeewhere

salsry>40000,將返回的結果作為下一個查詢的篩選條件。

在這里返回的結果NAMES這個變量包括了兩個值Michelle和Paresh,所以在執(zhí)行過程出現(xiàn)

錯誤。那么應該改成IN或者ANYoINANY指定的是一個匹配范圍，而等號必須是?個具體

匹配的值。

這里可以思考SEPARATEDWITH這個語句，SAS認為是錯誤。

4.GiventheSASdataset

SASUSER.HIGHWAY:

SteeringSeatbeltSpeedStatusCount

absentNo0-29serious31

absentNo0-29not1419

absentNo30-49serious191

absentno30-49not2004

absentno50+serious216

ThefollowingSASprogramissubmitted:

procsqlnoprint;

selectdistinct

Speed[insertSQLclause]

fromSASUSER.HIGHWAY;

quit;

titlel〃Speedvaluesrepresentedare:&GROUPS”;

procprintdata=SASUSER.HIGHWAY;

run;

WhichSQLclausestoresthetext0-29,30-49,50+in

themacrovariableGROUPS?

A.into&GROUPS

B.into:GROUPS

C.into:GROUPSseparatedby','

D.into&GROUPSseparatedby','

此題考察的是SQL與宏的結合。

用SQL語句產(chǎn)生宏變量，語法結構如下：

PROCSQLNOPRINT;

SELECTcolumnl<,column2,...>

INTO:macro-variable-1<,:macro-variable-2,...>

FROMtable-1|view-1

<WHEREexpression>

<otherclauses>;

QUIT;

下面這個語法結構是將一變量的多個值組合在一個宏變量里面，例如：

Generalform,SELECTstatementwithINTOclausefbrcombiningvaluesintoone

macrovariable:

PROCSQLNOPRINT;

SELECTcolumn!

INTO:macro-variable-1

SEPARATEDBY'delimited'

FROMtable-1|view-1

<WHEREexpression>

<otherclauses>;

QUIT;

/*示例程序：*/

datacrackman;

inputSteering$Seatbelt$Speed$Status$Count@;

datalines;

absentNo0-29serious31

absentNo0-29not1419

absentNo30-49serious191

absentno30-49not2004

absentno50+serious216

run;

procsql;

11

selectdistinctspeedinto:groupsseparatedbyzfrom

crackman;

quit;

這里創(chuàng)建的宏變量是全局宏變量

/*Supposeyouwanttocreaterangesofmacrovariablesthat

containthecoursecode,location,andstartingdateofall

coursesthatarescheduledin2002.*/

procsqlnoprint;

selectcount(*)into:numrows

fromsasuser.schedule

whereyear(begindate)=2002;

%letnumrows=&numrows；

%putThereare&numrowscoursesin2002；

selectcourse_codezlocation,begindateformat=mmddyylO.

into:crsidl-:crsid&numrows,

splacel-:place&numrowsz

:datel-:date&numrows

fromsasuser.schedule

whereyear(begindate)=2002

orderbybegindate;

%put_user__；

quit;

5.TheSASdatasetWORK.CHECKhasan

indexonthevariableCodeandthefollowing

SASprogramissubmitted.

procsortdata=W0RK.CHECK;

byCode;

run;

Whichdescribestheresultofsubmitting

theSASprogram?

A.TheindexonCodeisdeleted.

B.TheindexonCodeisupdated.

C.TheindexonCodeisuneffected.

D.Thesortdoesnotexecute.

SORT過程是不能對已經(jīng)建立索引INDEX的數(shù)據(jù)集進行SORT或者REPLACE,因為那樣會破壞數(shù)

據(jù)集的INDEX文件。

當然如果非要排序，破壞INDEX文件，可以再后面加入一個參數(shù)force。

6.ThetableWORK.PILOTScontainsthe

followingdata:

WORK.PILOTS

IdNameJobcodeSalary

001AlbertPT150000

002BrendaPT170000

003CarlPT160000

004DonnaPT280000

005EdwardPT290000

006FloraPT3100000

Thedatasetwassummarizedtoincludeaverage

salarybasedonjobcode:

JobcodeSalaryAvg

P/p>

P/p>

PT16000060000

PT28000085000

PT29000085000

PT3100000100000

WhichSQLstatementcould\0Tgenerate

thisresult?

A.select

Jobcode,

Salary,

avg(Salary)label=，Avg'

fromWORK.PILOTS

groupbyJobcode

orderbyId

B.select

Jobcode,

Salary,

(selectavg(Salary)

fromWORK.PILOTSasPl

wherePl.Jobcode=P2.Jobcode)asAvg

fromWORK.PILOTSasP2

orderbyId

C.select

Jobcode,

Salary,

(selectavg(Salary)

fromWORK.PILOTS

groupbyJobcode)asAvg

fromWORK.PILOTS

orderbyId

D.select

Jobcode,

Salary,

Avg

from

WORK.PILOTS,

(select

JobcodeasJc,

avg(Salary)asAvg

fromWORK.PILOTS

groupby1)

whereJobcode=Jc

orderbyId

DATAPILOTS;

inputIdName$Jobcode$Salary@@;

datalines;

001AlbertPT150000

002BrendaPT170000

003CarlPT160000

004DonnaPT280000

005EdwardPT290000

006FloraPT3100000

run;

/*選項A*/

procsql;

select

Jobcode,

Salaryz

avg(Salary)label='Avg1

fromWORK.PILOTS

groupbyJobcode

orderbyId;

quit;

/*NOTE:指定的查詢涉及按其SELECT子句中沒出現(xiàn)的一項排序。*

//NOTE：查詢要求將匯總統(tǒng)計量與原始的數(shù)據(jù)重新合并?？梢赃\行出結果，sas沒有報

錯★/

/*選項B*/

procsql;

select

Jobcode,

Salaryz

(selectavg(Salary)

fromWORK.PILOTSasPl

wherePl.Jobcode=P2.Jobcode)asAvg

fromWORK.PILOTSasP2

orderbyId;

quit;

*這是一個correctedsubqueriesz

Correlatedsubqueriescannotbeevaluatedindependentlyzbut

dependonthevalues

passedtothembytheouterqueryfortheirresults.；

/*選項c*/

procsql;

select

Jobcodez

Salary,

(selectavg(Salary)

fromWORK.PILOTS

groupbyJobcode)asAvg

fromWORK.PILOTS

orderbyId;

quit;

/*運行不出結果，因為在括號中是multiple-valuenoncorrectedsuqueries,

Somesubqueriesaremultiple-valuesubqueries：theyreturnmorethan

onevalue(row)totheouterquery.

Ifyournoncorrelatedsubquerymightreturnavalueformorethanone

row,buttheWHEREorHAVINGclauseintheouterquery

containsanoperatorotherthanoneoftheoperatorsthatarespecified

above,thequerywillfail.Anerrormessage

isdisplayedintheSASlog,whichindicatesthatthesubqueryevaluated

tomorethanonerow.*/

/*選項d*/

procsql;

select

Jobcodez

Salary,

Avg

from

WORK.PILOTS,

(select

JobcodeasJcz

avg(Salary)asAvg

fromWORK.PILOTS

groupby1)

whereJobcode=Jc

orderbyId;

quit;

/*括號內(nèi)的子查詢已經(jīng)得出一個各個jobcode的均值，形成一個view,通過where語

句進行match,最后可以得出結果//

7.Aquickruleofthumbforthespace

requiredtorunPROCSORTis:

A.twotimesthesizeoftheSASdatasetbeingsorted.

B.threetimesthesizeoftheSASdatasetbeingsorted.

C.fourtimesthesizeoftheSASdatasetbeingsorted.

D.fivetimesthesizeoftheSASdatasetbeingsorted.

這道題有爭議，sasprepguide里面的原話是這樣的：

TheSORTprocedureisonetechniquethatcanbeusedtogroupororderdata.

However,theSORTprocedurecanuseahighvolumeofresources.Whenanuncompressed

datafileissortedusingtheSORTprocedure,SASrequiresenoughspaceinthedata

libraryfortwocopiesofthedatafile,plusaworkspacethatisapproximatelytwo

tofourtimesthesizeofthedatafile.

However,Themultiplier4appliesonlytoutilityfilesusedinreleasesprior

toSAS9,whenPROCSORTneedstousediskspaceinordertosortthedata.For

in-memorysortingandsortingwithSAS9andlater,themultiplieris2orless.

ThreadedprocessingtakesadvantageofmultipleCPUsbyexecutingmultiple

threadsinparallel(parallelprocessing).Threadedproceduresarecompletedinless

realtimethanifeachtaskwerehandledsequentially,althoughtheCPUtimeis

generallyincreased.

Whenathreadedsortisused,theobservationsintheinputdatasetaredivided

intoequaltemporarysubsets,basedonhowmanyprocessorsareallocatedtotheSORT

procedure.Eachsubsetisthensortedonadifferentprocessor.Thesortedsubsets

aretheninterleavedtorecreatethesortedversionoftheinputdataset.

所以最后選A兩倍。

8.Multi-threadedprocessingforPROC

SORTwilleffectwhichofthesesystem

resources?

A.CPUtimewilldecrease,wallclocktimewilldecrease

B.CPUtimewillincrease,wallclocktimewilldecrease

C.CPUtimewilldecrease,wallclocktimewillincrease

1).CPUtimewillincrease,wallclocktimewillincrease

Thread-enabledproceduresarecompletedinsubstantiallylessrealtimethanifeach

taskishandledsequentially,buttheCPUtimeisgenerallyincreased.

ThreadedsortingisenabledordisabledbyusingtheSASsystemoptionTHREADS|

NOTHREADSortheTHREADS|NOTHREADSprocedureoption.

Generalform,SORTprocedurewiththeTHREADS|NOTHREADSoption:

PROCSORTSAS-data-set-nameTHREADS|NOTHREADS;

9.GiventheSASdataset

WORK.TRANSACT:

RepCostShip

SMITH20050

SMITH40020

JONES10010

SMITH600100

JONES1005

Thefollowingoutputisdesired:

Rep

JONES105

SMITH250

WhichSQLstatementwasused?

A.select

rep,

min(Cost+Ship)

fromWORK.TRANSACT

orderbyRep

B.select

Rep,

min(Cost,Ship)asMin

fromWORK.TRANSACT

summarybyRep

orderbyRep

C.select

Rep,

min(Cost,Ship)

fromWORK.TRANSACT

groupbyRep

orderbyRep

D.select

Rep,

min(Cost+Ship)

fromWORK.TRANSACT

groupbyRep

orderbyRep

/*示例程序：*/

dataTRANSACT;

inputRep$CostShip@;

datalines;

SMITH20050

SMITH40020

JONES10010

SMITH600100

JONES1005

run;

/★選項A★/

procsql;

selectrepfmin(Cost+Ship)

fromWORK.TRANSACTorderbyRep;

quit;

/*

JONES105

JONES105

SMITH105

SMITH105

SMITH105

得出的結果應該是沒有分組比較的結果，就是自身比較，所以會得到5個結果，沒有分組,

而且M1N的值都為105*/

/*選項B*/

procsql;

selectRep,min(CostrShip)asMin

fromWORK.TRANSACT

summarybyReporderbyRep;

quit;

/*ERROR22-322:語法錯誤，期望下列之一：,3,GROUP.*/

/*選項C*/

procsql;

selectRepzmin(CostzShip)

fromWORK.TRANSACT

groupbyReporderbyRep;quit;

/*Rep

JONES10

JONES5

SMITH50

SMITH20

SMITH100

得到是在一個REP上的這兩個變量COSTSHIP之間的小值。*

/*選項D*/

procsql;

selectRep,min(Cost+Ship)

fromWORK.TRANSACT

groupbyReporderbyRep

r

quit;

/*因為在要得到的數(shù)據(jù)集中有一個是沒有命名的，所以在

中MIN(COST+SH工P)是沒有命名的變量的值。*/

變形題：

GiventheSASdatasetWORK.TRANSACT:

RepCostShip

SMITH20050

SMITH40020

JONES10010

SMITH600100

JONES1005

Thefollowingoutputisdesired:

Rep

JONES105

JONES105

SMITH105

SMITH105

SMITH105

WhichSQLstatementwasused?

A.

selectRep,min(Cost+Ship)

fromWORK.TRANSACT

orderbyRep

B.

selectRep,min(Cost,Ship)asMin

fromWORK.TRANSACT

summarybyRep

orderbyRep

C.

selectRep,min(Cost,Ship)

fromWORK.TRANSACT

groupbyRep

orderbyRep

D.

selectRep,min(Cost+Ship)

fromWORK.TRANSACT

groupbyRep

orderbyRep

Answer:A

10.ThefollowingSASprogramis

submitted:

%letValue=9;

%letAdd=5;

%letNewval=%eval(&Value/&Add);

%put&Newval;

Whatisthevalueofthemacrovariable

Newvalwhenthe%PUTstatementexecutes?

A.0.555

B.2

C.1.8

D.1

%PUTstatementshowsthat%EVALdiscardsthefractionalpart(小數(shù)部分)，whenit

performsdivisiononintegersthatwouldresultinafraction:

如果要獲得準確的結果應該選擇函數(shù)%sysevalf替換%eval.

the%EVALfunctiongeneratesERRORmessagesinthelogwhenitencountersan

expressionthatcontainsnon-integervalues.InordertoavoidtheseERRORmessages,

youcanusethe%SYSEVALFfunction.The%SYSEVALFfunctionevaluatesarithmeticand

logicalexpressionsusingfloating-pointarithmetic.

11.ThefollowingSAScodeissubmitted:

dataWORK.TEMPWORK.ERRORS/view=W0RK.TEMP;

infileRAWDATA;

inputXaXbXc;

ifXa=.thenoutputWORK.ERRORS;

elseoutputWORK.TEMP;

run;

WhichofthefollowingistrueoftheWORK.ERRORSdataset?

A.ThedatasetiscreatedwhentheDATAstepissubmitted.

B.ThedatasetiscreatedwhentheviewTEMPisusedinanotherSASstep.

C.ThedatasetisnotcreatedbecausetheDATAstatementcontainsasyntaxerror.

I).ThedescriptorportionofWORK.ERRORSiscreatedwhentheDATAstepissubmitted.

UsingSASDATAStepViewstoConserveDataStorageSpace(continued)

DATAStepViews

ADATAstepviewcontainsapartiallycompiledDATAstepprogramthatcanreaddatafromavarietyofsources,including

?rawdatafiles

?SASdatafiles

?PROCSQLviews

?SAS/ACCESSviews

?DB2,ORACLE,orotherDBMSdata.

ADATAstepviewcanbecreatedonlyinaDATAstepADATAstepviewcannotcontainglobalstatements,host-specificdatasetoptions,ormosthost-

specificFILEandINF1LEstatements.Also,aDATAstepviewcannotbeindexedorcompressed.

YoucanuseDATAstepviewsto

?alwaysaccessthemostcurrentdatainchangingfiles

?avoidstoringacopyofalargedatafile

?combinedatafrommultiplesources

Thecompiledcodedoesnottakeupmuchroomforstorage,soyoucancreateDATAstepviewstoconsen/ediskspace.Ontheotherhand,useofDATA

stepviewscanincreaseCPUusagebecauseSASmustexecutethestoredDATAstepprogrameachtimeyouusetheview

Generalform,DATAsteptocreateaDATAstepview:

DATASAS-data-view<SAS-data-file-1...SASdata-file-n>I

VIEW-SAS-data-v/etv;

<SASstatements〉

RUN;

where

?SAS-data-viewnamesthedataviewtobecreated

?SAS-data-file-1SAS-data-file-nisanoptionallistthatnamesanydatafilestobecreated

?SASstatementsincludesotherDATAstepsyntaxtocreatethedataviewandanydatafilesthatarelistedintheDATAstatement

TheVIEW=optiontellsSAStocompile,butnottoexecute,thesourceprogramandtostorethecompiledcodeintheinputDATAstepviewthatisnamedinthe

option.

回IfyouspecifyadditionaldatafilesintheDATAstatement,SAScreatesthesedatafileswhentheviewisprocessedinasubsequentDATAorPROCstep.

Therefore,youneedtoreferencethedataviewbeforeyouattempttoreferencethedatafileinlatersteps.

Example

ThefollowingprogramcreatesaDATAstepviewnamedCompany.NewdatathatreadsfromthefilereferencedbythefilerefintheINFILEstatement

datacoiqsany.newdata/view=company.newdata;

infile<fileref>;

<DATAszepscatezencs>

run;

所以這里sasstatement并沒有語法錯誤，更為準確的答案是B

12.Whichtitlestatementwouldalwaysdisplaythecurrentdate?

A.titleTodayis:&sysdate.〃；

B.title"Todayis:&sysdate9.〃；

C.title"Todayis:&today.〃；

D.titleTodayis:%sysfunc(today(),worddate.);

A選項和B選項輸出的是sassessionstarted的時間或者日期

SYSDATE9representsthedateonwhichtheSASsessionstarted,andSYSTIMErepresents

thetimeatwhichtheSASsessionstarted.Supposeyouwouldratherseethedate

insomeotherformat,orsupposeyouwouldratherseethecurrentdateortime.You

canusethe%SYSFUNCfunctiontoexecuteotherSASfunctionsaspartofthemacro

facility.

Generalform,%SYSFUNCfunction:

%SYSFUNC(function(argument(s))<.formats)

where

?functionisthenameoftheSASfunctiontoexecute.

?argument(s)isoneormoreargumentsthatareusedbyfunction.Usecommastoseparateallarguments.Anargumentcanbea

macrovariablereferenceoratextexpressionthatproducesargumentsforafunction.

?formatisanoptionalformattoapplytotheresultoffunction.Bydefault,numericresultsareconvertedtoacharacterstring

usingtheBEST12.format,andcharacterresultsareusedastheyare.withoutformattingortranslation.

舉個例子：

Supposeyouwanttocreateareporttitlethatincludesthecurrentdatein

WORDDATE,format.Youcouldusethisstatement:

title"ReportProducedon%sysfunc(today(),worddate.)"；

However,thatwouldresultinthefollowingtitle:

ReportProducedonJune7,2002

Youcanmaskthecommabyusingthe%QSYSFUNCfunctioninstead,asfollows:

title"ReportProducedon

%sysfunc(left(%qsysfunc(today(),worddate.)))"；

Themodifiedstatementgeneratesthefollowingtitle:

ReportProducedonJune7,2002(去空)

13.GiventheSASdatasets:

WORK.ONEWORK.TWO

IdNameIdSalary

112Smith243150000

243Wei35545000

457Jones52375000

ThefollowingSASprogramissubmitted:

dataWORK.COMBINE;

mergeWORK.ONEWORK.TWO;

byId;

run;

WhichSQLprocedurestatementproducesthesameresults?

A.createtableWORK.COMBINEas

select

Id,

Name,

Salary

from

WORK.ONE

fulljoin

WORK.TWO

onONE.Id=TWO.Id

B.createtableWORK.COMBINEas

select

coalesce(ONE.Id,TWO.Id)asId,

Name,

Salary

from

WORK.ONE,

WORK.TWO

whereONE.ld=TWO.Id

C.createtableWORK.COMBINEas

select

coalesce(ONE.Id,TWO.Id)asId,

Name,

Salary

from

WORK.ONE

fulljoin

WORK.TWO

onONE.Id=TWO.Id

orderbyId

D.createtableWORK.COMBINEas

select

coalesce(ONE.Id,TWO.Id)asId,

Name,

Salary

from

WORK.ONE,

WORK.TWO

whereONE.Id=TWO.Id

orderbyONE.Id

下面對四個答案進行解析。

A:

犯了一個明顯的錯誤，就是ID這個變量引用不明確。所以運行不出結果來；

B:

procsql;

createtableWORK.COMBINEas

select

coalesce(ONE.IdzTWO.Id)asIdf

Name,

Salary

from

WORK.ONE,

WORK.TWO

whereONE.Id=TWO.Id

r

quit;

procprint;run;

*WhenyouaddtheCOALESCEfunctiontotheSELECTclauseofthePROC

SQLouterjoin,

thePROCSQLouterjoincanproducethesameresultasaDATAstep

match-merge.；

/*返回只有一個觀測，因為ONE和TWO的連接按照where篩選條件進行了篩選，結果只有一

條符合條件。*/

C

procsql;

createtableWORK.COMBINEas

select

coalesce(ONE.Id,TWO.Id)asIdf

Name,

Salary

from

WORK.ONE

fulljoin

WORK.TWO

onONE.Id=TWO.Id

orderbyId

r

quit;

/*首先執(zhí)行的是按照ON條件執(zhí)行FULLJOIN鏈接，此時如果不用COALESCE,數(shù)據(jù)集會

有兩個ID,一個是ONE?個是TWO的，

如果ONE和TWO的工D不匹配，那么想要的工D就為缺失值。

那么COALESCE函數(shù)就是合并這兩個工D,然后重復的合并在一起，剔除缺失值，所以結

果就和MERGE一致。*/

D；

錯誤和B一樣，連接方式不是FULLJOIN,而是innerjoin。

看mergeSQL在橫向水平上合并數(shù)據(jù)集的區(qū)別

datacrackmanl(index=(x));

inputxy;

datalines;

12

34

56

/

run;

datacrackman2(index=(x));

inputxz;

datalines;

18

39

57

/

run;

datacrackman;

mergecrackmanlcrackman2;

byx;

run;

procsql;

createtablecrackmanasselecta.xra.yfb.z

fromcrackmanla,crackman2bwherea.x=b.x;

quit;

兩種方法都可以實現(xiàn)既定的目標

但是各有什么優(yōu)缺點呢？

MERGE：

優(yōu)點：

1.除非內(nèi)存限制，否則對要合并的數(shù)據(jù)集的觀測總數(shù)是沒有限制的

2.可以通過DATASTEP里面的DOLOOP和ARRAY以及其他一些MERGE的功能來實現(xiàn)比較復雜

的邏輯合并

3.可以通過對要合并數(shù)據(jù)集的多個變量的比較進行合并

缺點：

1.要合并的數(shù)據(jù)集必須先排序或者建立索引

2.根據(jù)多個變量或者一個變量的比較進行數(shù)據(jù)集合并，那么前提是要合并的數(shù)據(jù)集必須包括

要比較的變量。

3.按照指定的變量進行嚴格比較，獲得所需要的結果，而且必須至少有一個觀測是匹配成功

的。

因為MERGE在DATASTEP里面，在匹配的過程中，如果匹配成功之后，不會再重復讀取已經(jīng)

匹配成功的數(shù)據(jù)觀測，例如：如果crackman1的第一個觀測和crackman2的第一個觀測匹配

成功了，那么在進行第二個觀測匹配時，就不會再去讀取第一個觀測的數(shù)據(jù)，所以效率會高

些。但是SQL不是這樣的，SQL是多對多的掃描匹配，產(chǎn)生一個cartesianproduct,笛卡

爾乘積，所以相對MERGE顯得速度和效率要慢一些

SQL：

優(yōu)點：

L數(shù)據(jù)集不需要排序或者建立索引，當然如果建立索引更好

2.多個數(shù)據(jù)集可以