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!!!!
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
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:
Additional question: Does flag always proceed in ascending order. I.e. can flag=1 reappear after flag>1? Oops, I see the answer.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.