BookmarkSubscribeRSS Feed
AAWTomHanks
Calcite | Level 5

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
Haikuo
Onyx | Level 15

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

Haikuo

AAWTomHanks
Calcite | Level 5

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

art297
Opal | Level 21

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
Barite | Level 11

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 1491 views
  • 0 likes
  • 4 in conversation