topic Re: keep line specific lines which contains a specific value by id in SAS Programming

keep line specific lines which contains a specific value by id

ger15xxhcker — Wed, 10 Jul 2019 21:42:20 GMT

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!

Re: keep line specific lines which contains a specific value by id

hashman — Wed, 10 Jul 2019 21:52:36 GMT

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

Re: keep line specific lines which contains a specific value by id

ger15xxhcker — Wed, 10 Jul 2019 21:55:34 GMT

Thanks for your quick help!

Re: keep line specific lines which contains a specific value by id

Ksharp — Thu, 11 Jul 2019 12:15:55 GMT

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;