topic Re: Delete the lines that are annulled in SAS Programming

Delete the lines that are annulled

mansour_ib_sas — Tue, 18 Dec 2018 10:52:56 GMT

Hello,

I have the test table and I want to have the table test1.

The objectif is to delete the lines that are annulled

Thank you for your help

data test;
input var1 $ var2;
cards;
A   200
A   100
A  -200
A   200
B   100
;
run;

data test1;
input var1 $ var2;
cards;
A   200
A   100
B   100
;
run;

Re: Delete the lines that are annulled

Jagadishkatam — Tue, 18 Dec 2018 11:02:04 GMT

data test;
input var1 $ var2;
cards;
A   200
A   100
A  -200
A   200
B   100
;
run;

proc sort data=test out=want nodupkey;
by var1 var2;
where var2>0;
run;

Re: Delete the lines that are annulled

RW9 — Tue, 18 Dec 2018 11:04:29 GMT

That's not enough information. What indicates annulment? Is it only when the previous record exactly matches this one, but minus? What about:

a 100

1 -200

Is this an annullment?

What are the ordering variables here?

This works, not sure it will cover all eventualities though:

data test;
  input var1 $ var2;
cards;
A   200
A   100
A  -200
A   200
B   100
;
run;

data want;
  set test;
  retain newdiff flag;
  by var1;
  if first.var1 then do;
    newdiff=var2;
    flag=0;
  end;
  else if newdiff+var2=0 then flag=1;
  if flag=1 then do;
    flag=2;
    delete;
  end;
  if flag=2 then do;
    flag=0;
    delete;
  end;
run;

Re: Delete the lines that are annulled

Ksharp — Tue, 18 Dec 2018 12:54:53 GMT

data test;
  input var1 $ var2;
cards;
A   200
A   100
A  -200
A   200
B   100
;
run;

data p(index=(x=(var1 var2))) n(index=(x=(var1 var2)));
 set test;
 if var2>0 then output p;
  else do;var2=-var2;output n;end;
run;
data want;
 ina=0;inb=0;
 merge p(in=ina) n(in=inb);
 by var1 var2;
 if not ina or not inb;
run;