Cap a Numeric Column

[AAWTomHanks]

I have a table like the one below and need to create a new column. The new column is like the money column except for each person I need the money to max out at 10,000. Any ideas?

Name	Month	Money	New Column
a	1	1000	same as money column
a	2	3200	same as money column
a	3	50	same as money column
a	4	0	same as money column
a	5	0	same as money column
a	6	0	same as money column
a	8	2000	same as money column
a	9	4000	3750
a	11	0	0
a	12	3600	0
b	1	450
b	2	700
b	3	6000
b	4	100
b	7	0
b	8	6200

Thanks.

[Haikuo]

Hi, Where does the '3750' in new column come from?

Haikuo

[AAWTomHanks]

The person A's running sum caps out at 10,000 once the money gets to that value.

[art297]

How about something like:

data want (drop=total);

  set have;

  by name;

  retain total;

  if first.name then total=0;

  if total ge 10000 then new_column=0;

  else if total+money ge 10000 then do;

    new_column=10000-total;

    total+money;

  end;

  else do;

    total+money;

    new_column=money;

  end;

run;

[DanielSantos]

Try this.

%let _MAX=10000;

data z;

set x;

by NAME;

drop _:;  * loose temp vars;

if first.NAME then _SUM=0;  * reset sum var;

_SUM+MONEY;  *sum;

if _SUM gt &_MAX then do;  * is it maxed?;

   NEW_COL=MONEY-mod(_SUM,&_MAX);

   _SUM=&_MAX;  * max sum;

end;

else NEW_COL=MONEY;

run;

Cheers from Portugal.