topic Re: How to delete rows with the same absolute value in PROC SQL? in SAS Programming

How to delete rows with the same absolute value in PROC SQL?

seohyeonjeong — Mon, 06 Jun 2022 11:38:43 GMT

Hi, everyone!

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

I have a data table like this.

(TABLE A)

ContractNo	NumFlag	Price01	...
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	...

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)

ContractNo	NumFlag	Price01	...
ABC0001	1	33100	...
ABC0002	1	500	...
ABC0003	1	6600	...
ABC0003	1	7000	...

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.

Re: How to delete rows with the same absolute value in PROC SQL?

Shmuel — Mon, 06 Jun 2022 12:04:13 GMT

data temp;

set have;

NumFlag = abs(NumFlag);

Price01 = abs(Price01);

run;

proc sort data=taemp out=want NODUPKEY;

by ContarctNumber NumFlag Price01;

run;

Re: How to delete rows with the same absolute value in PROC SQL?

Ksharp — Mon, 06 Jun 2022 12:10:12 GMT

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;

Re: How to delete rows with the same absolute value in PROC SQL?

seohyeonjeong — Mon, 06 Jun 2022 12:46:49 GMT

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.

Re: How to delete rows with the same absolute value in PROC SQL?

seohyeonjeong — Mon, 06 Jun 2022 13:02:48 GMT

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.