First, I would suggest you to read in the dates using an informat like below, since you will need to compare dates in the process.
:mmddyy10.
Here is your data recreated:
data test;
infile datalines missover dsd dlm=' ';
input entity vote_date :mmddyy10. passed $ failed $;
datalines;
7 10/25/2015 Y
7 1/18/2016 Y
7 10/18/2016 Y
14 2/16/2017 Y
14 8/26/2017 Y
14 9/15/2017 Y
run;
Then use a RETAIN statement to keep track of the most recent date for passed and failed referendum (I used lastpassed_date and lastfailed_date to hold their values).
data test2;
set test;
by entity vote_date;
retain lastpassed_date lastfailed_date;
if first.entity then do;
lastpassed_date=.;
lastfailed_date=.;
end;
if passed='Y' then lastpassed_date=vote_date;
else if failed='Y' then lastfailed_date=vote_date;
run;
Now that you have these dates stored, you can compare them at the end of the program. Sounds like you might only want to keep the records where lastpassed_date is greater than lastfailed_date, making sure to check that lastfailed_date isn't missing.
if lastpassed_date > lastfailed_date and not missing(lastfailed_date);
Let me know if this works.
... View more
