Data step-List Customers touch failure and belong to our pop

[Ronein]

Hello

I want to get CustomerID list that meet following criteria:

1- InNov 2022 they beling to popultion 7

2- Between Jun2022 and OCT2022 they touch failure

The code below calculate correctly variable pop7  but doesn't calculate correctly variable Touch_FAILURE.

May anyone help please?

data have;
format ddate date9.;
input CustID ddate :date9.  pop Ind_Fail;
cards;
1 '01APR2022'd 7 0
1 '01MAY2022'd 7 0
1 '01JUN2022'd 7 0
1 '01JUL2022'd 7 0
1 '01AUG2022'd 7 0
1 '01SEP2022'd 7 0
1 '01OCT2022'd 7 0
1 '01NOV2022'd 7 0
2 '01APR2022'd 7 0
2 '01MAY2022'd 7 0
2 '01JUN2022'd 7 1
2 '01JUL2022'd 7 0
2 '01AUG2022'd 7 0
2 '01SEP2022'd 7 0
2 '01OCT2022'd 7 0
2 '01NOV2022'd 7 1
3 '01APR2022'd 7 0
3 '01MAY2022'd 7 0
3 '01JUN2022'd 7 1
3 '01JUL2022'd 7 1
3 '01AUG2022'd 7 1
3 '01SEP2022'd 7 1
3 '01OCT2022'd 7 0
3 '01NOV2022'd 7 0
4 '01APR2022'd 8 0
4 '01MAY2022'd 8 0
4 '01JUN2022'd 8 1
4 '01JUL2022'd 8 0
4 '01AUG2022'd 8 0
4 '01SEP2022'd 8 0
4 '01OCT2022'd 8 0
4 '01NOV2022'd 8 0
5 '01APR2022'd 7 0
5 '01MAY2022'd 7 0
5 '01JUN2022'd 7 1
5 '01JUL2022'd 7 0
5 '01AUG2022'd 7 0
5 '01SEP2022'd 7 0
5 '01OCT2022'd 7 0
5 '01NOV2022'd 8 0
;
Run;



data want2;
do until(last.CustID);
set have(KEEP=CustID pop  ddate Ind_Fail);
by CustID;
if (ddate='01NOV2022'd and pop=7) then POP7=1;
if ddate in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd) and Ind_Fail=1 then Touch_FAILURE=1;
end; 
keep  CustID POP7 Touch_FAILURE;
run;

[PaigeMiller]

... but doesn't calculate correctly variable Touch_FAILURE.

What are the correct values for TOUCH_FAILURE?

[Ronein]

0
1
1
0
0

[Tom]

You never set either variable to zero.   You might add this after the BY statement.

if first.custid then do;
  pop7=0;
  touch_failure=0;
end;

Why do the last two customers get zeros?

If it is because they are not in the POP7 group then add this statement after the DO loop.

  touch_failure=pop7 and touch_failure;

[Ronein]

Can you please show the full code?

In my code I see result only for customer 6

data have;
format ddate date9.;
input CustID ddate :date9.  pop Ind_Fail;
cards;
1 '01APR2022'd 7 0
1 '01MAY2022'd 7 0
1 '01JUN2022'd 7 0
1 '01JUL2022'd 7 0
1 '01AUG2022'd 7 0
1 '01SEP2022'd 7 0
1 '01OCT2022'd 7 0
1 '01NOV2022'd 7 0
2 '01APR2022'd 7 0
2 '01MAY2022'd 7 0
2 '01JUN2022'd 7 1
2 '01JUL2022'd 7 0
2 '01AUG2022'd 7 0
2 '01SEP2022'd 7 0
2 '01OCT2022'd 7 0
2 '01NOV2022'd 7 1
3 '01APR2022'd 7 0
3 '01MAY2022'd 7 0
3 '01JUN2022'd 7 1
3 '01JUL2022'd 7 1
3 '01AUG2022'd 7 1
3 '01SEP2022'd 7 1
3 '01OCT2022'd 7 0
3 '01NOV2022'd 7 0
4 '01APR2022'd 8 0
4 '01MAY2022'd 8 0
4 '01JUN2022'd 8 1
4 '01JUL2022'd 8 0
4 '01AUG2022'd 8 0
4 '01SEP2022'd 8 0
4 '01OCT2022'd 8 0
4 '01NOV2022'd 8 0
5 '01APR2022'd 7 0
5 '01MAY2022'd 7 0
5 '01JUN2022'd 7 1
5 '01JUL2022'd 7 0
5 '01AUG2022'd 7 0
5 '01SEP2022'd 7 0
5 '01OCT2022'd 7 0
5 '01NOV2022'd 8 0
6 '01APR2022'd 7 0
6 '01MAY2022'd 7 0
6 '01JUN2022'd 8 1
6 '01JUL2022'd 8 1
6 '01AUG2022'd 8 1
6 '01SEP2022'd 8 1
6 '01OCT2022'd 8 1
6 '01NOV2022'd 7 0
;
Run;

data want;
do until(last.CustID);
set have(KEEP=CustID pop  ddate Ind_Fail);
by CustID;

if first.custid then do;
  pop7=0;
  touch_failure=0;
end;

if (ddate='01NOV2022'd and pop=7) then POP7=1;
if ddate in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd) and Ind_Fail=1 then _Touch_FAILURE_=1;
touch_failure=pop7 and _Touch_FAILURE_;
end; 
run;

[Tom]

Your logic MIGHT work if the values are sorted so that POP7 is set before all of the places where TOUCH_FAILURE is created.  But it would be much easier to just wait until you get to end of the observations for this customer.  So after the END of the DO loop.

data have;
  input CustID ddate :date9.  pop Ind_Fail;
  format ddate date9.;
cards;
1 01APR2022 7 0
1 01MAY2022 7 0
1 01JUN2022 7 0
1 01JUL2022 7 0
1 01AUG2022 7 0
1 01SEP2022 7 0
1 01OCT2022 7 0
1 01NOV2022 7 0
2 01APR2022 7 0
2 01MAY2022 7 0
2 01JUN2022 7 1
2 01JUL2022 7 0
2 01AUG2022 7 0
2 01SEP2022 7 0
2 01OCT2022 7 0
2 01NOV2022 7 1
3 01APR2022 7 0
3 01MAY2022 7 0
3 01JUN2022 7 1
3 01JUL2022 7 1
3 01AUG2022 7 1
3 01SEP2022 7 1
3 01OCT2022 7 0
3 01NOV2022 7 0
4 01APR2022 8 0
4 01MAY2022 8 0
4 01JUN2022 8 1
4 01JUL2022 8 0
4 01AUG2022 8 0
4 01SEP2022 8 0
4 01OCT2022 8 0
4 01NOV2022 8 0
5 01APR2022 7 0
5 01MAY2022 7 0
5 01JUN2022 7 1
5 01JUL2022 7 0
5 01AUG2022 7 0
5 01SEP2022 7 0
5 01OCT2022 7 0
5 01NOV2022 8 0
;
data want2;
  do until(last.CustID);
    set have;
    by CustID;
    if (ddate='01NOV2022'd and pop=7) then POP7=1;
    if ddate in ('01OCT2022'd,'01SEP2022'd ,'01AUG2022'd,'01JUL2022'd,'01JUN2022'd)
       and Ind_Fail=1 then Touch_FAILURE=1;
  end; 
  pop7 = 0 or pop7;
  touch_failure=touch_failure and pop7;
  keep custid pop7 touch_failure;
run;

Result

       Cust             Touch_
Obs     ID     POP7    FAILURE

 1       1       1        0
 2       2       1        1
 3       3       1        1
 4       4       0        0
 5       5       0        0

[mkeintz]

I think a self-merge might be slightly simpler than a SET inside a DO group:

data want (keep=custid pop7 touchfail);
  merge have
        have (where=(ddate='01nov2022'd and pop=7)  in=p7)
        have (where=(('01jun2022'd<=ddate<='30oct2022'd) and ind_fail=1) in=fail);
  by custid;
  if first.custid ;
  pop7=p7;
  touchfail=fail and p7;
run;

[PaigeMiller]

---

@Ronein wrote:
0
1
1
0
0

---

Thanks.

This doesn't match your description in words in the initial problem statement.

Please from now on, when you are getting the wrong results, you need to specifically show us the right results, as we cannot tell from your words and we cannot tell from your code. I know in the past you have provided the WANT data set, we need that all the time if the issue is that your results are not what you want.