Hi, I have a requirement from each subject visit date ( VIS_DATE) to find out next highest visit date (Next_h_Vdate). I am having issues with getting max highest visit date as below highlighted. WANT OUTPUT: 101 2-May-23 30-May-23 Next Visit date 103 10-May-23 11-May-23 Next Vist date proc sql;
create table want as
select a.*,b.Next_h_Vdate
from data1 a
left join data2 b
on a.SUBJECT = b.SUBJECT and Next_h_Vdate >= Visitdate
group by a.SUBJECT, a.VIS_DATE
having Next_h_Vdate=max(Next_h_Vdate);
run; Subject Visitdate Next_h_Vdate Flag 101 2-May-23 2-May-23 101 2-May-23 30-May-23 Next Vist date 102 5-May-23 1-May-23 102 5-May-23 5-May-23 No More Visit date 103 10-May-23 10-May-23 103 10-May-23 11-May-23 Next Vist date 103 10-May-23 12-May-23
... View more
