@SR11 wrote (bold italics mine)
@SR11 wrote:
Hi Astounding,
1. In my real dataset, yes the dates were formatted to dates(MMDDYY10.). The dataset in the post was just made in word doc.
2. The question is very logical. Thank you for the question. Yes 'falls between' includes Group 1 or 2 start and end dates. i.e. if the Group 1 start date is equal to Group 2 start or end date then Flag the observation.
"Group 1 or 2 start and end dates" suggests to me that you are look for an overlap or any group 1 date with any group 2 date. But your original post and the following phrase suggests you care about whether a group 1 start_date only overlaps with a group 2 date range.
So the program below assumes your earliest description - i.e. look only for the intersection of group 1 start_date with any group 2 range.
The program passes through each ID twice - the first pass reads only group2 records and updates an array of dates (group2_dates) with a dummy set to one for each group 2 date. The second pass re-reads the ID again (this time all records), and compares the start_date with the group2_dates array.
data want (drop=_:);
set have (where=(not (med in ('A','B','C'))) in=firstpass)
have (in=secondpass);
by id ;
array group2_dates {%sysevalf("01jan2015"d):%sysevalf("31dec2020"d)} _temporary_;
if first.id then call missing(of group2_dates{*});
if firstpass then do _d=start_date to end_date;
group2_dates{_d}=1;
end;
if secondpass;
if med in ('A','B','C') and group2_dates{start_date}=1 then flag=1;
run;
I offer no program test, as the data is not provide in the form of a working data step, which presumably would make a note about the apparently invalid date value for end_date (=15/02/2017) in the 7th data record.
This program assumes the data are sorted by ID (but can be any order within a given ID).
The primary feature here is the array. Instead of the array being indexed from (say) 1 to 1000, it is indexed on the underlying values of "01jan2015"d (20089) through "31dec2020"d (22280). Of course, you can change the lower bound and upper bound dates to fit your data.
The other point is that the array is _temporary_, meaning its values are retained from observation to over all records for a given id. So the firstpass populates the array, which is still available for the secondpass.
Click image to register for webinar
