topic Re: Macro in SAS Programming

Macro

Stephenjohn — Mon, 16 Aug 2021 05:43:46 GMT

Want to compare the sas files used in a sas code with the list which has sas file n domain as a naming .can we hav a macro in place

Re: Macro

andreas_lds — Mon, 16 Aug 2021 06:08:52 GMT

If you don't want to write the code yourself, you have to hire someone or search for code that you can adept to your needs.

Re: Macro

ChrisNZ — Mon, 16 Aug 2021 07:01:18 GMT

Please provide:

- A proper title

- A proper explanation

- An example of what you have (what is "the sas files used in a sas code " ?)

- An example of what you want (what is "the list which has sas file n domain as a naming" ? )

Spending a few seconds to haphazardly throw together incomplete words in the hope that they make sensible sentences, and then expecting volunteers to spend time guessing your needs and providing a thoughtful and comprehensive reply is unrealistic to say the least.

Re: Macro

Patrick — Mon, 16 Aug 2021 08:21:24 GMT

Hi @Stephenjohn

Welcome to the SAS Communities! Guess people haven't realized that you're new to this forum else your welcome would have been a bit warmer.

Help us help you and try to formulate the problem as clear as you can also providing the necessary detail.

A lot of people here are also more than happy to share actual and tested code. You just need to provide representative sample data for this (as a SAS data step creating such data and not as a screenshot please!) and eventually the code you've already tried. Even if such code isn't properly working it's still often very helpful to get to better understand where you are coming from.

Re: Macro

Stephenjohn — Mon, 16 Aug 2021 09:29:04 GMT

Please find the example below of the requirement i am looking for.

ex: this is a sas code where i am taking data from a lib raw which has have and bright.. I want to check whether HAVE and BRIGHT are used in the particular code(want.sas file). I have a list to compare in the form of an excel.. Can we have a macro to find this ..

Re: Macro

andreas_lds — Mon, 16 Aug 2021 10:13:51 GMT

So, you want to analyse your sas-code files.

What exactly do you expect as result?

How do you identify the datasets you want to find?

Re: Macro

Patrick — Mon, 16 Aug 2021 10:21:36 GMT

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

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

Stephenjohn — Mon, 16 Aug 2021 11:52:15 GMT

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

eg: below is a sas file named AE.sas and i want to find 'raw.ae' from this code.

proc sql;
create table ae_supp as
select a.*,b.* from raw.ae

as a inner join suppae_act as b on a.usubjid=b.usubjid1 and a.aeseq=b.idvarval;

quit;

to identify the which text needs to be searched in the sas file we have a excel file as below.

text sas file

raw.ae AE.sas

raw.cm CM.sas

raw.ae ho.sas

etc.

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

Quentin — Mon, 16 Aug 2021 12:29:50 GMT

Thanks, this is clearer. I wouldn't think about a macro yet, I would start with thinking about how you would check one .sas file to see if it has a string in it.

So, I think the first step could be to write a program that would search the file AE.sas, to see if it has the string "raw.ae" in it.

Have you tried writing that program? This could be done with a SAS data step (reading AE.sas as a data file). Or could be done with other languages. I'd suggest you try writing a DATA step to read that file and parse it. You could also try searching lexjansen.com for papers about SAS log scanners. The log scanners typically read in a .log file and search for specific words. Both a .log file and a .sas file are just text files. So the approach used by a log scanner to search for a string should also work for scanning a .sas file.

Once you have a working approach for scanning one file, you can move on to automating it with a macro, or CALL EXECUTE, or similar.

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

Stephenjohn — Mon, 16 Aug 2021 12:48:45 GMT

I tried with the below program.. but this can give only 1 search at a time...

I had to write the macro Search n number of times to get an output in different datasets..and then append and then compare with the excel file.. if there is a way to find multiple strings from multiple sas files stored in one path that would help.

%macro search(results,dataset,input);
%let root= path;
filename finp ("&root./&input..sas");

data &results.;
length fname _filepath frname $200;
infile finp filename = _filepath eov=_eov truncover;
input a_line $200.;
fname =compress(scan(_filepath,-1,'\'),".sas");

if _eov=1 then do;
_n=0;
_eov=0;
end;
_n+1;
frname=compress(scan(a_line,-1,'.'),';');
if find(a_line,"&dataset.",'i')
then output;

keep _n fname frname;
run;
%mend search;

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

andreas_lds — Mon, 16 Aug 2021 12:51:19 GMT

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.

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

Quentin — Mon, 16 Aug 2021 13:03:25 GMT

Does that macro you wrote work for searching one file for one string?

I would think it would be possible to write a DATA step that would read in a list of files, and search each of them for a string. But the hassle would be that you would need to manage the lookup of which string should be looked for in each file.

If that macro works for one file, I would look into using CALL EXECUTE. You could read in the Excel file which has one row for each file to search, and lists the file name and the string to search for. And CALL EXECUTE would call your macro once for each row, passing in the as parameters the values in the columns "raw file" and "sas code." You would get one output file from each search, but you could use PROC APPEND (probably inside the macro), so that each time you call the macro it does the search, and adds a record with the results of that search.

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

PhilC — Mon, 16 Aug 2021 18:21:26 GMT

Easy:

If the team members who coded the SAS programs religiously used "valid two-level SAS names". Then writing SAS code to open all SAS code files and do string searches will work. This is still difficult, but this is the best case scenario.

Bruno_SAS wrote useful code here to bring multiple text files into a dataset.

Solved: Re: Read all text files into one dataset - SAS Support Communities

Hard:

They did not use valid two-level SAS names: I would begin with familiarizing myself PROC SCAPROC.

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

ballardw — Mon, 16 Aug 2021 20:20:25 GMT

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

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

ChrisNZ — Mon, 16 Aug 2021 22:41:53 GMT

It's a lot easier to help you when you provide all the information.

This does what you want.

data TEXT;
  input TEXT $ FILENAME $;
cards;
raw.ae AE.sas
raw.cm CM.sas
raw.ae ho.sas
run;

data CHECK;
  set TEXT;
  infile IN filevar=FILENAME end=EOF;
  do until (EOF);
    input;
    if find(_INFILE_, TEXT, 't') then output;
  end;
run;

Please adapt to your needs and report. The path is most likely missing.

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

Stephenjohn — Tue, 17 Aug 2021 06:17:22 GMT

How will i assign a path in this code to check for the file names.

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

ChrisNZ — Tue, 17 Aug 2021 06:41:23 GMT

> How will i assign a path in this code to check for the file names.

Add it in the FILENAME variable.

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

ChrisNZ — Tue, 17 Aug 2021 06:43:24 GMT

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

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

Stephenjohn — Tue, 17 Aug 2021 14:24:18 GMT

Thank you. that was helpful.

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

Tom — Tue, 17 Aug 2021 14:48:51 GMT

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;