BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Schen
Fluorite | Level 6

Hi SAS users,

 

Please help me with the following question.

 

 

IDIndexdateStartdate
1118/1/20188/29/2018
1118/1/20188/21/2018
1118/1/20187/20/2018
2226/4/20176/1/2018
2226/4/20176/3/2018
2226/4/20176/4/2017
2226/4/20177/4/2018
3333/2/201512/4/2014
3333/2/20154/24/2015
3333/2/20154/25/2015

 

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

 

Output should look like.

 

IDWant date
1117/20/2018
2226/4/2017
33312/4/2014

 

Thank you for taking time to answer my question.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

 

View solution in original post

2 REPLIES 2
novinosrin
Tourmaline | Level 20
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;

 

novinosrin
Tourmaline | Level 20
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 2692 views
  • 3 likes
  • 2 in conversation