/******************************************************************* ** Macro Name: %LeftJoin ** Topic: Merge ** Requirements: ..\Macros\LeftJoin\LeftJoin_URS.doc ** Source File: ..\Macros\LeftJoin\LeftJoin.sas ** Test Program: ..\Macros\LeftJoin\LeftJoin_UAT.sas ** Manual: ..\Macros\LeftJoin\LeftJoin_UM.doc ** Company/Department: - ** Software(Dev/Sys): SAS Version 9.2 / Windows XP **------------------------------------------------------------------ ** Short Description: Perform a left join. ** Input: Two datasets for merge. ** Output: Result dataset. ** Return Value: - ** Comments: - ******************************* H I S T O R Y ********************** ** Developer Date Remarks ** --------- ---------- ----------------------------------------- ** HaS 02.08.2009 Start of development ** HaS 08.08.2009 Implementation finished *******************************************************************/ %macro LeftJoin( pHelp /* = ? - print help text to log window */ ,pLibIn1 = work /* library with input data set 1 */ ,pDSIn1 = /* input data set 1 */ ,pDSOpt1 = /* options of data set 1 */ ,pLibIn2 = work /* library with input data set 2 */ ,pDSIn2 = /* input data set 2 */ ,pDSOpt2 = /* options of data set 2 */ ,pKeys = /* key variables for by statement */ ,pLibOut = work /* library for output data set */ ,pDSOut = /* output data set */ ,pDSOptOut = /* options of output data set */ ,pDSCode = /* additional datastep code */ ,pDebug = no /* = (yes, y) - debug mode on */ /* = else - debug mode off */ ); %local tDebug /* Y/N - debug mode on/off */ tErr /* 0 - no errors found */ /* 1 - error, stop macro execution */ tMacName /* name of macro */ tMsgTxt /* text for log message */ tNewLib /* name of temporary library */ tRes /* result of proc sql */ tVar /* current variable in list pKeys */ tVarCnt /* current element number in list pKeys */ ; /******************************************************************* ** print help to log *******************************************************************/ %if (&pHelp. eq ?) %then %do; %put %nrstr(%LeftJoin); %put %nrstr(Perform a left join.); %put ; %put %nrstr(Syntax:); %put %nrstr(%LeftJoin%(, pDSIn1, ,); %put %nrstr( , pDSIn2, , pKeys,); %put %nrstr( , pDSOut, , %);); %put ; %put %nrstr(Example call:); %put %nrstr(%LeftJoin%(); %put %nrstr( pLibIn1 = lib1); %put %nrstr( ,pDSIn1 = dsn1); %put %nrstr( ,pDSOpt1 = drop = age); %put %nrstr( ,pLibIn2 = lib2); %put %nrstr( ,pDSIn2 = dsn2); %put %nrstr( ,pKeys = usubjid dsnseq); %put %nrstr( ,pLibOut = work); %put %nrstr( ,pDSOut = dsnout); %put %nrstr(%);); %GOTO HelpExit; %end; /* %if (&pHelp. eq ?) */ /******************************************************************* ** macro intro *******************************************************************/ %let tMacName = &sysmacroname.; %put ##### Begin of macro &tMacName. #####; /******************************************************************* ** set debug mode *******************************************************************/ %if %upcase(&pDebug.) eq YES or %upcase(&pDebug.) eq Y %then %do; %let tDebug = Y; %end; %else %do; %let tDebug = N; %end; /******************************************************************* ** parameter check *******************************************************************/ %let tErr = 0; /* check parameter exist */ %if %quote(&pDSIn1.) eq %then %do; %put %str(ERR)%str(OR: &tMacName. - Parameter pDSIn1 is empty !); %let tErr = 1; %end; %if %quote(&pDSIn2.) eq %then %do; %put %str(ERR)%str(OR: &tMacName. - Parameter pDSIn2 is empty !); %let tErr = 1; %end; %if %quote(&pDSOut.) eq %then %do; %put %str(ERR)%str(OR: &tMacName. - Parameter pDSOut is empty !); %let tErr = 1; %end; %if %quote(&pKeys.) eq %then %do; %put %str(ERR)%str(OR: &tMacName. - Parameter pKeys is empty !); %let tErr = 1; %end; %if &tErr. ne 0 %then %do; /* if error stop macro execution */ %let tMsgTxt = %str(&tMacName. - stop macro execution !); %GOTO ErrExit; %end; /* check libraries exist */ %if %sysfunc(libref(&pLibIn1.)) ne 0 %then %do; %put %str(ERR)%str(OR: &tMacName. - &pLibIn1. doesn%'t exist !); %let tErr = 1; %end; %if %sysfunc(libref(&pLibIn2.)) ne 0 %then %do; %put %str(ERR)%str(OR: &tMacName. - &pLibIn2. doesn%'t exist !); %let tErr = 1; %end; %if %sysfunc(libref(&pLibOut.)) ne 0 %then %do; %put %str(ERR)%str(OR: &tMacName. - &pLibOut. doesn%'t exist !); %let tErr = 1; %end; /* check datasets exist */ %if %sysfunc(exist(&pLibIn1..&pDSIn1.)) eq 0 %then %do; %put %str(ERR)%str(OR: &tMacName. - &pDSIn1. doesn%'t exist !); %let tErr = 1; %end; %if %sysfunc(exist(&pLibIn2..&pDSIn2.)) eq 0 %then %do; %put %str(ERR)%str(OR: &tMacName. - &pDSIn2. doesn%'t exist !); %let tErr = 1; %end; %if &tErr. ne 0 %then %do; /* if error stop macro execution */ %let tMsgTxt = %str(&tMacName. - stop macro execution !); %GOTO ErrExit; %end; /* check key variables exist in pDSIn1 */ %let tVarCnt = 1; %let tVar = %qscan(&pKeys., &tVarCnt., ' '); %do %while(&tVar. ne); /* loop over all key variables */ proc sql noprint; select left(put(count(*), best.)) into :tRes from dictionary.columns where upcase(libname) eq "%upcase(&pLibIn1.)" and upcase(memname) eq "%upcase(&pDSIn1.)" and upcase(name) eq "%upcase(&tVar.)"; quit; %if &tRes. eq 0 %then %do; %let tMsgTxt = %str(Variable &tVar. doesn%'t exist in &pDSIn1.); %put %str(ERR)%str(OR: &tMacName. - &tMsgTxt.); %let tErr = 1; %end; %let tVarCnt = %eval(&tVarCnt. + 1); %let tVar = %qscan(&pKeys., &tVarCnt., ' '); %end; /* %do %while(&tVar. ne) */ /* check key variables exist in pDSIn2 */ %let tVarCnt = 1; %let tVar = %qscan(&pKeys., &tVarCnt., ' '); %do %while(&tVar. ne); /* loop over all key variables */ proc sql noprint; select left(put(count(*), best.)) into :tRes from dictionary.columns where upcase(libname) eq "%upcase(&pLibIn2.)" and upcase(memname) eq "%upcase(&pDSIn2.)" and upcase(name) eq "%upcase(&tVar.)"; quit; %if &tRes. eq 0 %then %do; %let tMsgTxt = %str(Variable &tVar. doesn%'t exist in &pDSIn2.); %put %str(ERR)%str(OR: &tMacName. - &tMsgTxt.); %let tErr = 1; %end; %let tVarCnt = %eval(&tVarCnt. + 1); %let tVar = %qscan(&pKeys., &tVarCnt., ' '); %end; /* %do %while(&tVar. ne) */ %if &tErr. ne 0 %then %do; /* if error stop macro execution */ %let tMsgTxt = %str(&tMacName. - stop macro execution !); %GOTO ErrExit; %end; /* add () to option parameter */ %if (%quote(&pDSOpt1.) ne ) %then %do; %let pDSOpt1 = (&pDSOpt1.); %end; %if (%quote(&pDSOpt2.) ne ) %then %do; %let pDSOpt2 = (&pDSOpt2.); %end; %if %quote(&pDSOptOut.) ne %then %do; %let pDSOptOut = (&pDSOptOut.); %end; /******************************************************************* ** create temporary library *******************************************************************/ %CreateLib(tNewLib); /******************************************************************* ** sort both datasets *******************************************************************/ proc sort data = &pLibIn1..&pDSIn1. &pDSOpt1. out = &tNewLib..tDSIn1; by &pKeys.; run; proc sort data = &pLibIn2..&pDSIn2. &pDSOpt2. out = &tNewLib..tDSIn2; by &pKeys.; run; /******************************************************************* ** merge both datasets *******************************************************************/ data &pLibOut..&pDSOut. &pDSOptOut.; merge &tNewLib..tDSIn1(in = a) &tNewLib..tDSIn2; by &pKeys.; if a; &pDSCode.; run; /******************************************************************* ** delete temporary datasets *******************************************************************/ %if &tDebug. eq N %then %do; /* debug mode off */ proc datasets lib=&tNewLib. nolist kill memtype=all; quit; libname &tNewLib. clear; %end; %GOTO Exit; %ErrExit: %put %str(ERR)%str(OR: &tMsgTxt.); %Exit: %put ##### End of macro &tMacName. #####; %HelpExit: %mend; /******************************************************************* ** End of Macro *******************************************************************/