Find the latest date before a specific date

[georgel]

Hi all 

Best wishes to all of you for 2021

I am trying to find a simple solution  for the the data I have 

data have;
input cusip_id Name $10. date_issue :ddmmyy10. datadate :ddmmyy10.  ;
format date_issue ddmmyy10.;
format datadate ddmmyy10.;
datalines;
10343452 Henry PLC 01/04/2014  31/12/2012
10343452 Henry PLC 01/04/2014  31/12/2013
10343452 Henry PLC 01/04/2014  31/12/2014
10343452 Henry PLC 01/04/2014  31/12/2015
10343452 Henry PLC 01/04/2014  31/12/2016
;
run;
/*WHAT I WANT*/
data want ;
input cusip_id Name $10. date_issue :ddmmyy10. datadate :ddmmyy10.  ;
format date_issue ddmmyy10.;
format datadate ddmmyy10.;
datalines;
10343452 Henry PLC 01/04/2014  31/12/2013
;
run;

My solution:

data have;
     set have;
	 If dat_issue>=datadate;
run;
proc sort data=want out=want;
     by cusip descending datadate ;
run;
proc sort data=want out=want nodupkeys;
     by cusip  ;
run;

Is there  a way to be efficient and have this in a single step. Can this be done in proc sql when I join the cusip and issue_date with the dataset of compustat (that includes all the fiscal years i.ie datadate)? 

Many thanks in advance 

Regards

George

[singhsahab · Accepted solution]

proc sql;
create table want as
select  *,monotonic() as row_num from have where date_issue ge datadate group by cusip_id having max(row_num)=row_num;
quit;

[georgel]

This is exactly what I wanted. Great Many thanks

Regards
George

[Kurt_Bremser]

If your dataset is already sorted in ascending order, a double DO loop does it:

data want;
do until (last.cusip);
  set have;
  by cusip;
  if datadate le date_issue then ref = datadate;
end;
do until (last.cusip);
  set have;
  by cusip;
  if datadate = ref then output;
end;
drop ref;
run;

In SQL, try this

proc sql;
create table want as
  select *
  from have
  where datadate le date_issue
  group by cusip
  having date_issue - datadate = min(date_issue - datadate)
;
quit;

Untested, posted from my tablet.

[georgel]

Dear Kurt
Many thanks for this solution and for your quick response. I really appreciated your help
Regards
George