BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bnarang
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
sknguru
Calcite | Level 5

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

3 REPLIES 3
DBailey
Lapis Lazuli | Level 10

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.

bnarang
Calcite | Level 5

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 3 replies
  • 785 views
  • 0 likes
  • 3 in conversation