Pyrite | Level 9

## Delete the lines that are annulled

Hello,

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

The objectif  is to delete the lines that are annulled

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Delete the lines that are annulled

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;
3 REPLIES 3
Amethyst | Level 16

## Re: Delete the lines that are annulled

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;
Thanks,
Jag
Diamond | Level 26

## Re: Delete the lines that are annulled

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

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;

Super User

## Re: Delete the lines that are annulled

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;
Discussion stats
• 3 replies
• 497 views
• 0 likes
• 4 in conversation