DATA Step, Macro, Functions and more

Cap a Numeric Column

Reply
Contributor
Posts: 37

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.

Respected Advisor
Posts: 3,124

Re: Cap a Numeric Column

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

Haikuo

Contributor
Posts: 37

Re: Cap a Numeric Column

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

PROC Star
Posts: 7,363

Re: Cap a Numeric Column

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;

Super Contributor
Posts: 474

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.

Ask a Question
Discussion stats
  • 4 replies
  • 227 views
  • 0 likes
  • 4 in conversation