proc sql;
create table want as
select a.id, a.date, max(b.date) as servicedate format=yymmddd10.
from table_a a, table_b b
where a.id = b.id and b.date <= a.date
group by a.id, a.date;
quit;
data a;
input Id $ Date : mmddyy10.;
format date mmddyy10.;
cards;
A1 01/02/19
D1 01/15/19
A1 01/24/19
D1 01/20/19
;
data b;
input Id $ Date : mmddyy10.;
format date mmddyy10.;
cards;
A1 01/01/19
D1 01/13/19
A1 01/23/19
D1 01/18/19
;
data want;
set a;
if _n_=1 then do;
if 0 then set b(rename=(date=serv_date));
declare hash H (dataset:'b(rename=(date=serv_date))',multidata:'y') ;
h.definekey ("id") ;
h.definedata ("serv_date") ;
h.definedone () ;
end;
do rc=h.find() by 0 while(rc=0);
if serv_date<date then last_serv_date=max(serv_date,last_serv_date);
rc= h.find_next();
end;
format last_serv_date mmddyy10.;
drop rc serv_date;
run;
Direct access
data a;
input Id $ Date : mmddyy10.;
format date mmddyy10.;
cards;
A1 01/02/19
D1 01/15/19
A1 01/24/19
D1 01/20/19
;
data b;
input Id $ Date : mmddyy10.;
format date mmddyy10.;
cards;
A1 01/01/19
D1 01/13/19
A1 01/23/19
D1 01/18/19
;
data want;
set a ;
if _n_=1 then do;
if 0 then set b(rename=(date=last_serv_date));
declare hash H (dataset:'b(rename=(date=last_serv_date))',multidata:'y',ordered:'d') ;
h.definekey ("id","last_serv_date") ;
h.definedata ("last_serv_date") ;
h.definedone () ;
declare hiter hh('h');
end;
rc=h.add(key:id,key:date,data:date);
rc = hh.setcur(key:id,key:date) ;
rc=hh.next();
drop rc;
run;
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 25. 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.