topic Re: Using FINDW in a DO loop to flag keywords in a string in SAS Programming

Using FINDW in a DO loop to flag keywords in a string

jl13491 — Wed, 26 Jan 2022 17:43:50 GMT

Hi everyone,

I'm about to cry because of SAS! I feel like I'm going crazy! Here's my issue: I want to create variables to flag specified keywords that are found in a TOPIC variable. Here's the code that I have:

data test ;
    format topics $char256. ; 
    infile datalines dsd dlm=":";
    input topics $ ;
    datalines;
PHYSICAL COMPUTING  BASIC COMPUTER TEACHING  PROJECTS USING WORD PROCESSING AND DATA ANALYZING  POWERPOINT
GAME DESIGN
WEB DESIGN AND DEVELOPMENT MOBILE APP DEVELOPMENT
PHYSICAL COMPUTING MAKER ACTIVITIES WEB DESIGN AND DEVELOPMENT TEXT-BASED PROGRAMMING
;
run;

%macro test() ; 
DATA test ;
    SET test ;

    array keywords [6] $ t1-t6 ('PHYSICAL', 'ACTIVITES', 'GAME', 'DATA', 'CODING', 'MOBILE') ;
    array varnames [6] physcomp makeract gamedesign datasci coding mobiledev ; 

    %do i = 1 %to 6 ;
        if findw(topics, keywords[&i]) > 0 then varnames[&i] = 1 ;
        else varnames[&i] = 0 ;
    %end ; 

RUN ;
%mend ; 

%test() ;

which produces the following data:

PHYSICAL COMPUTING BASIC COMPUTER TEACHING PROJECTS USING WORD PROCESSING AND DATA ANALYZING POWERPOINT 1 0 0 0 0 0 GAME DESIGN 0 0 0 0 0 0 WEB DESIGN AND DEVELOPMENT MOBILE APP DEVELOPMENT 0 0 0 0 0 0 PHYSICAL COMPUTING MAKER ACTIVITIES WEB DESIGN AND DEVELOPMENT TEXT-BASED PROGRAMMING 1 0 0 0 0 0

Huh? Why isn't GAME in the second observation being recognized? Or MOBILE in the third observation? Why is PHYSICAL the only keyword being recognized? Please help, I'm at my wit's end!!!

Re: Using FINDW in a DO loop to flag keywords in a string

jl13491 — Wed, 26 Jan 2022 17:46:53 GMT

Also, the order of the keyword/varnames list doesn't matter -- even if I put game design first, it still only recognizes physical computing.

Re: Using FINDW in a DO loop to flag keywords in a string

Astounding — Wed, 26 Jan 2022 18:02:47 GMT

I can't test and explore right now, but here's an idea to examine.

When you create character variables with an array the default length is 8. So "GAME" is not the value "GAME " is the value. I'm not sure how FINDW handles that, but you can easily find out. Also note that some of your keywords are longer than 8 characters long, so the values are being chopped off.

Re: Using FINDW in a DO loop to flag keywords in a string

FreelanceReinh — Wed, 26 Jan 2022 18:05:18 GMT

Hi @jl13491 and welcome to the SAS Support Communities!

Don't worry, FINDW can really be confusing. Only minutes before I read your post I struggled with that function in a different context ...

I see three issues:

Length of array keywords is the default 8, so will truncate longer initial values. Use
```
 array keywords [6] $10 ...
```
Typo "ACTIVITES" in the second initial value.
The trailing blanks of the keywords must be trimmed. Otherwise they become part of the search term (which is a problem for all keywords that are shorter than the defined length -- and the reason why "PHYSICAL" worked well with length 8). Use
```
findw(topics, trim(keywords[&i]))
```

Re: Using FINDW in a DO loop to flag keywords in a string

jl13491 — Wed, 26 Jan 2022 18:06:36 GMT

I LOVE YOU ASTOUNDING! Thank you for your big brain, you have cured me! Using TRIM(keywords[&i]) solved the issue!

Re: Using FINDW in a DO loop to flag keywords in a string

jl13491 — Wed, 26 Jan 2022 18:07:50 GMT

Thank you, Freelance, for the extremely thorough solution! You, like Outstanding, have saved me from SAS's horrific clutches! ❤️

Re: Using FINDW in a DO loop to flag keywords in a string

jl13491 — Wed, 26 Jan 2022 18:08:34 GMT

Astounding*

Re: Using FINDW in a DO loop to flag keywords in a string

PaigeMiller — Wed, 26 Jan 2022 18:20:52 GMT

In addition to the above correct comments, you probably want to avoid using macros and macro variables when they are not necessary. Arrays contain all the features you need to do this, without macros.

DATA test1;
    SET test ;
    array keywords [6] $ 10 _temporary_ ('PHYSICAL', 'ACTIVITIES', 'GAME', 'DATA', 'CODING', 'MOBILE') ;
    array varnames [6] physcomp makeract gamedesign datasci coding mobiledev; 
    do i = 1 to 6 ; 
        if findw(topics, trim(keywords[i]))> 0 then varnames[i] = 1 ; else varnames[i]=0;   
    end ; 
    drop i;
RUN ;

Re: Using FINDW in a DO loop to flag keywords in a string

JackHamilton — Wed, 26 Jan 2022 18:53:38 GMT

Unnecessarily putting code inside a macro is so common that there must be a flaw in how the macro language is taught.

If you had dozens or hundreds of keywords instead of half a dozen, it might be better to put the keywords and corresponding variable names into a SAS data set, and use a macro to generate the two ARRAY statements. DOSUBL might be even easier.

Also, you might want to look at the R or T modifier in the FINDW function as an alternative to using the TRIM function.

Re: Using FINDW in a DO loop to flag keywords in a string

PaigeMiller — Wed, 26 Jan 2022 19:03:55 GMT

@JackHamilton said:

If you had dozens or hundreds of keywords instead of half a dozen, it might be better to put the keywords and corresponding variable names into a SAS data set, and use a macro to generate the two ARRAY statements. DOSUBL might be even easier.

Why do you say that?

Also, you might want to look at the R or T modifier in the FINDW function as an alternative to using the TRIM function.

I should have thought of that!

Re: Using FINDW in a DO loop to flag keywords in a string

JackHamilton — Wed, 26 Jan 2022 23:33:42 GMT

Personally, I find it easier to maintain a long narrow list than a short wide list, and if the list is coming from an external source it's usually easier to read it in a data step than in a macro. Data-driven instead of manually maintained array statements.

Using DOSUBL lets me do the processing right where the list of values is needed, so I don't have to create global macro variables. Depending on how much parameterization is needed, it might or might not be better than a macro.

Re: Using FINDW in a DO loop to flag keywords in a string

JackHamilton — Wed, 26 Jan 2022 23:50:51 GMT

/* Create a SAS data set containing words to be looked for and  */
/* their associated variables.                                  */

data mylist;
    input @1 lookfor $32.
         @34 varname $32. ;
datalines4;
PHYSICAL                         physcomp
ACTIVITIES                       makeract
GAME                             gamedesign
DATA                             datasci 
CODING                           coding
MOBILE                           mobiledev 
;;;;

/*** Method 1 ***/
/* Create global macro variables containing expanded parts of array statements.  */
%global lookfor_list varname_list list_length;

proc sql noprint;
    select 
        quote(trim(lookfor), "'") ,
        trim(varname) 
    into
        :LOOKFOR_LIST separated by ' ' ,
        :VARNAME_LIST separated by ' ' 
    from 
        mylist;
    %let list_length = &SQLOBS. ;
quit;

%put INFO: &=LOOKFOR_LIST ;
%put INFO: &=VARNAME_LIST ;
%put INFO: &=LIST_LENGTH  ;

data _null_; 
    array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ;
    array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; 
    put keywords[1]=;
run;

%symdel lookfor_list varname_list list_length;

/*** Method 2 ***/
/* create global macro variables containing complete array statements.  */

%macro make_code();

    %global lookfor_code varname_code;
    %local lookfor_list varname_list list_length;
    
    proc sql noprint;
        select 
            quote(trim(lookfor), "'") ,
            trim(varname) 
        into
            :LOOKFOR_LIST separated by ' ' ,
            :VARNAME_LIST separated by ' ' 
        from 
            mylist;
        %let list_length = &SQLOBS. ;
    quit;
        
    %let lookfor_code = array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ;
    %let varname_code = array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; 
    
    %put INFO: &=LOOKFOR_CODE;
    %put INFO: &=VARNAME_CODE;

%mend make_code;
%make_code();

data _null_; 
    &LOOKFOR_CODE. ;
    &VARNAME_CODE. ; 
    put keywords[1]=;
run;

%symdel lookfor_code varname_code;    
   
   
/*** Method 3 ***/
/* Create a macro that inserts code generated from SAS data set,    */
/* without causing a section break.  In real life, this would be    */
/* parameter-driven.                                                */

%macro use_dosub();

    %local lookfor_code varname_code;
    %local lookfor_list varname_list list_length;

    %let dosubl_rc = %sysfunc(dosubl(%nrstr(    
        proc sql noprint;
            select 
                quote(trim(lookfor), "'") ,
                trim(varname) 
            into
                :LOOKFOR_LIST separated by ' ' ,
                :VARNAME_LIST separated by ' ' 
            from 
                mylist;
            %let list_length = &SQLOBS. ;
        quit;
            
        %let lookfor_code = array keywords [&LIST_LENGTH.] $32 _temporary_ (&LOOKFOR_LIST.) ;
        %let varname_code = array varnames [&LIST_LENGTH.] 4 &VARNAME_LIST. ; 
    )));
    %put INFO: RC from &=DOSUBL_RC;

    %put INFO: &=LOOKFOR_CODE;
    %put INFO: &=VARNAME_CODE;    
    
    &LOOKFOR_CODE;
    &VARNAME_CODE;

%mend use_dosub;


data _null_; 
    %use_dosub(); 
    put keywords[1]=;
run;

%put _user_;

This is using a sledgehammer for a penny nail, but sometimes the ability to create code inline without generating a section break is very useful.

Re: Using FINDW in a DO loop to flag keywords in a string

Tom — Thu, 27 Jan 2022 00:09:52 GMT

Here is example that remove macros.

First create some sample datasets. One with the list of strings to search and a second with the list of words to find.

data test ;
  infile datalines truncover;
  row+1;
  input topics $256. ;
datalines;
PHYSICAL COMPUTING  BASIC COMPUTER TEACHING  PROJECTS USING WORD PROCESSING AND DATA ANALYZING  POWERPOINT
GAME DESIGN
WEB DESIGN AND DEVELOPMENT MOBILE APP DEVELOPMENT
PHYSICAL COMPUTING MAKER ACTIVITIES WEB DESIGN AND DEVELOPMENT TEXT-BASED PROGRAMMING
;

data keywords;
  key+1;
  input keyword $30.;
datalines;
PHYSICAL
ACTIVITIES
GAME
DATA
CODING
MOBILE
;

Then do a cross join to match every keyword to every string and then using FINDW() to check if the keyword is in the string.

If you want the wide format then use PROC TRANSPOSE to create it. If the goal is the wide structure then make the join a VIEW.

proc sql ;
create view tall as 
  select a.*,b.*
       , 0<findw(topics,keyword,' ','sit') as found
  from test a
     , keywords b
  order by row,key
;
quit;

proc transpose data=tall out=want(drop=_name_) prefix=keyword;
  by row topics;
  id key;
  idlabel keyword;
  var found;
run;

Let's check the results:

proc print;
 var row key: ;
run;

proc print label;
 var row key: ;
run;

Re: Using FINDW in a DO loop to flag keywords in a string

Tom — Thu, 27 Jan 2022 00:13:44 GMT

I usually find that I need to train FINDW() to SIT for it be useful.

i or I

ignores the case of the characters.

s or S

adds space characters (blank, horizontal tab, vertical tab, carriage return, line feed, and form feed) to the list of characters.

t or T

trims trailing blanks from the string, word, and character arguments.

Re: Using FINDW in a DO loop to flag keywords in a string

PaigeMiller — Thu, 27 Jan 2022 11:27:23 GMT

@JackHamilton wrote:
Personally, I find it easier to maintain a long narrow list than a short wide list, and if the list is coming from an external source it's usually easier to read it in a data step than in a macro. Data-driven instead of manually maintained array statements.

Good explanation! Thanks!

Re: Using FINDW in a DO loop to flag keywords in a string

Kurt_Bremser — Thu, 27 Jan 2022 12:30:39 GMT

Apart from Maxim 46 (Beware of the Hidden Blanks), you should also heed Maxim 11 (A Macro Is Not Needed) and, for the time being, forget that macros exist. You can and should then relearn this when you have mastered the basics of the SAS language, like DO loops in a data step:

data have;
infile datalines truncover;
input topics $256.;
datalines;
PHYSICAL COMPUTING  BASIC COMPUTER TEACHING  PROJECTS USING WORD PROCESSING AND DATA ANALYZING  POWERPOINT
GAME DESIGN
WEB DESIGN AND DEVELOPMENT MOBILE APP DEVELOPMENT
PHYSICAL COMPUTING MAKER ACTIVITIES WEB DESIGN AND DEVELOPMENT TEXT-BASED PROGRAMMING
;

data want;
set have;
array keywords [6] $ t1-t6 ('PHYSICAL','ACTIVITIES','GAME','DATA','CODING','MOBILE');
array varnames [6] physcomp makeract gamedesign datasci coding mobiledev; 
do _n_ = 1 to 6 ;
  if findw(topics, trim(keywords[_n_])) > 0 then varnames[_n_] = 1;
  else varnames[_n_] = 0;
end; 
run;

To become master of a tool, the most important thing to learn is when not to use it.