topic Re: SAS macro variable to hold all values of a field in a SAS dataset in SAS Programming

SAS macro variable to hold all values of a field in a SAS dataset

suchismita — Fri, 28 Feb 2020 20:31:41 GMT

I have a sas dataset that contains list of .sas file names in a column called codename.

My task is to read them one by one and then look for a particular string (something like a specific macro variable or a procedure) .

If the string is there , then I will only filter those .sas files.

as an example, let's say below are two values in codename column, I want these two file paths to be read one by one and a search/find function (similar to grep in linux) should be executed on them.

codename

/path1/codes/proj1/test1.sas
/path2/codes/proj3/test1/program/test7.sas

please suggest.

Re: SAS macro variable to hold all values of a field in a SAS dataset

Reeza — Fri, 28 Feb 2020 20:47:41 GMT

1. First get it working on one file. Here's an example of how to search a file from SAS

https://gist.github.com/statgeek/26cf789d6da0ce941e447022fdccf0f4

2. Figure out how to automate that for all your files - usually using a macro.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

@suchismita wrote:

I have a sas dataset that contains list of .sas file names in a column called codename.

My task is to read them one by one and then look for a particular string (something like a specific macro variable or a procedure) .

If the string is there , then I will only filter those .sas files.

as an example, let's say below are two values in codename column, I want these two file paths to be read one by one and a search/find function (similar to grep in linux) should be executed on them.

codename

/path1/codes/proj1/test1.sas
/path2/codes/proj3/test1/program/test7.sas

please suggest.

Re: SAS macro variable to hold all values of a field in a SAS dataset

suchismita — Fri, 28 Feb 2020 22:05:04 GMT

thank you @Reeza . Few urls below I am not able to open because of company policy.

Is there anyway , you can post sample code snippet to achieve this. Please let me know if my question is not clear to you.

i was trying to read all values into a single macro variable like, below but i am getting error because length of macro variable .

ERROR: The length of the value of the macro variable NAME (65540) exceeds the maximum length (65534). The value has been truncated to 65534
characters.

proc sql;
select codename into :name separated by "','" from  test;
quit;

So my plan was , after reading these .sas codes into a macro variable, i would apply some kind find or search function on each of them , to look for that particular string in them. But I am not getting a proper way to handle this.

Re: SAS macro variable to hold all values of a field in a SAS dataset

PaigeMiller — Fri, 28 Feb 2020 22:29:29 GMT

As @Reeza said, get you code to work on one file without macros, before trying anything with macros.

Re: SAS macro variable to hold all values of a field in a SAS dataset

Reeza — Fri, 28 Feb 2020 22:43:09 GMT

/*This code will search text files for a single word, search_string
Originally via @schmuel here:
https://communities.sas.com/t5/Base-SAS-Programming/Searching-SAS-code-for-keywords/m-p/390472#M93671
*/

%let search_string = rename;
%let suffix = sas;
%let root=/folders/myshortcuts/My_Folders/;
filename finp ("&root.sas_help/*.&suffix"); 

data results;
     length fname _filepath $200;
     infile finp filename = _filepath eov=_eov truncover;
     input a_line $200.;
     fname = _filepath;
     
     if _eov=1 then do;
       _n=0;

       _eov=0;
     end;
     _n+1;
     
     if find(a_line,"&search_string",'i')
     then output;
     keep _n a_line fname;
run;

The macro tutorials are harder to link because they're either rendered Jupyter Notebooks or HTML files. Sorry. You can try accessing the material on a non work computer/device?

Re: SAS macro variable to hold all values of a field in a SAS dataset

Tom — Fri, 28 Feb 2020 22:59:52 GMT

The FILEVAR option of the INFILE statement will let you use your data without having to make macro variables.

data want ;
  set have ;
  fname=codename;
  infile code filevar=fname end=eof;
  found=0;
  do while (not eof and not found);
    input;
    if find(_infile_,'some string','i') then found=1;
  end;
  keep codepath found;
run;

Re: SAS macro variable to hold all values of a field in a SAS dataset

Reeza — Sat, 29 Feb 2020 19:28:15 GMT

Untested, but an idea of how to extend @Tom nice clean solution to multiple words.

%let searchWords = apple, banana, pears;

data want ;
  set have ;
  fname=codename;
  nWords = countw("&searchWords");
  
  infile code filevar=fname end=eof;
  found=0;
  do while (not eof and not found);
    input;
    do i=1 to nWords while (not found);
       str = scan("&searchWords", i, ",");
       if find(_infile_, str ,'i') then found=1;
    end;
  end;
  keep codepath found;
run;