topic Re: How to cancel out observations that sum up to 0? in SAS Data Science

How to cancel out observations that sum up to 0?

Alex_mcc — Fri, 24 Jul 2015 07:31:36 GMT

HI,

For days now I've been trying to clean up my data set.

Example:

OBS Number amount count

1 1 45 1

2 1 -45 2

3 1 45 3

4 2 30 1

5 2 25 2

6 2 -30 3

7 2 25 4

...

I was thinking of doing a do loop to start from first.number to last.number to exclude the observations that sum up to zero (obs 2, 3). Sometimes the negative amount is a sum of two previous/following numbers. I also tried deduping by squaring the amount and concatenating it with the number, doing count. However it doesn't work in all cases (obs 5,7 would be wrongly excluded too). So my required data set once cleaned would be:

OBS Number amount

1 1 45

5 2 25

7 2 25

Please help!

Thanks in advance!

Alex

Re: How to cancel out observations that sum up to 0?

Ksharp — Fri, 24 Jul 2015 12:48:14 GMT

There are lots of logic you need to consider about .

Code: Program

data have;
input Number   amount ;
cards;   
  1 45  
  1 -45  
  1 45  
  2 30  
  2 25  
  2 -30  
  2 25  
;
run;
data temp1 temp2;
 set have;
if amount lt 0 then do;
 amount=abs(amount);
 output temp2;
end;
else output temp1;
run;
proc sort data=temp1;by Number amount;run;
proc sort data=temp2;by Number amount;run;
data want;
ina=0;inb=0;
merge temp1(in=ina) temp2(in=inb);
by Number amount;
if not ina then amount=-amount;
if not ina or not inb;
run;

Xia Keshan

Re: How to cancel out observations that sum up to 0?

Bryan — Fri, 24 Jul 2015 18:41:44 GMT

Here is how I was able to generate the answer.

data DTTBL_00;

infile datalines delimiter='09'x dsd firstobs=1;

input Itm Qntty;

datalines;

1 45

1 -45

1 45

2 30

2 25

2 -30

2 25

;

run;

Data DTTBL_01;

Set DTTBL_00;

Cntr=_N_;

Run;

PROC SQL;

CREATE TABLE DTTBL_02 AS

SELECT t1.Itm,

t1.Qntty,

t1.Cntr

FROM WORK.DTTBL_01 t1

WHERE t1.Qntty < 0;

QUIT;

%macro BldGrph2(mcrItm, mcrQntty, mcrCntr);

%put =====> &mcrItm, &mcrQntty, &mcrCntr;

PROC SQL outobs=1;

CREATE TABLE DTTBL_03 AS

SELECT t1.Itm,

t1.Qntty,

t1.Cntr

FROM WORK.DTTBL_01 t1

WHERE t1.Qntty = abs(&mcrQntty);

QUIT;

PROC SQL;

delete from DTTBL_01

where Itm = (select Itm from DTTBL_03)

and Qntty = (select Qntty from DTTBL_03)

and Cntr = (select Cntr from DTTBL_03)

;

PROC SQL;

delete from DTTBL_01

where Itm = &mcrItm

and Qntty = &mcrQntty

and Cntr = &mcrCntr

;

%mend BldGrph2;

data _Null_;

set DTTBL_02;

Call EXECUTE('%BldGrph2('||Itm||','||Qntty||','||Cntr||')');

run;

Data RESULTS;

Set DTTBL_01;

run;

Re: How to cancel out observations that sum up to 0?

Fugue — Fri, 24 Jul 2015 19:23:48 GMT

Do you need to keep the original OBS number? If OBS is a sequence counter, and the observations are in the order they occurred, then wouldn't OBS 3 be the one you want to keep instead of OBS 1 (OBS 2 is a reversal of OBS 1)?

If you don't need the OBS #, why not just sum the amounts by Number?

proc sql;

create table want as

select number, sum ( amount ) as sum_amt

from have

group by number

/* optional - exclude totals that equal zero */

having sum ( amount ) ^= 0

order by 1

;

quit;

Re: How to cancel out observations that sum up to 0?

Reeza — Fri, 24 Jul 2015 19:41:30 GMT

That looks like a transactional data where you're reversing a transaction. There's usually some ID that tells you what type each transaction is and how to obtain the 'last' transaction for each transaction.

Re: How to cancel out observations that sum up to 0?

Fugue — Fri, 24 Jul 2015 20:59:50 GMT

I get that - it looks like it is some type of transactional data on its face. But my question is why keep the OBS if it isn't needed? Just gettiing back to basics . . . simpler is better.

Now if the OP wants to keep OBS because it is important to further analysis, then fine. But, the data is not necessarily structured in such a way as to know whether subsequent events relate to prior events. We certainly don't have the business context to know. We are assuming based on limited information.

Further, in the example provided, the OP wants to keep OBS 1. Sequentially, however, OBS 2 appears to be a reversal of OBS 1. So, shouldn't OBS 3 be the row to keep if OBS is important?

Re: How to cancel out observations that sum up to 0?

Reeza — Fri, 24 Jul 2015 21:27:26 GMT

My point was doing it in this manner is most likely not the best manner, and I was responding to the OP.

Given the provided information, your answer/comments are perfectly valid.

Re: How to cancel out observations that sum up to 0?

Fugue — Fri, 24 Jul 2015 21:32:30 GMT

I missed that you were responding to the OP. Gotcha - just having a conversation here - hope I'm not coming across as adversarial or critical. I've been off forums for awhile, just back, and trying to re-engage.

Re: How to cancel out observations that sum up to 0?

Alex_mcc — Tue, 28 Jul 2015 11:16:56 GMT

Hi Xia Keshan,

Thanks for your help.

I do like your approach, however it doesn't work in all cases (I probably should have given different/extra examples). On few occasions I have a partial refund (in this case I'd like the refund to be deleted, the amount of the refund taken away from the closest positive amount within the number), refund which is a sum of previous payments and sum of two refunds is an amount. Oh and where I say refund I mean negative amount. Easier to explain on the below example. Is there a way of amending your code so it considers all below cases? (thanks again!!!)

I don't need to keep obs, it is here only for explanation purposes.

OBS Number amount count

1 1 45 1

2 1 -45 2

3 1 45 3

4 2 30 1

5 2 25 2

6 2 -30 3

7 2 25 4

8 3 50 1

9 3 60 2

10 3 -17 3

11 4 100 1

12 4 100 2

13 4 100 3

14 4 -200 4

15 5 80 1

16 5 40 2

17 5 -20 3

18 5 -20 4

And I'd like the results to be:

OBS Number amount

1 1 45

5 2 25

7 2 25

8 3 50 1

9 3 43 2 /************************************ partial refund 60-17=43************************/

11 4 100 1 /****************************** obs 12,13 cancel out obs 14***************************/

15 5 80 1 /******************************obs 16 cancel out obs 17 and 18***************************/

;

Thanks for you help!

Re: How to cancel out observations that sum up to 0?

Ksharp — Tue, 28 Jul 2015 14:16:07 GMT

Code: Program

data have;
input Number   amount count ;
cards;   
  1 45 1  
  1 -45 2
  1 45 3
  2 30 1
  2 25 2  
  2 -30 3
  2 25 4
  3 50 1
  3 60 2
  3 -17 3
  4 100 1
  4 100 2
  4 100 3
  4 -200 4
  5 80 1
  5 40 2
  5 -20 3
  5 -20 4
;
run;
data temp1 temp2;
 set have;
if amount lt 0 then do;
 amount=abs(amount);
 output temp2;
end;
else output temp1;
run;
proc sort data=temp1;by Number amount;run;
proc sort data=temp2;by Number amount;run;
data x;
ina=0;inb=0;
merge temp1(in=ina) temp2(in=inb);
by Number amount;
if not ina then amount=-amount;
if not ina or not inb;
run;
proc sort data=x;by Number descending count;run;
data want;
 set x;
 by number;
 retain found;
 if first.number then do;sum=0;found=0;end;
 sum+amount;
 if not found and sum gt 0 then do;amount=sum;found=1; end;
 if sum gt 0;
 drop sum found;
run;
proc sort data=want;by number count;run;

Re: How to cancel out observations that sum up to 0?

Alex_mcc — Wed, 29 Jul 2015 10:07:49 GMT

Hi Xia Keshan,

Thanks for your help! Nearly there Although this code works for majority of cases, it still doesn't work for all of them. Especially when there are other transactions within 'number' following the refund. I was testing my data on your code (below). Also, sometimes when it does work and the 'sum' should be zero, there appears to be observation with 1.421085E-14. Is that due to formatting?

Also for number 7:

7 840.42 1

7 14.54 2

7 753.65 3

7 31.6 4

7 -768.19 5

Is there a way of it checking (within a number) for sum = 0, so I'd want

7 840.42 1

7 31.6 4

left (as 14.54+753.65-768.19=0). But if it's not going to work for every single case, then don't worry.

Thanks again!

data have;

input Number amount count ;

cards;

1 45 1

1 -45 2

1 45 3

2 30 1

2 25 2

2 -30 3

2 25 4

3 50 1

3 60 2

3 -17 3

4 100 1

4 100 2

4 100 3

4 -200 4

5 80 1

5 35.9 2

5 188.66 3

5 -224.56 4

5 20 5

6 57.57 1

6 28.38 2

6 57.57 3

6 -85.95 4

6 28.38 5

7 840.42 1

7 14.54 2

7 753.65 3

7 31.6 4

7 -768.19 5

8 116.39 1

8 82.09 2

8 116.39 3

8 -198.48 4

8 82.09 5

8 116.39 6

8 -198.48 7

;

run;

Re: How to cancel out observations that sum up to 0?

Ksharp — Wed, 29 Jul 2015 12:40:24 GMT

Why it is

14.54+753.65-768.19=0

NOT

31.6+753.65-768.19=0

Re: How to cancel out observations that sum up to 0?

Alex_mcc — Wed, 29 Jul 2015 12:59:25 GMT

Because it doesn't sum up to 0.

31.6+753.65-768.19=17.06 (not 0)

There must have been another payment before the refund

Re: How to cancel out observations that sum up to 0?

Ksharp — Wed, 29 Jul 2015 13:11:38 GMT

So what is your output ?

Output can't be ?

7 840.42 1

7 14.54 2

7 17.06 3

Do you want pick up the most likely value for that one ?

Re: How to cancel out observations that sum up to 0?

Ksharp — Wed, 29 Jul 2015 13:14:53 GMT

NO, You can't do that, unless you can enumerate all the combination of positive value .

Re: How to cancel out observations that sum up to 0?

Alex_mcc — Wed, 29 Jul 2015 13:18:53 GMT

what I mean is, where there is a negative value (refund) I want that obs to cancel out with others. In case of obs 7

7 840.42 1

7 14.54 2

7 753.65 3

7 31.6 4

7 -768.19 5

I'd want to have this left:

7 840.42 1

7 31.6 4

But that's not as important. The code you gave me previously works perfect, apart from when there are other obs withing that number. Any ideas?

Re: How to cancel out observations that sum up to 0?

Ksharp — Wed, 29 Jul 2015 13:48:02 GMT

Idea is enumerate all the combination of position value to see if the sum of combination could refund the negative.

But That is too complicated .

First , check the one value to see if it could refund negative value.

Second, check the all the combination of two to see if its sum could refund

Third, check the all the combination of three to see if its sum could refund

...............

Do you really want this ?

Re: How to cancel out observations that sum up to 0?

Ksharp — Wed, 29 Jul 2015 13:49:33 GMT

And if all these combination could not refund to zero , what are you going to do ? Negative value minus WHAT ?

Re: How to cancel out observations that sum up to 0?

Alex_mcc — Thu, 30 Jul 2015 08:04:51 GMT

If it wouldn't refund to zero then there'd be partial refund. That's why I though to do a loop within a number.

Re: How to cancel out observations that sum up to 0?

Ksharp — Thu, 30 Jul 2015 13:04:35 GMT

OBS Number amount count

1 1 95 1

2 1 -45 2

3 1 45 3

You want this , right ? You don't need to consider the order ?

OBS Number amount count

1 1 95 1