topic Re: Duplicates values only in SAS Programming

Duplicates values only

Solly7 — Thu, 16 Jan 2020 10:05:20 GMT

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

2 300 3 100

2 -300 3 100

3 100

4 450

Re: Duplicates values only

ErikLund_Jensen — Fri, 17 Jan 2020 15:58:37 GMT

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;

Re: Duplicates values only

hashman — Sat, 18 Jan 2020 04:09:13 GMT

@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.

Re: Duplicates values only

Solly7 — Thu, 23 Jan 2020 13:07:30 GMT

thank you