BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
seohyeonjeong
Obsidian | Level 7

Hi, everyone!

 

I really need your help, and I will really appreciate any answers. 

 

I have a data table like this.

 

(TABLE A)

ContractNoNumFlagPrice01...
ABC0001

1

33100...
ABC0001133100...
ABC0001133100...
ABC0001-1-33100...
ABC0001-1-33100...
ABC000214000...
ABC00021500...
ABC0002-1-4000...
ABC000316600...
ABC000317000...

 

I want to delete the rows with the same absolute value and different (negative-positive) signs in the same ContractNo.

 

The output data should seem like the below.

 

(TABLE B)

ContractNoNumFlagPrice01...
ABC0001133100...
ABC00021500...
ABC000316600...
ABC000317000...

 

 

Most of the codes are written on PROC SQL, so hopefully, I can solve this problem with PROC SQL.

However, base SAS is also acceptable.

 

Thank you in advance. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

SQL is not right tool for this scenario . Try data step.

 

data have;
infile cards expandtabs;
input ContractNo $	NumFlag	Price01;
cards;
ABC0001	1 33100	 ...
ABC0001	1	33100	...
ABC0001	1	33100	...
ABC0001	-1	-33100	...
ABC0001	-1	-33100	...
ABC0002	1	4000	...
ABC0002	1	500	 ...
ABC0002	-1	-4000	...
ABC0003	1	6600	...
ABC0003	1	7000
;

data have1(index=(x=(ContractNo Price01))) 
     have2(index=(x=(ContractNo Price01)) drop=NumFlag);
 set have;
 if NumFlag=1 then output have1;
 if NumFlag=-1 then do;Price01=-Price01;output have2;end;
run;
data want;
 ina=0;inb=0;
 merge have1(in=ina) have2(in=inb);
 by ContractNo Price01;
 if not ina or not inb;
run;

 

 

View solution in original post

4 REPLIES 4
Shmuel
Garnet | Level 18

data temp;

 set have;

      NumFlag = abs(NumFlag);

      Price01 = abs(Price01);

run;

proc sort data=taemp out=want NODUPKEY; 

    by ContarctNumber NumFlag Price01;

run;

seohyeonjeong
Obsidian | Level 7

 

Thank you for your answer!

 

I believe using NODUPKEY is not a good way in this case.

If there are only two rows with the same value of Price01 & different signs in the same ContractNo,

I have to delete all of them. 

 

Ksharp
Super User

SQL is not right tool for this scenario . Try data step.

 

data have;
infile cards expandtabs;
input ContractNo $	NumFlag	Price01;
cards;
ABC0001	1 33100	 ...
ABC0001	1	33100	...
ABC0001	1	33100	...
ABC0001	-1	-33100	...
ABC0001	-1	-33100	...
ABC0002	1	4000	...
ABC0002	1	500	 ...
ABC0002	-1	-4000	...
ABC0003	1	6600	...
ABC0003	1	7000
;

data have1(index=(x=(ContractNo Price01))) 
     have2(index=(x=(ContractNo Price01)) drop=NumFlag);
 set have;
 if NumFlag=1 then output have1;
 if NumFlag=-1 then do;Price01=-Price01;output have2;end;
run;
data want;
 ina=0;inb=0;
 merge have1(in=ina) have2(in=inb);
 by ContractNo Price01;
 if not ina or not inb;
run;

 

 

seohyeonjeong
Obsidian | Level 7

Thank you so much!!

 

I tested it and it seems working!

I have to test this on my real data tmr though.

 

You literally saved my day. Thanks alot.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 4 replies
  • 594 views
  • 1 like
  • 3 in conversation