BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

data temp;
input group x;
cards;
1 23
1 34
1 5
1 15
2 78
2 92
2 45
2 89
2 34
2 76
3 31
4 23
4 12
;
run;

data cusum;
set temp;
by group;
if first.group then total=0;
total+x;
if total >= 35 then flag='Y';
run;

How to reset the total equal to x once the threshold is reached.
In the above example,id 1 reaches threshold (>=35) after second iteration(23+34).
so I would want the flag='Y at that record only and then for third iteration total must be equal to x and then cumulative addition
must be performed. the thrid and foruth iteration values of total must  5 and 20

7 REPLIES 7
art297
Opal | Level 21

Depending upon what you actually want, you may only have to add one extra condition to your if statement:

data cusum;

  set temp;

  by group;

  if first.group or total >= 35 then total=0;

  total+x;

  if total >= 35 then flag='Y';

run;

SteveNZ
Obsidian | Level 7

Hiya, try:

data cusum;

set temp;

by group;

if first.group then total=0;

total+x;

if total >= 35 then do ;

flag='Y';

total = 0 ;

end ;

run;

SASPhile
Quartz | Level 8

It is setting total to 0 at the threshold.but I want the total to be actual total and the next observation should be equal to value of x

SteveNZ
Obsidian | Level 7

Try:

data cusum (drop = A B) ;

set temp;

by group;

if first.group then A=0;

A+x;

if A >= 35 then do ;

flag='Y';

B = A ;

A = 0 ;

end ;

total = sum(A,B) ;

run;

SASPhile
Quartz | Level 8

Thanks Steve.

art297
Opal | Level 21

: Did you try the code that I suggested?

SASPhile
Quartz | Level 8

Thanks Art. I tried and works.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1036 views
  • 0 likes
  • 3 in conversation