keep line specific lines which contains a specific value by id

[ger15xxhcker]

Hi!

I have a test data set. If there is more than one identifier in the table and it is included 20900101 date then keep it that row. The rest are related to this ids delete it/drop it.

data;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2 
1 21000101 rte www
4 20190101 www rrr 
;
run;

so i'd like to this: (1. id contains 20900101 2. id contains 20900101 3. id contains 20900101 4. id not contains so keep it)

data;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20900101 jdk lao2
3 20900101 wer wes2 
4 20190101 www rrr 
;
run;

Thanks!

[hashman · Accepted solution]

@ger15xxhcker :

It looks like a typical job for the DoW-loop:

data test ;                              
  input id (date var1 var2) ($) ;        
  cards ;                                
1 20900101 abc abc2                      
2 20070102 cdf cdf2                      
2 20900101 jdk lao2                      
1 20110101 cds sxa                       
1 20150202 jui wsa                       
3 20900101 wer wes2                      
1 21000101 rte www                       
4 20190101 www rrr                       
;                                        
run ;                                    
                                         
proc sort data = test out = _test ;      
  by id ;                                
run ;                                    
                                         
data want (drop = _:) ;                  
  do _n_ = 1 by 1 until (last.id) ;      
    set _test ;                          
    by id ;                              
    if date ne "20900101" then continue ;
    output ;                             
    _flag = 1 ;                          
  end ;                                  
  do _n_ = 1 to _n_ ;                    
    set _test ;                          
    if not _flag then output ;           
  end ;                                  
run ;                                    

Kind regards

Paul D.

[ger15xxhcker]

Thanks for your quick help!

[Ksharp]

data have;
input id date$ var1$ var2$;
datalines;
1 20900101 abc abc2
2 20070102 cdf cdf2
2 20900101 jdk lao2
1 20110101 cds sxa
1 20150202 jui wsa
3 20900101 wer wes2 
1 21000101 rte www
4 20190101 www rrr 
;
run;
proc sql;
create table want as
select *
 from (select * from have group by id having count(*)>1)
  where date='20900101'
union all
select * from have group by id having count(*)=1;
quit;