Macro to seach for a text in the code and compare the same with the excel file

[ChrisNZ]

Also see if you need the string search to be case insensitive. Look at 'i' value for third parameter for the FIND() function 

[Tom]

Note that when using the FILEVAR= option it is better to use DO WHILE() instead of DO UNTIL() to avoid any empty file in the list causing the data step to stop early because it read past the end the input file.

  infile IN filevar=FILENAME end=EOF;
  do while (not EOF);
    input;
    if find(_INFILE_, TEXT, 't') then output;
  end;

[andreas_lds]

It is still not clear, what you expect as result.

In the meantime start as @Quentin suggested: write a data step that reads a program-file searching for the name of a dataset. Don't waste time on creating a macro, you won't need it.

[ballardw]

---

@Stephenjohn wrote:

i have to find a text from the sas codes and compare it with the excel file

Which suggests that providing an example of the Excel file (not best) or a data set created from the Excel file would be a good idea.

Some additional thoughts: is the Excel to search for text case sensitive? Meaning that if you are looking for "Raw" that "raw" would not be a match? If the search is not case sensitive then you would want to make sure to use an insensitive approach.

Actually the "tricky" part is getting the two pieces of information, the Excel to search for list, and the SAS code into data sets.

Here is one example of "matching" based on a word in one data set present in another.

data example;
   input name $;
datalines;
John
Johnny
Mary
;

data example2;
  input phrase $35.;
datalines;
Johnny planted apple trees
My name is Phred
Sally sells sea shells
Parsly Sage Rosemary  and thyme
I met Mary on Saturday
;

proc sql;
   create table matched as
   select a.name,b.phrase
   from example as a, example2 as b
   where indexw(b.phrase,a.name)>0
   ;
quit;

So if the data set with the SAS code has a variable that holds the code file name then you could select the variable holding that name.

Note that you need to determine in the number of times a value appears is important as the above sort of match will find all the matches. Typically data sets and such are referenced multiple times. If you only one result then that is important to know. You could also read the SAS code file(s) and get a line number to tell where exactly the the match(es) occur if needed.

[Patrick]

@Stephenjohn Put yourself into the shoes of someone who doesn't know anything about what you have and the problem you need to solve. Do you believe your description would be sufficient for you to understand? 

Also PLEASE don't post sample data as screenshots. Use the following two icons to share text  - something we can actually copy/paste into code.

If you have an Excel then you can attach it - but please be aware that a lot of people won't download Excels for security concerns so much better is to provide such data via a working SAS datastep creating the data (you can provide such code via the running man icon ).

You're asking for "a macro" (=code) so you need to provide to us as much of the source data as you can in a form where we don't have to spend the time to prepare the data for you but can spend the time for answering your question.

And last but not least: It's in my experience really worth to spend the time for clearly formulating the problem. To clearly define the problem is already half of the solution. It happened to me more than once that while trying to formulate a clear question for the forum I actually got the clarity in my mind to solve the problem myself. 

Hope that makes sense.