Computing a flag based on consecutive columns

Question

I am struggling with quite a simple task.

Below is a sample of my data, I have a bunch of IDs and periods, with period_1 reflecting the value 1 week before, period_2 the value 2 weeks before and so on...

data have;
infile datalines delimiter='|' missover;
input id (period_1 period_2 period_3 period_4 period_5 period_6) (:$3.);
datalines;
1||NO|||NO|
2|NO||NO|NO||
3|YES|YES|YES|||
4|NO|YES|NO|YES|YES|NO
5|NO|YES|NO|NO|YES|YES
6||NO|YES|NO|YES|YES
7|YES|||||
8|YES||NO|NO|YES|
9|YES|YES|NO|NO|NO|
10|NO|NO|NO|YES|YES|
11|NO|YES| |NO|NO|NO
;

+----+----------+----------+----------+----------+----------+----------+
| id | period_1 | period_2 | period_3 | period_4 | period_5 | period_6 |
+----+----------+----------+----------+----------+----------+----------+
|  1 | NO       | NO       |          |          |          |          |
|  2 | NO       | NO       | NO       |          |          |          |
|  3 | YES      | YES      | YES      |          |          |          |
|  4 | NO       | YES      | NO       | YES      | YES      | NO       |
|  5 | NO       | YES      | NO       | NO       | YES      | YES      |
|  6 | NO       | YES      | NO       | YES      | YES      |          |
|  7 | YES      |          |          |          |          |          |
|  8 | YES      | NO       | NO       | YES      |          |          |
|  9 | YES      | YES      | NO       | NO       | NO       |          |
| 10 | NO       | NO       | NO       | YES      | YES      |          |
| 11 | NO       | YES      |          | NO       | NO       | NO       |
+----+----------+----------+----------+----------+----------+----------+

I want to compute a flag FINAL taking into account the last 6 weeks (period_) columns as such:

If 2 consecutive periods are YES then the final flag should be Y
If 3 consecutive periods are NO then the final flag should be N
If both are present (2 consecutive periods are YES and 3 consecutive periods are NO) the most recent condition should be taken into account (e.g. period_1 and period_2 are YES but period_3, period_4 and period_5 are NO, then final flag should be Y).
Final flag is empty if there are no consecutive periods that are YES or NO.

I have tried the following

data want;
    set have;
    length final $1.;

    if period_1 = 'YES' then
        do;
            if period_2 = 'YES' then
                window0= 'E';
            else window0 = '';
        end;

    if period_2 = 'YES' then
        do;
            if period_3 = 'YES' then
                window1 = 'E';
            else window1 = '';
        end;

    if period_3 = 'YES' then
        do;
            if period_4 = 'YES' then
                window2 = 'E';
            else Period2 = '';
        end;

    if period_4 = 'YES' then
        do;
            if period_5 = 'YES' then
                window3 = 'E';
            else window3 = '';
        end;

    if period_5 = 'YES' then
        do;
            if period_6 = 'YES' then
                window4 = 'E';
            else window4 = '';
        end;


    if window0 = 'E' OR window1 = 'E' OR window2 = 'E' then final = 'Y'; /* at least 2 consecutive YES */
    else if window3 = 'E' AND (period_1 = 'YES'  or period_2 = 'YES' ) then final = 'Y'; /* cannot be 3 consecutive NO so flag must be Y */
    else if window4 = 'E' AND (period_2 = 'YES'  or period_3 = 'YES' ) then final = 'Y'; /* same as above */
    else final = 'N';
    if cmiss(of period_:) in (5,6) then final = ''; /* need at least 2 non-empty period in order to compute final flag */
    if cmiss(of period_:) = 4 and whichc('YES', of period_:) in (0,1) then final = ''; /* need at least 2 YES if 4 periods missing to compute final */
run;

+----+----------+----------+----------+----------+----------+----------+-------+
| ID | PERIOD_1 | PERIOD_2 | PERIOD_3 | PERIOD_4 | PERIOD_5 | PERIOD_6 | FINAL |
+----+----------+----------+----------+----------+----------+----------+-------+
|  1 | NO       | NO       |          |          |          |          |       |
|  2 | NO       | NO       | NO       |          |          |          | N     |
|  3 | YES      | YES      | YES      |          |          |          | Y     |
|  4 | NO       | YES      | NO       | YES      | YES      | NO       | Y     |
|  5 | NO       | YES      | NO       | NO       | YES      | YES      | Y     |
|  6 | NO       | YES      | NO       | YES      | YES      |          | Y     |
|  7 | YES      |          |          |          |          |          |       |
|  8 | YES      | NO       | NO       | YES      |          |          | N     |
|  9 | YES      | YES      | NO       | NO       | NO       |          | Y     |
| 10 | NO       | NO       | NO       | YES      | YES      |          | N     |
+----+----------+----------+----------+----------+----------+----------+-------+

Not only does it not handle properly the case of id = 8, I wonder if there is a better way of tackling this problem, perhaps with window functions across columns?

Stu Sztukowski · Answer

Arrays will make this code easier to work with and more concise. We'll continually count consecutive values in a loop. If we hit the following conditions, we leave the loop:

2 consecutive YES's
3 consecutive NO's
A blank value

At the end we'll compare. If the consecutive count of YES was 2, then final = 'Y'. Otherwise if it was 3 consecutive NO's, then final = 'N'.

data want;
    set have;
    array period_[*] period_:;

    count_Y  = 0;   /* Counter for consecutive Yes */
    count_N  = 0;   /* Counter for consecutive No */

    do i = 1 to dim(period_);

        /* If either 2 consecutive Yes, 3 consecutive No, or the period is missing 
           then we're done */
        if(count_Y = 2 OR count_N = 3 OR missing(period_[i])) then leave;

        /* Reset the counter if the period differs */
        if(i > 1 and period_[i] NE period_[i-1]) then call missing(count_Y, count_N);

        /* Count the number of Yes and No's */
        if(period_[i] = 'YES') then count_Y+1;
            else if(period_[i] = 'NO') then count_N+1;
    end;

    /* Set the final flag */
    if(count_Y = 2) then final = 'Y';
        else if(count_N = 3) then final = 'N';

    drop count_Y count_N i;
run;

user7NPk · Answer

You can avoid looping by using functions. Use the CATX() function to combine the periods into one string. Use the FINDW() function to find the runs of interest.

data have ;
  infile cards dsd dlm='|' truncover;
  input id (period1-period6) (:$3.) final :$1.;
cards;
 1 | NO       | NO       |          |          |          |          |       |
 2 | NO       | NO       | NO       |          |          |          | N     |
 3 | YES      | YES      | YES      |          |          |          | Y     |
 4 | NO       | YES      | NO       | YES      | YES      | NO       | Y     |
 5 | NO       | YES      | NO       | NO       | YES      | YES      | Y     |
 6 | NO       | YES      | NO       | YES      | YES      |          | Y     |
 7 | YES      |          |          |          |          |          |       |
 8 | YES      | NO       | NO       | YES      |          |          | N     |
 9 | YES      | YES      | NO       | NO       | NO       |          | Y     |
10 | NO       | NO       | NO       | YES      | YES      |          | N     |
;

data want;
  set have;
  yes2 = findw(catx(',',of period1-period6),'YES,YES',',','e');
  no3 = findw(catx(',',of period1-period6),'NO,NO,NO',',','e');
  if yes2 then want='Y';
  else if no3 then want='N';
  if 0 < no3 < yes2 then want='N';
run;

Results

Computing a flag based on consecutive columns

2 Answers