Hi @NewbieTom,
I'm not sure I totally understand you. You say that you want to analyze each row as well as the whole group. In that case, you could add the group condition to the data as a new column and then filter by it later, something like this:
proc sql;
create table pass1 as
select client, status, started, finished,
sum(status = "success") > 0 and max(finished) - min(started) <= 1 as pass
from grouped
group by client, group
order by sortorder
;
quit;
data pass;
set pass1;
where pass /* and other criteria */;
drop pass;
run;
I'm not sure how that connects to the rest of your post.
For "Pass" table, it would be
1) If the difference between the finished date of the current "success" and the finished date of the last "success" was less or equal to 1 day. Then
all the preceding failures as well as the current success would be dumped into "Pass" table. Do you mean to say that instead of comparing the finished date of the current success and the start date of the first of the preceding series of consecutive failures, you want to compare the finished date of the current success to the finished date of the preceding success? Is that true even if the previous success was for a different client?
Would it be something like this?
a) If current client <> previous client and current statis = success and current Finished - current Started <= 1 Is this referring to the case where the first row for a certain client is a success? If so, that would be handled by the code I shared. (There would only be one record in the group, so max(finished) would be the finished date of that one success and min(started) would be the started date for the same record.
b) If current client = previous client and current statis = success and previous status = success and current Finished - current Started <= 1 Is this referring to the case of two successes in a row for the same client? If so, the same is true that I said for a).
For "Open" table, it would be
1) If there was no "success" in the group. I think we got this. Great!
For "Exception" table, it would be
1) If the difference between the finished date of the current "success" and the finished date of the last "success" was greater than 1 day. Then all the preceding failures (could be more than one) as well as the current success would be dumped into "Exception" table. This is the same as what I did in my program, except use finished date of previous success instead of start date of first consecutive failure. Correct?
