Code to identify all SAS programs in a directly with a specific text string

[Scott13]

Hello all.  I am working on a project where I need to identify all SAS programs  that are using a list of variables anywhere in the program.  I would like to be able to search a directory at a time and have SAS output the program names that use those variables.  I have been using the below code, however it is only identifying if the variables are being used in joins etc and doesn't flag the report if it is just in the select statement.  Any help would be greatly appreciated!

%macro srch_dir(dir=\\WFSISF20\Reports\Service Files\Code
 ,words=
  first_var second_var third_var);

%let words=%upcase(&words);
%if &sysscp=%str(WIN) %then %do;
        filename piped pipe "dir /b ""&dir"" ";
%end;
%else %if &sysscp=%str(SUN 4) %then %do;
        filename piped pipe "ls ""&dir"" ";
%end;

data work.progs;
        length progname $200;
        infile piped;
        input progname;
run;

filename piped clear;

data _null_;
        retain n 0;
        call symput('n',n);
        set work.progs (obs=0) nobs=n;
        stop;
run;

data work.words (keep=w word);
        length word $300 words $300;
        retain words "&words";
        w = 1;
        word = scan(words,w,' ');
        do while (word ne '');
                output;
                w + 1;
                word = scan(words,w,' ');
        end;
        call symput('w',w-1);
run;

%if &n > 0 %then %do;
        %do i=1 %to &n;

                data _null_;
                        retain n &i;
                        set work.progs point=n;
                        call symput('progname',progname);
                        stop;
                run;

                %if &sysscp=%str(WIN) %then %do;
                        %let prog=&dir\&progname;
                %end;
                %else %if &sysscp=%str(SUN 4) %then %do;
                        %let prog=&dir/&progname;
                %end;

                data work.elines(keep=program word textline n);
                        file = "&prog";
                        length word $300 program $200;
                        retain program "&prog" file "&prog";
                        infile dummy filevar=file truncover end=eof;
                        input textline $200.;
                        textline = upcase(textline);
                        n+1;
                        %do j = 1 %to &w;
                                word = scan("&words",&j,' ');
                                if indexw(upcase(textline),word)>0 then
                                output;
                        %end;
                run;

                proc append base=work.lines new=work.elines;
                run;
        %end;

        data _null_;
                retain numobs 0;
                call symput('numobs',numobs);
                set work.lines (obs=0) nobs=numobs;
                stop;
        run;

        %if &numobs > 0 %then %do;
                title Programs in &dir with occurrences of the word(s)
&words;
                options ls=116 ps=85;

                proc report data=work.lines nowd split='#';
                        column program word n textline;
                        break after program / page;
                        break after word / skip;
                        define program / order "Program Name" width=20 flow;
                        define word / order "Word Found";
                        define n / order "Line Number";
                        define textline / "Line" width=40 flow;
                run;

                title;

                proc datasets lib=work nolist;
                        delete lines elines words progs;
                run;
                quit;
        %end;
        %else %do;
                data _null_;
                        put "WARNING: No files in &dir contain the word(s)
&words";
                run;
        %end;
%end;

%else %do;
        data _null_;
                put "WARNING: No files found in directory &dir";
        run;
%end;

%mend;

[ChrisNZ]

1- Maybe 

if indexw(upcase(textline),trim(word))>0 then

would work better?

2- Look at how the FILEVAR= option is properly used, to simplify your code significantly.

3- Note that you are not taking word boundaries into account in your search. 

So while looking for VARNAME you'll also flag VARNAME2.

[ChrisNZ]

You can use

if prxmatch(catt('/\b',WORD,'\b/i'),TEXTLINE) then

to take word boundaries into account.

[LaurieF]

Here's something I prepared earlier. I ran it over my macros directory looking for putmsg, because it's a macro that my macros themselves call a lot.

%search_directory(path=\\my\macro\directory\path, string=putmsg);

String putmsg found in collapse.sas.
String putmsg found in count.sas.
String putmsg found in countobs.sas.
String putmsg found in csv.sas.
String putmsg found in dup.sas.
String putmsg found in freq.sas.
String putmsg found in genfmt.sas.
String putmsg found in lrb.sas.
String putmsg found in mixcase.sas.
String putmsg found in putmsg.sas.
String putmsg found in varexp.sas.
11 entries found in path for putmsg.

The output ain't flash, because for me it doesn't need to be

Here's the code:

%macro search_directory(path=, string=, suffix=sas);
%local did fid rc infocnt fileref i found strlen count mprint notes;
%let fileref = source;
%let rc = %sysfunc(filename(fileref, &path));
%if &rc ne 0 %then
    %put %sysfunc(sysmsg());
%do i = 1 %to 20;
    %let did = %sysfunc(dclose(&i));
    %end;
%let did = %sysfunc(dopen(&fileref));
%let i = 0;
%let notes = %sysfunc(getoption(notes));
%let mprint = %sysfunc(getoption(mprint));
option nonotes nomprint;
%let strlen = %length(&string);
%let count = 0;
%do %until(&dname = );
    %let i = %eval(&i + 1);
    %let dname = %qsysfunc(dread(&did, &i));
    %if %nrbquote(&dname) ne and %qsysfunc(indexc(%superq(dname), %bquote(&))) = 0 %then %do;
        %if %lowcase(%scan(&dname, -1, %str(.))) = &suffix %then %do;
            %let found = 0;
            data _null_;
            infile "&path.\&dname";
            input @"&string" +(-&strlen) string $char&strlen..;
            if string = "&string" then
               call symput('found', '1');
            run;
            %if %eval(&found ne 0) %then %do;
                %let count = %eval(&count + 1);
                %put String &string found in &dname..;
                %end;
            %end;
        %end;
    %end;
%put &count entries found in path for &string..;
%let rc = %sysfunc(dclose(&did));
%put %sysfunc(sysmsg());
%let rc = %sysfunc(filename(fileref));
%put %sysfunc(sysmsg());
option &mprint &notes;
%mend search_directory;

[ChrisNZ · Accepted solution]

I rewrote your macro, feel free to improve, there are still many gaps:

%macro srch_dir(dir  =\\WFSISF20\Reports\Service Files\Code
               ,words= first_var second_var third_var);

  %if %length(&words)=0 %then %return;
  %local nbfiles nbmatch s;
  %let s=s; %if %sysfunc(countw(&words,%str( )))=1 %then %let s=;

  filename PIPED pipe %if &sysscp.=WIN %then "dir /b ""&dir"" "; %else "ls ""&dir"" ";;
  data PROGS;
    infile PIPED pad;
    input PROGNAME $256.;
    call symput('nbfiles','1');
  run;
  filename PIPED clear;

  %if ^%length(&nbfiles) %then %do;
    %put WARNING: No files found in directory &dir..;
    %return;
  %end;

  data LINES(keep=PROGNAME WORD TEXTLINE N);
    set PROGS;
    length WORD FILEVAR $256;
    FILEVAR=catt("&dir", ifc("&sysscp"="WIN",'\','/'), PROGNAME);
    infile dummy filevar=FILEVAR truncover end=EOF pad;
    do until(EOF);
      N=sum(N,1);
      input TEXTLINE $256.;
      do J=1 to countw("&words",' ');
        WORD = scan("&words",J,' ');
        if prxmatch(catt('/\b',WORD,'\b/i'),TEXTLINE) then output;
        call symput('nbmatch','1');
      end;
    end;
  run;

  %if ^%length(&nbmatch) %then %do;
    %put WARNING: No files in &dir contain the word&s &words..;
    %return;
  %end;

  title "Programs in &dir with occurrences of the word&s";
  title2 "&words";
  options ls=116 ps=85;

  proc report data=LINES nowd split='#';
    column PROGNAME WORD N TEXTLINE;
    define PROGNAME / order "Program Name" width=20 flow;
    define WORD     / order "Word Found";
    define N        / order "Line Number";
    define TEXTLINE / "Line" width=40 flow;
    break after PROGNAME / page;
    break after WORD    / skip;
  run;

  title;

  proc datasets lib=WORK nolist;
    delete LINES ELINES WORDS PROGS;
  quit;

%mend; %* You should restore the options here;

[Scott13]

Thanks ChrisNZ, this seems to be doing exactly what I need!