topic Re: How to get next highest date from a date variable. in SAS Programming

How to get next highest date from a date variable.

Spintu — Wed, 07 Jun 2023 13:28:47 GMT

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

Re: How to get next highest date from a date variable.

Tom — Wed, 07 Jun 2023 13:33:20 GMT

Where did those next visit date values come from?

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

Re: How to get next highest date from a date variable.

Spintu — Wed, 07 Jun 2023 13:39:24 GMT

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.

Re: How to get next highest date from a date variable.

Tom — Wed, 07 Jun 2023 16:09:57 GMT

@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.

Re: How to get next highest date from a date variable.

Spintu — Wed, 07 Jun 2023 17:26:08 GMT

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

Re: How to get next highest date from a date variable.

Tom — Wed, 07 Jun 2023 18:50:28 GMT

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

Re: How to get next highest date from a date variable.

Spintu — Wed, 07 Jun 2023 18:51:44 GMT

Thank you for in explain in details.