topic Re: Table look up using merge in SAS Programming

Table look up using merge

mauri0623 — Wed, 07 Aug 2019 19:02:15 GMT

DAI and PIPS are two SAS datasets. PIPS could have missing values for the variables ALC, ODN, LOA, and TAS. We want to lookup the DAI dataset and populate the missing variables from PIPS. We have 8 different Scenarios as stated on the attached document. I have created the below code that takes care of the scenario 1 and 5. However I am stocked on other scenarios. We don't have to use hash merging necessarily. Anything that can cover the scenarios is fine with me. Your help is highly and professionally appreciated.

Best Regards, Mauri Esfandiari

My code for scenarios 1 and 5 is below,

/****************************************************

Use the SAS has merge to find the matches between

the two tables and if they match place them in the

PIPS_DAI_Match table and if they don't then place

them in the table Error_File.

****************************************************/

data sas_perm.PIPS_DAI_Match sas_perm.Error_File(drop=rc);

if 0 then set work.pips_no_sudo;

if _N_ = 1 then do;

declare hash h(dataset:'work.dai_fixed');

h.defineKey('ipac');

h.defineData('OBLIGATING_DOC_NO', 'accounting', 'tas');

h.defineDone();

call missing(OBLIGATING_DOC_NO, accounting, tas);

end;

set work.dai_fixed;

rc=h.find();

if rc then output sas_perm.PIPS_DAI_Match;

else output sas_perm.Error_file;

run;

Re: Table look up using merge

Shmuel — Wed, 07 Aug 2019 19:30:49 GMT

You can do all scenarios in the same data step. I assumed that variables are numeric (missing = .).

Scenario 6 is not clear - where are the other variables ?

data want_ok want_err;
 merge pips(in=inp)
       dai (in=ind rename=(alc=Dalc odn=Dodn loa=Dloa tas=Dtas);
  by <identifier>;
      if inp;
      if Dalc=. and Dodn=. and Dloa=. and Dtas=. 
         then output want_err; 
      else do;
        if odn=. then odn=Dodn;
        if loa=. then loa=Dloa;
        if tas=. then tas-Dtas;
        output want_ok;
     end;
     drop Dalc Dodn Dloa Dtas;
run;

Re: Table look up using merge

mauri0623 — Wed, 07 Aug 2019 20:01:05 GMT

Than you for the quick response. I accepted your solution.