topic Re: How to get last service date in ODS and Base Reporting

How to get last service date

Gil_ — Thu, 31 Jan 2019 14:06:23 GMT

Hi i have 2 tables i need to compare service date from table a and determine the last service date from table b
Table a
Id. Date
A1. 01/02/19
D1. 01/15/19
A1. 01/24/19
D1 01/20/19

Table b

Id. Date
A1. 01/01/19
D1. 01/13/19
A1. 01/23/19
D1 01/18/19
Output

Id. Date
A1. 01/02/19. A1. 01/01/19
D1. 01/15/19. D1. 01/13/19
A1. 01/24/19. A1. 01/23/19
D1 01/20/19. D1 01/18/19

Thanks for assistance

Re: How to get last service date

Kurt_Bremser — Thu, 31 Jan 2019 14:14:57 GMT

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;

Re: How to get last service date

novinosrin — Thu, 31 Jan 2019 15:11:53 GMT

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;

Re: How to get last service date

novinosrin — Thu, 31 Jan 2019 16:23:34 GMT

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;