Hi,
i need help in finding and viewing only duplicates by policy_no and amount
Data Have Data Want
policy_no Amount policy_no Amount
1 500 1 500
1 500 1 500
2 300 3 100
2 -300 3 100
3 100
3 100
4 450
SQL can be your friend, regardless of whether the input is sorted or not:
data have;
input policy_no amount ;
cards ;
1 500
1 500
2 300
2 -300
3 100
3 100
4 450
;
run ;
proc sql ;
create table want as select * from have natural join (select policy_no, amount from have group 1, 2 having count (*) > 1) ;
quit ;
Kind regards
Paul D.
Hi @Solly7
You can get your result in a data step with both policy_no Amount as by-vars. It requires data to be sorted by policy_no Amount. Output only if the same Amount occurs more than once for the same policy_no:
Data Have;
input policy_no Amount;
cards;
1 500
1 500
2 300
2 -300
3 100
3 100
4 450
;
run;
proc sort data=have;
by policy_no Amount;
run;
data want; set have;
by policy_no Amount;
if not (first.Amount and last.Amount);
run;
thank you
SQL can be your friend, regardless of whether the input is sorted or not:
data have;
input policy_no amount ;
cards ;
1 500
1 500
2 300
2 -300
3 100
3 100
4 450
;
run ;
proc sql ;
create table want as select * from have natural join (select policy_no, amount from have group 1, 2 having count (*) > 1) ;
quit ;
Kind regards
Paul D.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.