Accepted Solutions
Ok. There is quite a bit of logic to consider. See if this works for you. It returns the correct data for both of your posted pairs of example data sets table1 and table2
data table1;
input Product $ (st_date End_date)(:date11.) Price;
format st_date End_date date9.;
datalines;
A 10-Jan-18 15-Jan-18 10
;
data table2;
input Product $ Override_date:date11. Price;
format Override_date date9.;
datalines;
A 10-Jan-18 11
A 11-Jan-18 12
A 13-Jan-18 9
A 14-Jan-18 5
;
data want;
if 0 then set table2(rename=(Price=Newprice));
if _N_ = 1 then do;
declare hash h(dataset:'table2(rename=(Price=Newprice))', multidata:'Y');
h.defineKey('Product');
h.defineData(all:'Y');
h.defineDone();
end;
call missing(Override_date, Newprice);
set table1;
rc=h.find();
_tempOverride_date=Override_date;
_temp_st_date=st_date;
_tempEnd_date=End_date;
_tempprice=Newprice;
if rc ne 0 then output;
do c=1 by 1 while (rc=0);
h.has_next(result: r);
if r ne 0 then do;
Price=Newprice;
rc=h.find_next();
End_date=Override_date-1;
output;
st_date=End_date+1;
end;
if r=0 & c>1 then do;
st_date=End_date+1;
End_date=_tempEnd_date;
Price=Newprice;
output;
rc=h.find_next();
end;
if r=0 & c=1 then do;
if Override_date=st_date | Override_date=End_date then do;
st_date=_temp_st_date;
End_date=_tempEnd_date;
Price=_tempprice;
output;
end;
else if st_date < Override_date < End_date then do;
st_date=_temp_st_date;
End_date=Override_date-1;
output;
st_date=End_date+1;
End_date=_tempEnd_date;
Price=Newprice;
output;
end;
else output;
rc=h.find_next();
end;
end;
keep product st_date end_date price;
run;
proc print data=want;
run;
.. Also, for the first data sets posted, it returns
Hi @Srigyan
I have a macro to handle problems like this. It is an interval merger that takes several intervals for the same ID in both datasets and accepts missing periods between intervals. It works with subgroups too. It is made to handle large data sets, so it is quite effecient.
It requires intervals in both data sets, and besides interval- and ID variables it cannot accept variables withe same name in both data sets, so something must be done to add end dates to the override table and chane the price variable name before calling the macro. And the documentation is written in danish. It is big, so I have attached it as a file. You are free to use it, and I will be happy to help with any questions.
It is used like this:
data work.a;
informat St_date End_date anydtdte9.;
input Product $ St_date End_date Price;
datalines;
A 17/12/2017 09/01/2018 8
A 10/01/2018 15/01/2018 10
;
run;
data work.b;
informat Override_date End_date anydtdte9.;
input Product $ Override_date End_date new_price;
datalines;
A 10/01/2018 10/01/2018 11
A 11/01/2018 12/01/2018 12
A 13/01/2018 13/01/2018 9
A 14/01/2018 31/12/9999 5
;
run;
%FletrensInterval(work.a, work.b, work.want, unita=Product, datefirsta=St_date,
datelasta=End_date, unitb=Product, datefirstb=Override_date, datelastb=End_date, join=left);
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.
-
2631 views
-
3 likes
-
-
