topic Re: How to create columns with values based on occurence of words (from predefined lists) in a strin in SAS Programming

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

LuciaCekanakova — Thu, 10 Oct 2019 20:42:57 GMT

Hi all,

I'd like your help with this problem.

I have 2 tables:

I want to take each string in the STRINGS table, go over the list of values of each variable in VARIABLES table and if the value from this list occurs in the string, the value should be written into a new column that has the same name as the variable.

See the output table for better understanding:

The number of columns in VARIABLES table may vary, and the column names won't always be as regular as var1, var2...

Also, if the string contains more than 1 value fromt he list (e.g. the last observation in STRINGS table), it has to have assigned only 1 value in the new column, no matter which one (so in case of string "a b", it could be assigned either "a", "b" or "a b"). Important thing is for at least one value to be there without duplication of the original string in another row.

I am a beginner in coding in SAS, so the only way I could do this is by typing out a zillion of IF conditions for all the values for creating each new column. However, I'd like the solution to be automatic so that it doesn't matter how many variables there are, what are their names and how many values each one has.

Please, can anyone help me with this?

Here's the code for creating my data:

data strings; 
   input string $6.;
   datalines;                      
a x
aa l z
b k
b y m
c z
c x k
a b
;

data variables;
   infile datalines delimiter=',' dsd; 
   input var1 $ var2 $ var3 $;
   datalines;                      
a,k,x
b,l,y
a b,m,z
,n,
;

Thanks!

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

ballardw — Thu, 10 Oct 2019 21:20:51 GMT

Up until your value of 'a b' this looked moderately routine. However since you are looking for a single value of 'a' from the first line of your Strings data it is going to be a bit difficult to search for just the 'a' except when 'a b' is present in a generic sense.

I ask for clarity sake, does your actual working data have exactly 1 or many Variables values that include other variable values as substrings? If so then you may need to provide more information or a more complex example closer to your actual data.

Best might be to find some way to split up that String variable into the component parts that you need if possible so that you have values of 'a' alone instead of 'a x'.

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

hashman — Fri, 11 Oct 2019 03:25:43 GMT

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

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

ChrisNZ — Fri, 11 Oct 2019 04:12:06 GMT

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

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

Ksharp — Fri, 11 Oct 2019 11:40:57 GMT

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;

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

LuciaCekanakova — Fri, 11 Oct 2019 12:34:16 GMT

Hi ballardw,

My working data can contain many variable values that include other variable values as substrings.

Here's a more complex example:

You made me realize more things that are necessary here, so all in all I would like:

to output the longest match in case of multiple occurences of values from list in a string (e.g. "green beans Audi" outputs vegetables="green beans", not just "beans")
to make the scan case-insensitive (for string "bmw x5" the output is cars="BMW X5" in the same case as was in the variables table)
to make the code independent of the names of variables and their number - these can be changed and added, so it would be great to have a universal code that can deal with any names and any numebr of variables (such as cars, vegetables etc.)

@ballardw, @hashman how would you go about this?

In case you want to give it a try, here's the code for data:

data strings ;                                                                                                            
  input string $30.;                                                                                                      
  cards ;                                                                                                                 
Ford carrot
Honda beans
south america black beans
green beans Audi
x5 chili pepper lettuce
garlic north America
central europe bmw
lettuce onion X5
America pepper
X5 BMW Europe
Central East Europe
bmw X5 america
;                                                                                                                         
run ;  

data variables;
   infile datalines delimiter=',' dsd; 
   length cars $6 vegetables $12 regions $14;
   input cars $ vegetables $ regions $;
   datalines;                      
Audi,beans,America
BMW,black beans,Central Europe
BMW X5,carrot,East Europe
X5,chili pepper,Europe
Honda,green beans,North America
,lettuce,South America
,onion,
,pepper,
;
run;

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

Tom — Fri, 11 Oct 2019 12:44:16 GMT

I still don't get what you want to do with the multi-word strings in the VARIABLES table.

Do you only want to match 'BMW X5' to STRINGS that contain 'BMW X5' in that order and not to strings that have 'X5 BMW' or 'BMW model X5'?

Do you want to match also to strings that contain only 'BMW' or only 'X5'?

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

LuciaCekanakova — Fri, 11 Oct 2019 13:10:26 GMT

Do you only want to match 'BMW X5' to STRINGS that contain 'BMW X5' in that order and not to strings that have 'X5 BMW' or 'BMW model X5'? YES, exactly.

Do you want to match also to strings that contain only 'BMW' or only 'X5'? These are matched to values 'BMW' and 'X5' that are also in the variable list, but not because they are part of 'BMW X5' that is in this list. You basically always scan the string for the exact value in the variable list. And if the value list contains values 'BMW', 'X5' and 'BMW X5', a string like the one in observation #12 would match all of these, but I'd like the output to be only the longest exact match - which is 'BMW X5' in this case. In obs #10 it matches 'X5' and 'BMW', so 'BMW' goes into output.

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

Tom — Fri, 11 Oct 2019 13:12:02 GMT

In that case use the FINDW() function.

proc sql ;
  create table want as
    select string
         , case when (findw(string,cars,' ','sit')) then cars else ' ' end as cars
         , case when (findw(string,vegetables,' ','sit')) then vegetables else ' ' end as vegetables
         , case when (findw(string,regions,' ','sit')) then regions else ' ' end as regions
    from strings,variables
    having not missing(cats(calculated cars,calculated vegetables,calculated regions))
    order by 1,2,3,4
  ;
quit;

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

LuciaCekanakova — Fri, 11 Oct 2019 13:17:28 GMT

This program finds all the matches and creates an observation for each match. How do I make it to select only the longest match for each variable?

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

Tom — Fri, 11 Oct 2019 13:46:11 GMT

Probably easier to do each "variable" separately then. I added row numbers to your sample dataset to make it easier to see what is happening. And to give a tiebreaker when there are multiple

data strings ;
  input string $30.;
  snum+1;
cards;
Ford carrot
Honda beans
south america black beans
green beans Audi
x5 chili pepper lettuce
garlic north America
central europe bmw
lettuce onion X5
America pepper
X5 BMW Europe
Central East Europe
bmw X5 america
;

data variables;
   infile cards delimiter=',' dsd;
   length cars $6 vegetables $12 regions $14;
   input cars vegetables regions;
   vnum+1;
cards;
Audi,beans,America
BMW,black beans,Central Europe
BMW X5,carrot,East Europe
X5,chili pepper,Europe
Honda,green beans,North America
,lettuce,South America
,onion,
,pepper,
;

proc sql ;
  create table vegetables as
    select snum,string
         , lengthn(vegetables) as length
         , vnum,vegetables
    from strings left join variables
      on findw(string,vegetables,' ','sit')
    order by snum,string,length,snum
  ;
quit;

data vegetables;
  set vegetables;
  by snum;
  if last.snum;
run;

proc print data=vegetables width=min;
run;

Obs    snum    string                       length    vnum    vegetables

  1      1     Ford carrot                     6        3     carrot
  2      2     Honda beans                     5        1     beans
  3      3     south america black beans      11        2     black beans
  4      4     green beans Audi               11        5     green beans
  5      5     x5 chili pepper lettuce        12        4     chili pepper
  6      6     garlic north America            0        .
  7      7     central europe bmw              0        .
  8      8     lettuce onion X5                7        6     lettuce
  9      9     America pepper                  6        8     pepper
 10     10     X5 BMW Europe                   0        .
 11     11     Central East Europe             0        .
 12     12     bmw X5 america                  0        .

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

LuciaCekanakova — Fri, 11 Oct 2019 13:53:52 GMT

For some reason my output from your code looks different:

Any idea why? There are no errors or warnings in the log.

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

Tom — Fri, 11 Oct 2019 14:08:32 GMT

Copy the code again as I did change the ordering and the IF statement to make sure it is taking the longest match.

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

hashman — Fri, 11 Oct 2019 19:56:33 GMT

@LuciaCekanakova:

Well, in this case my program in its core remains the same, except:

In addition to STRING, each variable from VARIABLES has to be scanned
A provisions must be made for storing the (sub) strings from VARIABLES in their original (prop)case for further retrieval
Logic for selecting the longest matching substring has to be added

The rest is just diligent, even if somewhat tedious, coding:

data strings ;                                                                                                                          
  input string $30.;                                                                                                                    
  cards ;                                                                                                                               
Ford carrot                                                                                                                             
Honda beans                                                                                                                             
south america black beans                                                                                                               
green beans Audi                                                                                                                        
x5 chili pepper lettuce                                                                                                                 
garlic north America                                                                                                                    
central europe bmw                                                                                                                      
lettuce onion X5                                                                                                                        
America pepper                                                                                                                          
X5 BMW Europe                                                                                                                           
Central East Europe                                                                                                                     
bmw X5 america                                                                                                                          
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data variables;                                                                                                                         
   infile datalines delimiter=',' dsd;                                                                                                  
   length cars $6 vegetables $12 regions $14;                                                                                           
   input cars $ vegetables $ regions $;                                                                                                 
   datalines;                                                                                                                           
Audi,beans,America                                                                                                                      
BMW,black beans,Central Europe                                                                                                          
BMW X5,carrot,East Europe                                                                                                               
X5,chili pepper,Europe                                                                                                                  
Honda,green beans,North America                                                                                                         
,lettuce,South America                                                                                                                  
,onion,                                                                                                                                 
,pepper,                                                                                                                                
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                        
proc sql noprint ;                                                                                                                      
  select max (length) into :maxvlen from dictionary.columns                                                                             
  where libname="WORK" and memname="VARIABLES"                                                                                          
  ;                                                                                                                                     
quit ;                                                                                                                                  
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  set strings ;                                                                                                                         
  if _n_ = 1 then do ;                                                                                                                  
    length _s _z $ &maxvlen ;                                                                                                           
    dcl hash h () ;                                                                                                                     
    h.definekey ("_s") ;                                                                                                                
    h.definedata ("_z", "_i_") ;                                                                                                        
    h.definedone () ;                                                                                                                   
    do until (z) ;                                                                                                                      
      set variables end = z ;                                                                                                           
      array v cars vegetables regions ;                                                                                                 
      do over v ;                                                                                                                       
        if cmiss (v) then continue ;                                                                                                    
        do _i = 1 to countw (v) ;                                                                                                       
          _z = scan (v, _i) ;                                                                                                           
          h.ref (key: upcase (_z), data: _z, data: _i_) ;                                                                               
          do _j = _i + 1 to countw (v) ;                                                                                                
            _z = catx (" ", _z, scan (v, _j)) ;                                                                                         
            h.ref (key: upcase (_z), data: _z, data: _i_) ;                                                                             
          end ;                                                                                                                         
        end ;                                                                                                                           
      end ;                                                                                                                             
    end ;                                                                                                                               
  end ;                                                                                                                                 
  call missing (of v[*]) ;                                                                                                              
  do _i = 1 to countw (string) ;                                                                                                        
    _s = scan (string, _i) ;                                                                                                            
    if h.find (key: upcase (_s)) = 0 then if length (_z) > length (v) then v = _z ;                                                     
    do _j = _i + 1 to countw (string) ;                                                                                                 
      _s = catx (" ", _s, scan (string, _j)) ;                                                                                          
      if h.find (key: upcase (_s)) = 0 then if length (_z) > length (v) then v = _z ;                                                   
    end ;                                                                                                                               
  end ;                                                                                                                                 
run ;

Kind regards

Paul D.

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

LuciaCekanakova — Mon, 14 Oct 2019 12:54:28 GMT

Thank you! This is exactly what I need 🙂

The only thing I changed is that I created a macro variable with the list of variable names and then used it in array "v".

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

hashman — Mon, 14 Oct 2019 16:23:45 GMT

@LuciaCekanakova:

>I created a macro variable with the list of variable names and then used it in array "v"<

Given that you'd been striving to make the program as little hard coded as possible, this totally makes sense.

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

LuciaCekanakova — Fri, 25 Oct 2019 14:26:37 GMT

@hashman

Hi, I discovered 2 more things that aren't working as I expected:

When the VARIABLES table contains mulitple-word values, but the partial values are not in the value list (e.g. "South America" is there, but "South" by itself is not) they are also searched for in the STRINGS and if they are found, they are output in the new column (see the column regions in obs #2 and #8). I'd like to stop this from happening adn only search for the whole values if they have multiple-words.
In VARIABLES table it happens that the same value is in multiple columns (e.g. beans, carrot... - I added a new column with the general type of vegetable). When this occurs, the found value is output only in one variable column it was found in, it doesn't repeat in others (see columns vegetable and vegetablegeneral in obs #1,2,8,9). I'd like for these values to repeat in all the output column were they should be base don the original VARIABLES columns.

My output:

Desired output:

Please, do you have any tip on how to modify the code to achieve this?

The code so far with new data:

data strings ;                                                                                                                          
  input string $30.;                                                                                                                    
  cards ;                                                                                                                               
Ford carrot                                                                                                                             
Honda beans South Africa                                                                                                                             
south america black beans                                                                                                               
green beans Audi                                                                                                                        
x5 chili pepper                                                                                                   
garlic north America                                                                                                                    
central europe bmw                                                                                                                      
lettuce north onion X5                                                                                                                        
America pepper                                                                                                                     
X5 BMW Europe                                                                                                                           
Central East Europe                                                                                                                     
bmw X5 america                                                                                                                          
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data variables;                                                                                                                         
   infile datalines delimiter=',' dsd;                                                                                                  
   length cars $6 vegetablegeneral $7 vegetable $12 regions $14;                                                                                           
   input cars $ vegetablegeneral $ vegetable $ regions $;                                                                                                 
   datalines;                                                                                                                           
Audi,beans,beans,America                                                                                                                      
BMW,carrot,black beans,Central Europe                                                                                                          
BMW X5,lettuce,carrot,East Europe                                                                                                               
X5,onion,chili pepper,Europe                                                                                                                  
Honda,pepper,green beans,North America                                                                                                         
,,lettuce,South America                                                                                                                  
,,onion,                                                                                                                                 
,,pepper,                                                                                                                                
;                                                                                                                                       
run;                                                                                                                                    
                                                                                                                                        
proc sql noprint ;                                                                                                                      
  select max (length) into :maxvlen from dictionary.columns                                                                             
  where libname="WORK" and memname="VARIABLES"                                                                                          
  ;                                                                                                                                     
quit ;      

proc sql noprint;                                                                                                                      
  select name into :varlist separated by ' ' from dictionary.columns
  where libname="WORK" and memname="VARIABLES";
quit;   
                                                                                                                                        
data want (drop = _:) ;                                                                                                                 
  set strings ;                                                                                                                         
  if _n_ = 1 then do ;                                                                                                                  
    length _s _z $ &maxvlen ;                                                                                                           
    dcl hash h () ;                                                                                                                     
    h.definekey ("_s") ;                                                                                                                
    h.definedata ("_z", "_i_") ;                                                                                                        
    h.definedone () ;                                                                                                                   
    do until (z) ;                                                                                                                      
      set variables end = z ;                                                                                                           
      array v &varlist ;                                                                                                 
      do over v ;                                                                                                                       
        if cmiss (v) then continue ;                                                                                                    
        do _i = 1 to countw (v) ;                                                                                                       
          _z = scan (v, _i) ;                                                                                                           
          h.ref (key: upcase (_z), data: _z, data: _i_) ;                                                                               
          do _j = _i + 1 to countw (v) ;                                                                                                
            _z = catx (" ", _z, scan (v, _j)) ;                                                                                         
            h.ref (key: upcase (_z), data: _z, data: _i_) ;                                                                             
          end ;                                                                                                                         
        end ;                                                                                                                           
      end ;                                                                                                                             
    end ;                                                                                                                               
  end ;                                                                                                                                 
  call missing (of v[*]) ;                                                                                                              
  do _i = 1 to countw (string) ;                                                                                                        
    _s = scan (string, _i) ;                                                                                                            
    if h.find (key: upcase (_s)) = 0 then if length (_z) > length (v) then v = _z ;                                                     
    do _j = _i + 1 to countw (string) ;                                                                                                 
      _s = catx (" ", _s, scan (string, _j)) ;                                                                                          
      if h.find (key: upcase (_s)) = 0 then if length (_z) > length (v) then v = _z ;                                                   
    end ;                                                                                                                               
  end ;                                                                                                                                 
run ;

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

hashman — Sat, 26 Oct 2019 19:00:49 GMT

@LuciaCekanakova: Will take a look when I get a piece of free quality time.

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

LuciaCekanakova — Tue, 26 Nov 2019 09:08:30 GMT

Hi @hashman ,

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

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

hashman — Sun, 01 Dec 2019 03:56:47 GMT

@LuciaCekanakova: Sorry, totally swamped now.