Calcite | Level 5

## Cap a Numeric Column

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?

NameMonthMoney

New Column

a11000same as money column
a23200same as money column
a350same as money column
a40same as money column
a50same as money column
a60same as money column
a82000same as money column
a940003750
a1100
a1236000
b1450
b2700
b36000
b4100
b70
b86200

Thanks.

4 REPLIES 4
Onyx | Level 15

## Re: Cap a Numeric Column

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

Haikuo

Calcite | Level 5

## Re: Cap a Numeric Column

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

Opal | Level 21

## Re: Cap a Numeric Column

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;

Barite | Level 11

## Re: Cap a Numeric Column

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.

Discussion stats
• 4 replies
• 1491 views
• 0 likes
• 4 in conversation