Counting with conditions

[Sk1_SAS]

Hi team,

This is my base, the table is already sorted by ID and flag.

data temp;
infile DATALINES dsd missover;
input DATE:DDMMYY10. value ID FLAG;
FORMAT DATE DDMMYY10.;
CARDS;
15/05/2020,9360,1,1 
22/05/2020,9360,1,1 
07/08/2020,9880,1,1 
21/08/2020,9100,1,1 
21/08/2020,9880,1,2 
25/08/2020,9800,1,3 
26/08/2020,9100,1,4 
24/11/2020,9569,2,1 
17/12/2020,9600,2,1 
17/12/2020,9600,2,2 
17/12/2020,9600,2,3 
17/12/2020,9600,2,4 
17/12/2020,9600,2,5 
17/12/2020,9600,2,6 
17/12/2020,9600,2,7 
27/05/2020,9020,3,1 
10/06/2020,9519,3,1 
16/06/2020,9553,3,1 
18/06/2020,9553,3,2 
25/06/2020,9500,3,1 
10/07/2020,9498,3,1 
13/07/2020,9498,3,2 
16/07/2020,9075,3,3 
17/07/2020,9663,3,4 
17/07/2020,9368,3,5 
20/07/2020,9663,3,6 
21/07/2020,9663,3,7 
22/07/2020,9600,3,8 
;
run;

First i need to SUM value, if the flag is consecutive and need to be consecutive to 4 or more. 

And if is possible, i need to count if the flag is consecutive and need to be consecutive to 4 or more.

The result that i need:

Thanks!!!!

[mkeintz]

You've shown what you want for runs constant flag valules of length 4   (sum of value over the 4 obs).  And you've shown what you want for shorter runs (carry forward the previous sum).

Questions:

1. If flag=1 the only value that can have such runs?
2. What if a run has a length >4?

Additional question:  Does flag always proceed in ascending order.  I.e. can flag=1 reappear after flag>1?  Oops, I see the answer.

[Sk1_SAS]

i need to count and sum if the FLAG is consecutive to 4 or more, like this:

1 2 3 4 

1 2 3 4 5

and etc...

if the flag is consecutive but less than 4, like:

1 2 3

1 2

does not need to be done.

Yes, flag always proceed in ascending order, and that example can happen.

[yabwon · Accepted solution]

I think this will do:

data temp;
infile DATALINES dsd missover;
input DATE:DDMMYY10. value ID FLAG;
FORMAT DATE DDMMYY10.;
CARDS;
15/05/2020,9360,1,1 
22/05/2020,9360,1,1 
07/08/2020,9880,1,1 
21/08/2020,9100,1,1 
21/08/2020,9880,1,2 
25/08/2020,9800,1,3 
26/08/2020,9100,1,4 
24/11/2020,9569,2,1 
17/12/2020,9600,2,1 
17/12/2020,9600,2,2 
17/12/2020,9600,2,3 
17/12/2020,9600,2,4 
17/12/2020,9600,2,5 
17/12/2020,9600,2,6 
17/12/2020,9600,2,7 
27/05/2020,9020,3,1 
10/06/2020,9519,3,1 
16/06/2020,9553,3,1 
18/06/2020,9553,3,2 
25/06/2020,9500,3,1 
10/07/2020,9498,3,1 
13/07/2020,9498,3,2 
16/07/2020,9075,3,3 
17/07/2020,9663,3,4 
17/07/2020,9368,3,5 
20/07/2020,9663,3,6 
21/07/2020,9663,3,7 
22/07/2020,9600,3,8 
;
run;

data temp;
  set temp;
  if flag=1 then grp+1; /* create groups */ 
run;

data want;
 do _N_ = 1 by 1 until(last.grp);
   set temp;
   by grp;
   sum + value;
   cnt + 1;
 end;

 if cnt < 4 then call missing(sum, cnt);

 do _N_ = 1 to _N_;
  set temp;
  output;
 end;

 call missing(sum, cnt);
run;

proc print;
run;

Bart

[Sk1_SAS]

Very nice.

Thanks.