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 |
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
Where did those next visit date values come from?
You did not show the source data, just the results.
@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.
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 |
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: