topic Re: Data step-List Customers touch failure and belong to our pop in SAS Programming

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

Ronein — Wed, 04 Sep 2024 17:34:05 GMT

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;

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

PaigeMiller — Wed, 04 Sep 2024 17:53:02 GMT

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

What are the correct values for TOUCH_FAILURE?

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

Ronein — Wed, 04 Sep 2024 18:20:13 GMT

0
1
1
0
0

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

Tom — Wed, 04 Sep 2024 18:33:10 GMT

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;

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

Ronein — Wed, 04 Sep 2024 18:47:52 GMT

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;

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

Tom — Wed, 04 Sep 2024 19:12:05 GMT

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

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

mkeintz — Thu, 05 Sep 2024 02:48:00 GMT

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;

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

PaigeMiller — Thu, 05 Sep 2024 13:56:36 GMT

@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.