topic Re: Cap a Numeric Column in SAS Programming

Cap a Numeric Column

AAWTomHanks — Mon, 06 Aug 2012 18:26:07 GMT

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.

Re: Cap a Numeric Column

Haikuo — Mon, 06 Aug 2012 18:40:34 GMT

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

Haikuo

Re: Cap a Numeric Column

art297 — Mon, 06 Aug 2012 19:08:44 GMT

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;

Re: Cap a Numeric Column

AAWTomHanks — Mon, 06 Aug 2012 19:09:05 GMT

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

Re: Cap a Numeric Column

DanielSantos — Fri, 10 Aug 2012 07:54:14 GMT

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.