Logic help required?

[bnarang]

Hi Experts

I have a dataset which have the Id and money he spent. If for any instance, he got some negative money, then it needs to be checked from downwards and needs to be negated like the variable (Cal_money).Can you help me with the logic please:

ID      Money    Cal_money

1          10               10

1           20               0

1           10               0

1           -30              0

2           5                 0

2           -5                0

2            4                0

2            4                0

2            -8              0

Thank you so much

[DBailey]

I'm not quite sure I follow what you need...It sounds like you want a cal_money to be a running total of money transactions.

Assuming the data is sorted by id, and transaction order...

data want;

set have;

by id;

retain Running_Balance 0;

if first.id then Running_Balance=0;

Running_Balance = Running_Balance - money;

run;

results in this dataset:

1	10	10	-10
1	20	0	-30
1	10	0	-40
1	-30	0	-10
2	5	0	-5
2	-5	0	0
2	4	0	-4
2	4	0	-8
2	-8	0	0

If this isn't what you're looking for, you'll need to provide some clarification.

[bnarang]

Hi

For one of my project, I have to write a piece of code which can negate any money made by ID (account). Let's say my dataset looks like:

ID    Money

1        2

1        2 

1        3

1        -3

2        1

2         2

2         2

2        -4

This is my input dataset. My output dataset should look like:

ID    Money     Money_cal

1        2             2

1        2             2

1        3             0

1        -3            0 

2        1            1

2         2            0

2         2            0

2        -4            0

if this makes sense.

[sknguru · Accepted solution]

Try this approach

data d1;

  infile datalines;

  input id money;

  seq = _n_;

datalines;

1 2

1 2

1 3

1 -3

2 1

2 2

2 2

2 -4

;

run;

proc sort data=d1;

  by id descending seq;

run;

data d2;

  set d1;

  by id;

  retain mny 8.;

  if first.id then mny= 0;

  mny = mny + money;

  if mny < 0 then cal_money = 0; else cal_money = mny; * in else, if don't need to accumulate the positive, use money instead of mny;

  drop mny;

run;

proc sort data=d2 out=d3(drop=seq);

  by id seq;

run;