BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

 

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

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
Jagadishkatam
Amethyst | Level 16
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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

 

Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 542 views
  • 0 likes
  • 4 in conversation