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.
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;
data temp;
set have;
NumFlag = abs(NumFlag);
Price01 = abs(Price01);
run;
proc sort data=taemp out=want NODUPKEY;
by ContarctNumber NumFlag Price01;
run;
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.
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;
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.
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!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.