topic Re: Logic help required? in SAS Programming

Logic help required?

bnarang — Wed, 30 Jan 2013 13:48:38 GMT

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 -8 0

Thank you so much

Re: Logic help required?

DBailey — Wed, 30 Jan 2013 14:47:21 GMT

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.

Re: Logic help required?

bnarang — Thu, 31 Jan 2013 04:24:06 GMT

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 3

1 -3

2 1

2 2

2 -4

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

ID Money Money_cal

1 2 2

1 3 0

1 -3 0

2 1 1

2 2 0

2 -4 0

if this makes sense.

Re: Logic help required?

sknguru — Thu, 31 Jan 2013 07:01:38 GMT

Try this approach

data d1;

infile datalines;

input id money;

seq = _n_;

datalines;

1 2

1 3

1 -3

2 1

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;