topic handling duplicates in SAS Programming

handling duplicates

yoyong555 — Mon, 02 Sep 2019 02:18:34 GMT

Hi everyone.

I have the following data step:

data file;

input ID $ group;

cards;

1 0

1 1

2 0

3 0

4 1

;

run;

I would like to recode the var group. If an ID has a duplicate (eg. 1 and 3), the row value will take on the maximum group.

The column would look like

ID Group Group_r

1 0 1

1 1 1

2 0 0

3 0

3 0 0

4 1 1

Thanks.

Re: handling duplicates

novinosrin — Mon, 02 Sep 2019 02:25:08 GMT

data file;

input ID $ group;

cards;
1     0
1     1
2     0
3     .
3     0
4     1
5     .
;
run;

proc sql;
create table want as
select *,max(group) as group_r
from file
group by id;
quit;

Re: handling duplicates

hashman — Mon, 02 Sep 2019 02:49:46 GMT

@yoyong555:

Methinks the simplest is:

data have ;                                                                 
  input ID $ group ;                                                        
  cards ;                                                                   
1     0                                                                     
1     1                                                                     
2     0                                                                     
3     .                                                                     
3     0                                                                     
4     1                                                                     
5     .                                                                     
;                                                                           
run ;                                                                       
                                                                            
proc sql ;                                                                  
  create table want as select *, max (group) as group_r from have group id ;
quit ;

If you want the DATA step instead, could be:

data want ;                               
  set have (in=h1) have (in=h2) ;         
  by id ;                                 
  retain group_r ;                        
  if first.id then group_r = . ;          
  if h1 then group_r = group_r max group ;
  if h2 ;                                 
run ;

The alternative below is a bit more verbose, but it may make more sense logically (depending on whom you ask):

data want ;                        
  do _n_ = 1 by 1 until (last.id) ;
    set have ;                     
    by ID ;                        
    group_r = group_r max group ;  
  end ;                            
  do _n_ = 1 to _n_ ;              
    set have ;                     
    output ;                       
  end ;                            
run ;

Kind regards

Paul D.