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.
Hi, Where does the '3750' in new column come from?
Haikuo
The person A's running sum caps out at 10,000 once the money gets to that value.
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.