handling duplicates

[yoyong555]

Hi everyone.

I have the following data step:

data file;

input ID $ group;

cards;

1     0

1     1

2     0

3     

3     0

4     1

5

;

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

5                     

Thanks.

[novinosrin]

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;

[hashman · Accepted solution]

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