DATA Step, Macro, Functions and more

Logic help required?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 86
Accepted Solution

Logic help required?

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


Accepted Solutions
Solution
‎01-31-2013 02:01 AM
New Contributor
Posts: 4

Re: Logic help required?

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;

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: Logic help required?

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:

11010-10
1200-30
1100-40
1-300-10
250-5
2-500
240-4
240-8
2-800

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

Frequent Contributor
Posts: 86

Re: Logic help required?

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.

Solution
‎01-31-2013 02:01 AM
New Contributor
Posts: 4

Re: Logic help required?

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 205 views
  • 0 likes
  • 3 in conversation