topic Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column in SAS Programming

How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Columns

johnjinkim — Thu, 15 Jun 2017 14:40:48 GMT

data have;
input person date product paid ;
1 1/25/15 apple $43

1 1/25/15 apple -$43

1 1/25/15 apple $50

2 1/30/15 orange $50

3 1/31/15 apple $55
;

Would want to remove lines:

1 1/25/15 apple $43

1 1/25/15 apple -$43

since it is the same person #, date, product, and price is retracted. Would want to keep person #1 apple $50 as well as persons 2 and 3.

Thank you!

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

Reeza — Thu, 15 Jun 2017 14:29:19 GMT

Could you summarize by customer, date, product and sum the amount. If amount is 0 then delete?

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

RW9 — Thu, 15 Jun 2017 14:31:56 GMT

Here is one example:

data have;
  input person date $ product $ paid;
datalines;
1 1/25/15 apple 43
1 1/25/15 apple -43
2 1/30/15 orange 50
3 1/31/15 apple 55
;
run;

data want;
  set have;
  by person date product;
  retain tot;
  if first.product then tot=paid;
  if not(first.product) and tot+paid=0 then delete;
run;

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

johnjinkim — Thu, 15 Jun 2017 14:43:03 GMT

Good thought - have updated initial question as it does not appear for scenarios where there are other lines for that person that I would want to keep.

Truly trying to remove only retractions with the same positive and negative value.

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

johnjinkim — Thu, 15 Jun 2017 14:42:30 GMT

Thank you for the response. I believe this only deletes the negative retraction, but I would want to delete both the negative and positive paid with the same value.

so both of the below lines should be deleted.

1 1/25/15 apple 43
1 1/25/15 apple -43

Thanks again.

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

Reeza — Thu, 15 Jun 2017 14:57:31 GMT

Use a SQL self join to the data on the condition that it not equal a negative value

proc sql;

select a.*

from have as a

left join have as b

on a.id=b.Id and a.date=b.date and a.item =b.item

and a.amount ne -1*b.amount;

quit;

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

Ksharp — Sat, 17 Jun 2017 02:54:56 GMT

There are some problem in my code.

data have;
input person date : $20. product $ paid : dollar.;
cards;
1 1/25/15 apple $43
1 1/25/15 apple $-43
1 1/25/15 apple $50
2 1/30/15 orange $50
3 1/31/15 apple $55
;
data a;
 set have(where=(paid gt 0));
run;
data b;
 set have(where=(paid lt 0));
 paid=-paid;
run;
proc sort data=a;
 by  person date product paid;
run;
proc sort data=b;
 by  person date product paid;
run;

data want;
 ina=0;inb=0;
 merge a(in=ina)  b(in=inb);
 by person date product paid;
 if not (ina and inb) then do;
  if inb then paid=-paid;
  output;
 end;
run;

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

Haikuo — Fri, 16 Jun 2017 17:46:50 GMT

I won't be overly confident on order of events ,

data have;
input person date : $20. product $ paid : dollar.;
cards;
1 1/25/15 apple $50
1 1/25/15 apple $43
1 1/25/15 apple $-43
2 1/30/15 orange $50
3 1/31/15 apple $55
;
data want;
 ina=0;inb=0;
 merge have(where=(paid lt 0) in=ina) have(where=(paid gt 0) in=inb);
 by person date product;
 if not (ina and inb);
run;

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

Haikuo — Fri, 16 Jun 2017 18:14:33 GMT

At first glance, this looks rather straightforward, however, after discovering the question could involve both sequencing and no-sequencing process, It seems that Hash object is to be utilized to deliver a more robust solution.

data have;
input person date : $20. product $ paid : dollar.;
cards;
1 1/25/15 apple $50
1 1/25/15 apple $43
1 1/25/15 apple $-43
2 1/30/15 orange $50
3 1/31/15 apple $55
;

data want;
if _n_=1 then do;
  if 0 then set have;
    declare hash h(dataset:'have(rename=(paid=_p) where=(_p<0))', multidata:'y'); 
	h.definekey('person','date','product');
	h.definedata(all:'y');
	h.definedone();
	call missing (_p);
  end;
set have (where=(paid>=0));
_rc=h.find();
if paid eq -1*_p then do;
_rc=h.removedup();
delete;
end;

drop _:;
run;

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

johnjinkim — Mon, 19 Jun 2017 11:56:58 GMT

Thank you! This gets me to exactly what I need - apologies if the smaller level detalis/intracicies were not clearly stated. Seems as though it is fairly straightforward initially, but there are certainly smaler level detalis that this accounts for such as the sequencing.

Re: How to Delete Obs w/ Positive and Negative Values based on Same Values in Other Variables/Column

johnjinkim — Mon, 19 Jun 2017 12:00:17 GMT

Thanks for the response. This creates duplicates of certain values ... performing

proc sort data=have nodupkey ;

by id date item ;

run ;

does get me to the output I specified, though not accounting for smaller level detalis that Haikuo's solution accounts for. Thanks again.