SASAdv12月真题-63题

1.Whenattemptingtominimizememoryusage,themostefficientwaytodogroupprocessingwhenusingtheMEANSprocedureistouse:A.theBYstatement.B.GROUPBYwiththeNOTSORTEDspecification.C.theCLASSstatement.D.multipleWHEREstatements.(Thequestionisprettyclear-cut,IMO.Italludestothestandardlong-beardedpracticaltrickusedinthesituationwhenMEANS/SUMMARYwithCLASSusestoomuchmemory.Itdoesnotaskwhatismoreefficientingeneral(whichwouldbetousesomethingelseinsteadofMEANSifpossible),justwhatleadstotheminimalmemoryfootprint,andtheanswerisA.(AnswerBisjustadistraction-acommondeviceincomputer-adaptivetests).ThereasonisthatwithoutBY,MEANScreatesandbuildsitsAVLtreefortheentirefileandallcategoricalvaluescrossingsfoundinCLASS.WithBY,theprocbuildsthetreeforthecurrentBYgrouponly,computesthestats,thenerasesthetreebeforethenextBYgroupstarts,createsandbuildsitagain,andsoon.Theerase/createprocessisnotazerocostone,sothesmallerthemorenumeroustheBYgroupsare,themoreitslowsdoestheprocessing;however,thesmalleristhelargestBYgroup,thesmalleristhememoryfootprint.)2.TheSASdatasetWORK.CHECKhasavariablenamedId_Codeinit.WhichSQLstatementwouldcreateanindexonthisvariable?A.createindexId_CodeonWORK.CHECK;B.createindex(Id_Code)onWORK.CHECK;C.makeindex=Id_CodefromWORK.CHECK;D.defineindex(Id_Code)inWORK.CHECK;3.GiventheSASdatasets:WORK.EMPLOYEEWORK.NEWEMPLOYEENameDeptNamesSalary---------------------------AlanSalesMichelle50000MichelleSalesParesh60000ASASprogramissubmittedandthefollowingiswrittentotheSASlog:101procsql;102selectdept,name103fromWORK.EMPLOYEE104wherename=(selectnamesfromnewemployeewheresalary40000)ERROR:Subqueryevaluatedtomorethanonerow.105;106quit;Whatwouldallowtheprogramtosuccessfullyexecutewithouterrors?A.Replacethewhereclausewith:whereEMPLOYEE.Name=(selectNamesdelimitedwith','fromWORK.NEWEMPLOYEEwhereSalary40000);B.Replaceline104with:whereEMPLOYEE.Name=ANY(selectNamesseparatedwith','fromWORK.NEWEMPLOYEEwhereSalary40000);（any表示要有多个值，而=只能要求一个值。）C.ReplacetheequalsignwiththeINoperator.D.Qualifythecolumnnameswiththetablenames.4.GiventheSASdatasetSASUSER.HIGHWAY:SteeringSeatbeltSpeedStatusCount---------------------------------absentNo0-29serious31absentNo0-29not1419absentNo30-49serious191absentno30-49not2004absentno50+serious216ThefollowingSASprogramissubmitted:procsqlnoprint;selectdistinctSpeed[_insert_SQL_clause_]fromSASUSER.HIGHWAY;quit;title1Speedvaluesrepresentedare:&GROUPS;procprintdata=SASUSER.HIGHWAY;run;WhichSQLclausestoresthetext0-29,30-49,50+inthemacrovariableGROUPS?A.into&GROUPSB.into:GROUPSC.into:GROUPSseparatedby','D.into&GROUPSseparatedby','(2012/11/17日考题中选项有改动，但是答案没有变。)5.TheSASdatasetWORK.CHECKhasanindexonthevariableCodeandthefollowingSASprogramissubmitted.procsortdata=WORK.CHECK;byCode;run;WhichdescribestheresultofsubmittingtheSASprogram?A.TheindexonCodeisdeleted.B.TheindexonCodeisupdated.C.TheindexonCodeisuneffected.D.Thesortdoesnotexecute.（在于有了index的variable，不能再用by来SORT了。）6.ThetableWORK.PILOTScontainsthefollowingdata:WORK.PILOTSIdNameJobcodeSalary----------------------001AlbertPT150000002BrendaPT170000003CarlPT160000004DonnaPT280000005EdwardPT290000006FloraPT3100000Thedatasetwassummarizedtoincludeaveragesalarybasedonjobcode:JobcodeSalaryAvg------------------PT15000060000PT17000060000PT16000060000PT28000085000PT29000085000PT3100000100000WhichSQLstatementcouldNOTgeneratethisresult?A.selectJobcode,Salary,avg(Salary)label='Avg'fromWORK.PILOTSgroupbyJobcodeorderbyId;B.selectJobcode,Salary,(selectavg(Salary)fromWORK.PILOTSasP1whereP1.Jobcode=P2.Jobcode)asAvgfromWORK.PILOTSasP2orderbyId;（这里利用的是自身的数据集，分别设置不同的别名，可以当做两个数据集来使用。不过这里WHERE语句很值得学习，我个人对这里的WHERE语句这样理解的：(selectavg(Salary)fromWORK.PILOTSasP1whereP1.Jobcode=P2.Jobcode)启示执行之后返回的是三个AVG均值，但是因为这三个AVG均值都是与JOBCODE匹配的，那么前面的selectJobcode,Salary，所以当执行到(selectavg(Salary)fromWORK.PILOTSasP1whereP1.Jobcode=P2.Jobcode)这里时，实际上是选择了selectjobcode中jobcode对应的avg均值。）C.selectJobcode,Salary,(selectavg(Salary)fromWORK.PILOTSgroupbyJobcode)asAvgfromWORK.PILOTSorderbyId;(子查询涉及不止一项。)D.selectJobcode,Salary,AvgfromWORK.PILOTS,(selectJobcodeasJc,avg(Salary)asAvgfromWORK.PILOTSgroupby1)whereJobcode=JcorderbyId;7.AquickruleofthumbforthespacerequiredtorunPROCSORTis:A.twotimesthesizeoftheSASdatasetbeingsorted.B.threetimesthesizeoftheSASdatasetbeingsorted.C.fourtimesthesizeoftheSASdatasetbeingsorted.D.fivetimesthesizeoftheSASdatasetbeingsorted.(140-2010:DearMissSASAnswers:AGuidetoSortingYourData中的描述是这样的：Ifyouwantthesorttocompleteentirelyinmemory,asimpleruleofthumbisfourtimesthesizeofthedataset.InreleasespriortoSAS9,therequiredworkspaceisapproximatelythreetofourtimesthesizeofthedatafile.BeginningwithSAS9,therequiredworkspaceisapproximatelytwicethesizeofthedatafile.Theworkspacecanbeallocatedinmemoryand/orondiskasautilityfile,dependingonwhichsortutilityandoptionsarespecified.DearMissSASAnswers,Canyoupleasegivemesomeguidanceonhowmuchmemoryittakestosorta2GBtableSigned,MemoryDeprivedDearMemoryDeprived,Ifyouwantthesorttocompleteentirelyinmemory,asimpleruleofthumbisfourtimesthesizeofthedataset.AndI'massumingthedatasetisnot