I have a data set and it contains Claim sfx and amount , I need to remove the duplicates claim , but the problem here is there could be different sfx in the same claim , if its different for the same claim then I need to keep that claim
Claim sfx amount
100 23 897
100 23 897
101 25 789
101 89 463 Need to keep this Claim because of different sfx
102 56 789
102 56 789 Need to delete this becase of same sfx
data have;
input Claim sfx amount ;
cards;
100 23 897
100 23 897
101 25 789
101 89 463
102 56 789
102 56 789
;
run;
proc sql;
create table want as
select *
from have
group by claim
having count(distinct sfx) ne 1;
quit;
SAS can do this fairly easily:
proc sort data=have nodupkey;
by claim sfx;
run;
However, you should also consider what is the right outcome when CLAIM is the same, SFX is the same, but AMOUNT changes. perhaps you should go with:
proc sort data=have nodupkey;
by claim sfx amount;
run;
data have;
input Claim sfx amount ;
cards;
100 23 897
100 23 897
101 25 789
101 89 463
102 56 789
102 56 789
;
run;
proc sql;
create table want as
select *
from have
group by claim
having count(distinct sfx) ne 1;
quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.