Advanced Match Merging: Techniques, Tricks, and …

1 AdvancedMATCH- Merging: Techniques, tricks , ,NCABSTRACTM atch- merging ,orBYmerging,isthemostcommon mergingtechniqueusedinSAS**.Yet, snuancesandsubtleties,givesexamplesofmer geswhereevenexperiencedprogrammershavebe entrippedup, ,attheoffice, : TheSASmergeisasubtlething ;or Mergeshouldbemadetoworkbetterinthissitua tion ;or Beverycarefidwithusingmergeinthiscasebec auseyougetunexpectedresults! ,sortedmeansthattheinputfileshaveacommon keyandthattherecordsineachfileareordered accordingtothekeyfield(s).Considerthefol lowingtwoinputfiles, !MA04FA05 KAYAlOJIM11 MEvenwiththissimpleexample, (fileONE)andA04(fileTWO) :PROCSORTOATA=ONE:BYIO:RUN;PROCSORTOATA= TWO:BYIO:RUN:OATAONETWO;tllR~~ONETWO;RUN : Thiscodeissimpleenough, ,forsure,thattheinputdatasetsarealreadys ortedthentheSAScodecouldbereducedto:DATA ONETWO;t44R~~ONETWO;RUN; Now,whatcouldbesimpler?Well, , ;MERGELINETWO:RUN; (AY47 FAlOJIM11 MNoticethattheA05 IDislostinthismergeandtheNameKayismovedf i-omID=A05toID=A04, ,ontheotherhand, , , , ~ ,thereisonlyonekeyvariableanditidentifie stheunitofdatatherecordisassociatedwith, suchasapatient,accountnumber,transaction number,household,interviewee, ,goodBYvariablesarekeyvariablesthatEXACT LY identi~ , , :(1)theBYvariabledoesnotuniquelyidentifi therecord(ambiguousidentification);and(2 ) ,time,anddateareambiguous, ,thesetypesofitemsdonotreliablyidenti~ ,forexample, ,let ssayinaclinicaltrial, ,inpractice,usingthedateasthekeyvariable toidenti~thepatient , , ,onetiomthelabandanothertheclinic, , :-ManipulationHistory-Type(numericorchar acter)-Justification(forcharactervariabl esonly,usuallyleft) , ,howitwascreated,initialized, , ,asarule, , ,manipulated,orchanged,inanyway, , (unlessyouaredoingafuzzymergewhichisneit herdiscussedherenorrecommended).)

2 BecausethevaluesmustbeEXACTLY thesame,wesuggestthat,likeallgoodlabels, ,andonlyifitisimpossible, ,ifonefilehasanumericalBYvariable,say2 IDx=ll,anditismultipliedby10, ,anddividingbylOandmultiplyingby100inthe othertileismathematicallyidentical,butin practicell*lOisnotequalto(ll/10)* , ,theBYvariablesineachinputfileshouldhave identicalhistoriesofmanipulation(includi ngnumericmanipulationandLENGTH manipulation).BYVARIABLETYPE,JUSTIFICATI ON, (numericorcharacter) ,ifaspecificBYvariable,sayID3,isnumerici noneinputdataset, ERROR:VariableID3hasbeendefinedasbothcha racterandnumeric. ,beforetryingsuchaconversion, , ,theycouldbetwodifferenttypesofdatawhich ,bycoincidence, (Ieflorright) ,forexample,SASwillnotmatchtheleft-justi fiedvalueof A05 fromonedatasetwiththeright-justifiedvalu eof A05 ~ ,onceagain, ,withcertainty, ,orderivedfromfi-actions, (andattribute) ,iftheyhavechanged, :INPUTID-$1-3V_86-7:CARDS;A2212A3888A513 3 RUN:DATASDS2;INPUTID-$1-4V_96-7;CARDS;A2 272A3831A4111A51158 FILESOS_l_2------------ ;A221272 MERGESDS1 SDS_2;A3888;;RUN,8 ,thevariable,ID,hasaLENGTH=3inthefirstfi leandaLENGTH= ,theprogramdatavector,fortheoutputfile, ,inthiscase,theafterfirstfileinthemerges tatementisscanned,thedatavectoris(ID$3,V _8).

3 Then,thesecondtileisscannedandnewvariabl esaddedtothevector,sothatthefinaloutputd atais(ID$3V_8V_9).SincetheIDhasaLENGTH=3 inthedatavector,thevalueofID= ,whentheLENGTH saredifferent, ,thedesiredresultsareobtained!---------- ----------FILESDS21 DATASDS21;--------:.:-MERGESDS2 SDS_l:IDV9V8~uN,8 YID;------:----:.A227212---------------- ----A383788,44;1133A51158 Thislastexample, , (whichwasexaminedinaprevious------------ --------------------------section),theBY Value, ,namedALPHAandBETA, #;:2593AO1A02A04A1OA25A25A32A32A32A55A92 A92A92A96A96582047119;226188+:3728 Noticethatintheabovetwoinputfiles, , ,therearetworecordsforID=AO1, ~W~ERGE~~~IDV1 IDV_4-----:. :1)zero-to-one2)one-to-zero3)one-to-one4 )one-to-many5)many-to-one6)few-to-many7) many-to-few8) , ,alltypesofmatch-mergescancoexistinthein putfiles, , (seethecolumnentitledNUMOUTREC) ,lookatthegroupofrecordsforID= ,thereare2inputrecordsintheALPHA datasetand3inputrecordsintheBETA dataset,thus, ,thematch-mergeprocessitselflooksatthein putrecordsonaBYgroupbyBYgroupbasis, , , , ,O-TO-1,ANDl-TO-OMATCH-MERGET heworkingsofone-to-one,zero-to-one,andon e-to-zeromatch-mergearefairlyintuitivean dhavealreadybeenexaminedinthefirstexampl egiveninthispaper.

4 ( *no* )MANY-TO-MANYMATCH-MERGET hemany-to-manytypeofmatch-mergeoccurswhe n,foragivenBYgroup, ,a3-to-3merge,a6-to-6-to-6merge, , ;~~R~~ALPHABETA;RUN; !A2576A2538A257638 Themany-to-manymatch-mergeisessentiallya one-to-one(non-BY,non- Match ) , ,howdoesoneknowthatthefirstvalueofV_l(24 )issupposedtobematchedwiththefirstvalueo fv_4(4).Whyshouldn tV_l=24bematchedwithV_4=91(thesecondvalu eofV_4)?Ifgreatcareisnottaken, ,orperhapstheBYvariablesthemselvesarefau lty(seetheBYVARIABLE andassociatedtrapssectionforadiscussiono ffaultyBYvariables). ,thereismorethanonerecordinthefirstinput dataset, ,a3-to-5merge,aS-to-IO,orax-to-y(wherey> x>l) few (=x) ,few-to-many(orx-to-ymerge) Match -mergeis thesameasamany-to-manymerge(ora1-to-1non -matchnon-BYmerge)forthefirst few +lrecordsintheBYgroup, (Thismergeisperformedusingthecodegivenin theMANY-TO-MANYMATCH-MERGE section.) :. :----:-A927014A92467;A9246 SinceessentiallythefirstfewrecordsintheB Ygroupofafew-to-manyperformamany-to-many Match -merge, ,sincethelastpartofafew-to-manyisa1-to-m anymatch-merge, ,andmostuseilid, ;]~R~~A[PHABETA;RUN; :---IDV_lIDV_4 IDV_lV4 A325A3215A353A325A3222A325; , ;tj:R~~AEPHABETA;v1=V:1*2;RUNT---------- ---------FILEALPHAFILEBETAFILEALPHBET--- ------- :61A35 SeehowtheV_lvariableischanginginvaluefor ID=A32!

5 Thismaybecounter-intuitive, (inthiscase,10)forthewholeBYgroup,thefol lowingcodewouldprovidesucharesult, ;MERGEAEPHABETA;BYID;IFFIRSTSTHENV_l=V_l *2;RUN; , ,alineofcode(IFFIRSTSTHENV_l=V_l*2;) , ,thecodewouldexecuteproperlyona1-to-Oand al-to-1 BYgroupaswellasa1-to-many(missingvaluesf orV_lwillbeaccepted).Thefollowingcodeadd sastatementthatchecksthatthereisone,ando nlyone,ALPHA recordineachBYgroup:------------------DA TAALPHBET;i44R~~A[PHA(IN=INA)BETA;IFFIRS TSTHENVl=V1*2;IFINA=O&FIRSTS ORIMAM& Tiffthan1 AlphaRecRUN:------------------OVERLAPPIN GVARIABLES: Match -MERGEforBygroup :AONE-TO-MANYT heclassicalideaofamatch-mergeistotakesom evariablesfromonefileandADDthem(ortackth emon) , , ,V_4. ;~:R~~,AEPHABETA;RUN; -------------FILEBETAFILEALPH_BETl.. ~ ~ , , , , ,comparingthetwovaluesduringthemerge, , ,thenumberofoutputvariables(0)shouldbeth esumofnumberofinputvariables(11+12)lesst henumberofBYvariables(B).Informulaform,0 =(11+12) ,then0=(11+..InB*(n(1)l).ORDEROFDATASETS INMERGESTATEMENTI nthesectionBYVARIABLELENGTH, ;MERGEBETAALPHA;BYID; :----IDV_4V_lA32105A32225A32615A3563 NotethatthevalueofV_4isdifferentforthefi rstoutputrecord!)]

6 , ,andlessimportant, , (inthedescriptionpartofthedataset)asIDV_ 4V_l,whereasinthesecondlastexample, , , :Reference(Version6,FirstEdition),undert hetitle DATAStepProcessingduringMatch- merging