topic Find the latest date before a specific date in SAS Programming

Find the latest date before a specific date

georgel — Sat, 26 Dec 2020 07:27:50 GMT

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

Re: Find the latest date before a specific date

singhsahab — Sat, 26 Dec 2020 09:20:12 GMT

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;

Re: Find the latest date before a specific date

Kurt_Bremser — Sat, 26 Dec 2020 09:30:15 GMT

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.

Re: Find the latest date before a specific date

georgel — Sat, 26 Dec 2020 09:39:38 GMT

This is exactly what I wanted. Great Many thanks

Regards
George

Re: Find the latest date before a specific date

georgel — Sat, 26 Dec 2020 09:44:23 GMT

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