How to create columns with values based on occurence of words (from predefined lists) in a strin

[LuciaCekanakova]

Hi @hashman ,

any luck with the free time/the code yet? 🙂

[hashman]

@LuciaCekanakova: Sorry, totally swamped now. 

[hashman]

@LuciaCekanakova:

You can store VARIABLES in a lookup table and then, in principle, go two ways:

• Go through the table sequentially for every record from STRINGS and use the FIND function to look into STRING for every value in the table. But with this approach, you'll have to iterate through the whole table for every record in STRINGS in O(N) time.
• Parse STRING into all possible left-to-right substrings and search for each of them in the lookup table in O(1) time.  

The first approach is simpler since you don't have to write code to parse STRING, and yet I still find it the other one better because it's likely to yield much better performance; so this is what is adopted in the code below. 

data strings ;                                                                                                                          
  input string $6. ;                                                                                                                    
  cards ;                                                                                                                               
a x                                                                                                                                     
aa l z                                                                                                                                  
b k                                                                                                                                     
b y m                                                                                                                                   
c z                                                                                                                                     
c x k                                                                                                                                   
a b                                                                                                                                     
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data variables ;                                                                                                                        
  input (var1-var3) (& :$3.) ;                                                                                                          
  cards ;                                                                                                                               
a    k  x                                                                                                                               
b    l  y                                                                                                                               
a b  m  z                                                                                                                               
.    n  .                                                                                                                               
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want (keep = string var:) ;                                                                                                        
  set strings ;                                                                                                                         
  if _n_ = 1 then do ;                                                                                                                  
    dcl hash h () ;                                                                                                                     
    h.definekey ("_s") ;                                                                                                                
    h.definedata ("_i_") ;                                                                                                              
    h.definedone () ;                                                                                                                   
    do until (z) ;                                                                                                                      
      set variables end = z ;                                                                                                           
      array v var: ;                                                                                                                    
      do over v ;                                                                                                                       
        if cmiss (v) = 0 then h.add (key:v, data:_i_) ;                                                                                 
      end ;                                                                                                                             
    end ;                                                                                                                               
  end ;                                                                                                                                 
  _s = string ;                                                                                                                         
  call missing (_s, of var:) ;                                                                                                          
  do _i = 1 to countw (string) ;                                                                                                        
    _s = scan (string, _i) ;                                                                                                            
    if h.find (key: _s) = 0 then v = _s ;                                                                                               
    do _j = _i + 1 to countw (string) ;                                                                                                 
      _s = catx (" ", _s, scan (string, _j)) ;                                                                                          
      if h.find (key: _s) = 0 then v = _s ;                                                                                             
    end ;                                                                                                                               
  end ;                                                                                                                                 
run ;                                                   

Note that, as @ballardw has mentioned, the fact that any of VAR1-VAR3 can contain more than one component (in your case,  "a b" in VAR1) throws a kind of monkey wrench into the task: Without it, parsing STRING would be a simple scan, rather than the nested scan requiring the concatenation to account for all possible left-to-right substrings without losing any possible matches with VARIABLES. But specs are specs.

Kind regards

Paul D.

[ChrisNZ]

I am unsure what the actual data looks like, but you are likely to have multiple matches.

You may want to split the logic into two steps:

1. Match the data

2. Decide on the matches to keep.

This does the first step:

proc sql;
  create table WANT as
  select STRING
        ,ifc(findw(STRING,VAR1,' ','r'), VAR1,' ') as VAR1 length=8
        ,ifc(findw(STRING,VAR2,' ','r'), VAR2,' ') as VAR2 length=8
        ,ifc(findw(STRING,VAR3,' ','r'), VAR3,' ') as VAR3 length=8
   from STRINGS,VARIABLES
   having lengthn(VAR1) | lengthn(VAR2) | lengthn(VAR3)
  order by STRING;
quit;

STRING	VAR1	VAR2	VAR3
a x	a		x
aa l z		l
aa l z			z
b k		k
b k	b
b y m	b		y
b y m		m
c z			z
c x k		k	x
a b	a
a b	b
a b	a b

[Ksharp]

This could give you a start .

data strings ;                                                                                                                          
  input string $6. ;                                                                                                                    
  cards ;                                                                                                                               
a x                                                                                                                                     
aa l z                                                                                                                                  
b k                                                                                                                                     
b y m                                                                                                                                   
c z                                                                                                                                     
c x k                                                                                                                                   
a b                                                                                                                                     
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data variables ;                                                                                                                        
  input (var1-var3) (& :$3.) ;                                                                                                          
  cards ;                                                                                                                               
a    k  x                                                                                                                               
b    l  y                                                                                                                               
a b  m  z                                                                                                                               
.    n  .                                                                                                                               
;                                                                                                                                       
run ;  
proc sql;
create table want as
select 	a.*,var1,var2,var3
 from strings as a left join variables(keep=var1) as b 
  on findw(a.string,strip(b.var1),' ')
left join variables(keep=var2) as c 
  on findw(a.string,strip(c.var2),' ')
left join variables(keep=var3) as d 
  on findw(a.string,strip(d.var3),' ')
;
quit;