topic Re: Get the closest date to index date by ID in SAS Programming

Get the closest date to index date by ID

Schen — Tue, 21 Aug 2018 21:23:22 GMT

Hi SAS users,

Please help me with the following question.

ID	Indexdate	Startdate
111	8/1/2018	8/29/2018
111	8/1/2018	8/21/2018
111	8/1/2018	7/20/2018
222	6/4/2017	6/1/2018
222	6/4/2017	6/3/2018
222	6/4/2017	6/4/2017
222	6/4/2017	7/4/2018
333	3/2/2015	12/4/2014
333	3/2/2015	4/24/2015
333	3/2/2015	4/25/2015

I want to get the startdate that is closest and before or on indexdate ( Startdate <= Indexdate by ID).

Output should look like.

ID	Want date
111	7/20/2018
222	6/4/2017
333	12/4/2014

Thank you for taking time to answer my question.

Re: Get the closest date to index date by ID

novinosrin — Tue, 21 Aug 2018 21:50:26 GMT

data have;
input ID	(Indexdate	Startdate) (:mmddyy10.);
format Indexdate	Startdate mmddyy10.;
cards;
111	8/1/2018	8/29/2018
111	8/1/2018	8/21/2018
111	8/1/2018	7/20/2018
222	6/4/2017	6/1/2018
222	6/4/2017	6/3/2018
222	6/4/2017	6/4/2017
222	6/4/2017	7/4/2018
333	3/2/2015	12/4/2014
333	3/2/2015	4/24/2015
333	3/2/2015	4/25/2015
;

proc sql;
create table want(drop=d) as
select *, case when (Startdate <= Indexdate) then (Indexdate-Startdate+1) end as d
from have
group by id
having d=min(d);
quit;

Re: Get the closest date to index date by ID

novinosrin — Tue, 21 Aug 2018 21:57:31 GMT

data want;
do until(last.id);
set have;
by id;
call missing(k);
if Startdate <= Indexdate then k=Indexdate-Startdate+1;
min=min(min,k);
end;
do until(last.id);
set have;
by id;
k=Indexdate-Startdate+1;
if min=k then output;
end;
drop k min;
run;