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;
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;
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;
Than you for the quick response. I accepted your solution.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: