Duplicates values only

[Solly7]

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

[ErikLund_Jensen]

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;

[Solly7]

thank you

[hashman · Accepted solution]

@Solly7:

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.