How to get next highest date from a date variable.

[Spintu]

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

[Tom]

Where did those next visit date values come from?

You did not show the source data, just the results.

[Spintu]

next visit date values come from different source audit report. As i have merged finally get this data. here after I am not sure how to get it.

[Tom]

---

@Spintu wrote:
next visit date values come from different source audit report. As i have merged finally get this data. here after I am not sure how to get it.

---

If you are asking for help on how to combine TWO datasets then show example inputs for both input datasets and the result you want for those example inputs.

[Spintu]

Hi,

I hope these tables Data1 and Data2 can help.

Data1
Subject	Visitdate	Visit
101	2-May-23	Screen
102	5-May-23	Week3
103	10-May-23	Screen
104	12-May-23	Week1
Data2
Subject	Next_h_Vdate	Folder
101	2-May-23	Screen
101	30-May-23	week1
102	4-May-23	Week2
102	5-May-23	Week3
103	10-May-23	Screen
103	11-May-23	week1
103	13-May-23	Week2
104	12-May-23	Week1
104	15-May-23	Week2
Want
Subject	Visitdate	Visit	Next_h_Vdate	Folder	Flag
101	2-May-23	Screen	2-May-23	Screen
101	2-May-23	Screen	30-May-23	week1	Next  Highest Vist date
102	5-May-23	Week3	4-May-23	Week2	N/A
102	5-May-23	Week3	5-May-23	Week3	N/A
103	10-May-23	Screen	10-May-23	Screen
103	10-May-23	Screen	11-May-23	week1	Next  Highest Vist date
103	10-May-23	Screen	13-May-23	Week2
104	12-May-23	Week1	12-May-23	Week1
104	12-May-23	Week1	15-May-23	Week2	Next  Highest Vist date

[Tom · Accepted solution]

So first let's convert your listing into actual data.

data date1 ;
  input Subject $ Visitdate :date. Visit :$10.;
  format visitdate date9.;
cards;
101 2-May-23 Screen      
102 5-May-23 Week3      
103 10-May-23 Screen      
104 12-May-23 Week1      
;          
data date2 ;
  input Subject $ Next_h_Vdate :date. Folder :$10.;
  format Next_h_Vdate date9.;
cards;
101 2-May-23 Screen      
101 30-May-23 week1      
102 4-May-23 Week2      
102 5-May-23 Week3      
103 10-May-23 Screen      
103 11-May-23 week1      
103 13-May-23 Week2      
104 12-May-23 Week1      
104 15-May-23 Week2  
;

Now let's combine them matching on SUBJECT and only take combinations where the "next" date is larger than the current date.   While we are at it sort them.

proc sql;
create table want as
  select a.subject,a.visitdate,a.visit,b.next_h_vdate,b.folder
  from date1 a left join date2 b
  on a.subject=b.subject and b.next_h_vdate>a.visitdate
  order by a.subject,a.visitdate,b.next_h_Vdate
;
quit;

Now we have the information we need to check which record indicates the NEXT visit date.

data want;
  set want;
  by subject visitdate ;
  if first.visitdate then found=0;
  if (not found) and (next_h_vdate>visitdate) then do;
    found=1;
    flag = 'Next Highest Visit Date';
  end;
  else if found then delete;
  else if last.visitdate then flag='N/A';
  retain found;
run;

Result

                                           Next_h_
Obs    Subject    Visitdate    Visit         Vdate    Folder    found    flag

 1       101      02MAY2023    Screen    30MAY2023    week1       1      Next Highest Visit Date
 2       102      05MAY2023    Week3             .                0      N/A
 3       103      10MAY2023    Screen    11MAY2023    week1       1      Next Highest Visit Date
 4       104      12MAY2023    Week1     15MAY2023    Week2       1      Next Highest Visit Date

[Spintu]

Thank you for in explain in details.