Automate a code using SAS macro

[Aexor]

Hi All,

I have few sample  codes which needs to be migrated to new environment and i have  to replace old path with new.

Library location	Old path	New Path	Library location
Libname libx "\\win907\A$\Data\Inside\one";	\\win907\A$\Data\Inside\one	\\open\C\XYZ\folderA\one	Libname libx "\\open\C\XYZ\folderA\one";
Libname liby "\\win907\A$\Data\outside\two";	\\win907\A$\Data\outside\two	\\open\D\abc\folderB\two	Libname liby " \\open\D\abc\folderB\two";
LIBNAME libz  "\\win907\A$\Data\med\tag" ;	\\win907\A$\Data\med\tag	\\open\E\abc\folderC\tag	LIBNAME libz  "\\open\E\abc\folderC\tag" ;

  I need to write a macro which will replace old path to new path and give new library location .

Any suggestion or solution will be much appreciated.

[ChrisNZ]

How many files do you have? Where are they?

Can't you take the libname statements out of the individual programs and centralise them?

[Aexor]

Thanks for your response.

We have multiple sas programs which needs to be migrated which have libname statement as I mentioned in the above example.
we have to do this many times for different different programs so to avoid manual work we need to automate the process.

[Kurt_Bremser]

You should grab the opportunity and centralize the library definitions, either in an autoexec file or in SAS metadata. Programs should not have library definitions within them if those libraries are used in the same way (same libref and physical path) across different programs.

This will be a strong investment into your own future, instead of keeping the debt in time spent to maintain the codes.

[PaigeMiller]

Agreeing with the above.

If you write LIBNAME statements in every piece of code, when the location of the libref changes, then you have to change every piece of code. Putting this in an AUTOEXEC makes handling this situation much much simpler, you only have to change the location in one place.

In my autoexec, I use (something like) this:

%let pers = g:\myserver\mydepartment\MillerP;

And then in code

libname proj1 "&pers\proj1";

and if the location of my server files ever changes, I just change the autoexec and all code immediately uses the new location.

Similarly, you can do the same for databases, you put the proper libname in a simple macro in the AUTOEXEC and then always call the libname statement via a simple macro.

%macro db1;
libname db1 odbc dsn=AAAAA schema=dbo user=BBBBBB password=xxxxxx;
%mend db1;

and then in code where I want to link to this database

%db1

and again, if the libname ever changes, you just change the autoexec, not the actual code.

A long time ago, before I did this, I had to write SAS code that reads every .sas file and parses the code and looks for the old server location and then changes the text of the .sas file to contain the new server location. I no longer have this code as it was written when I worked at a different employer, but it can be done. Nevertheless, I think you can see that not having to do this when the server changes is a better solution in the long run.

[Aexor]

Thank you! I will try 🙂

[Aexor]

Thanks I got your point . will try to implement as suggested.

Also I am a bit curious on macro side too. can you suggest approach if there is any possibility of pattern matching of the libanme statemnet with the new libname name and then replacing the path

[Shmuel]

Next link conains a macro program which enables search text files (.sas, .log etc.) in different folders for a given string.

It can be addapted to seracch for any string given in a list of strings (old paths)

in a loop. (for example - generating the code and submiting it by call execute);

I do not suggest to replace strings automatically.

[Tom]

Is the issue just 3 librefs with constant paths?

%macro libs(version);
%if %qupcase(&version)=OLD %then %do;
Libname libx "\\win907\A$\Data\Inside\one";	
Libname liby "\\win907\A$\Data\outside\two";	
LIBNAME libz  "\\win907\A$\Data\med\tag" ;	
%end;
%else %do;
Libname libx "\\open\C\XYZ\folderA\one";
Libname liby " \\open\D\abc\folderB\two";
LIBNAME libz  "\\open\E\abc\folderC\tag" ;
%end;
%mend;

[Aexor]

No, I have many .

[Patrick]

@Aexor To use the opportunity for a clean-up as already suggested is certainly a very good idea. In case that's not possible then below should give you a start.

Make sure you first run this code with &for_real=N This will create a report of what would get changed without changing/creating new files.

The code applies for any .sas file that resides in a folder or sub-folder that you provide in below code section.

It's always worth to search the communities and the SAS documentation as many questions have been asked and resolved before.

And here the full code. As long as you don't run with &for_real=N this code will not write anything back to disk.

/*%let for_real=Y;*/  /* creates file with changes: <filename>.sas.CHANGED */
%let for_real=N;      /* creates and prints a table with changes but doesn't create the CHANGED file */

/** directory listing **/
/* https://communities.sas.com/t5/SAS-Programming/List-of-Files-in-Directory-and-Subdirectories-with-File/m-p/696059 */
data filelist;
  length dname thisFile $256 dir level 8;
  input dname;
  retain thisFile ' ' level 0 dir 1;
datalines4;
c:\temp
;;;;

data filelist;
  modify filelist;
  rc1=filename('tmp',catx('/',dname,thisFile));
  rc2=dopen('tmp');
  dir = not not rc2;
  if not dir then do;
    fid=fopen('tmp','i',0,'b');
    fid=fclose(fid);
  end;
  else do;
    dname=catx('/',dname,thisFile);
    thisFile=' ';
  end;
  replace;
  if dir;
  level=level+1;
  do i=1 to dnum(rc2);
    thisFile=dread(rc2,i);
    if upcase(scan(thisFile,-1,'.'))='SAS' then output;
  end;
  rc3=dclose(rc2);
run;

/** list of paths to replace **/
data paths;
  infile datalines truncover dlm='|';
  input oldPath :$1000. newPath :$1000.;
  datalines;
\\win907\A$\Data\Inside\one|\\open\C\XYZ\folderA\one
\\win907\A$\Data\outside\two|\\open\D\abc\folderB\two
;

/** search and replace paths in files from filelist **/
/*  Example 5: reading from multiple input files: 
/*    https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n1rill4udj0tfun1fvce3j401plo.htm#n1x9joj5qbfkjyn1kqtm7joru0qb */
data _null_;
  if _n_=1 then
    do;
      /* hash lookup table for old/new paths */
      length hkey $1;
      retain key '1';
      dcl hash h1(multidata:'y');
      h1.defineKey('hkey');
      h1.defineData('oldPath','newPath');
      h1.defineDone();
      do until(done);
        set paths end=done;
        rc=h1.add();
      end;
      done=0;
      /* hash table to collect rows with changes */
      length row 8 lineOrig lineChanged $256;
      dcl hash chng(multidata:'y',ordered:'y');
      chng.defineKey('dname','thisFile','row');
      chng.defineData('dname','thisFile','row','lineOrig','lineChanged');
      chng.defineDone();
    end;

  set filelist end=last;
  if not missing(thisFile);
  length fileloc $1100;
  fileloc=catx('\',dname,thisFile);

  infile myfile filevar=fileloc end=done lrecl=512 line=row; 

  do while(not done);
    input;
    /* search and replace paths */
    /* https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/p1f92kngyuurnln120e2na45022k.htm */
    do while (h1.do_over() = 0);
      startPos=find(_infile_,strip(oldPath),'i');
      if startPos>0 then
        do;
          lineOrig=_infile_;
          _infile_=cats(substrn(_infile_,1,startPos-1),newPath,substrn(_infile_,startPos+length(oldPath)));
          lineChanged=_infile_;
          /* collect changes */
          rc=chng.add();
        end;
    end;

    /* write changes to <filename>.sas.CHANGED */
    %macro for_real(for_real=N);
    %if &for_real=Y %then
      %do;
        outfile=cats(fileloc,'.CHANGED'); 
        file dummy filevar=outfile;
        len=length(_infile_);
        put _infile_ $varying. len;
      %end;
    %mend;
    %for_real(for_real=&for_real);
  end;

  if last then
    do;
      rc=chng.output(dataset:'work.changes');
    end;

run;

title 'Rows with Changes';
proc print data=work.changes;
run;
title;

[Tom]

---

@Aexor wrote:
No, I have many .

---

Then explain in more detail what your actual question is.

You made it sound like you wanted something that would modify an existing libref that has already been defined.  If that is true why?  Why not just define it right to begin with? 

Are you saying you need to modify the source code in a lot of program files that have hardcoded paths?

If so then to make the code more flexible you might replace the LIBNAME statements with a call to a macro, but you would need to explain in much more detail whether there is any pattern to the directories you need to point the librefs to.

For example if your directory structure creates a parallel locations to be used by each separate project you might create a macro where the user specifies the project name and the librefs are created automatically.

%macro librefs(project);
%let topnode=/myapplication;

libname raw "&topnode/&project/raw" access=readonly;
libname standard "&topnode/&project/standard";

%mend librefs;

[Quentin]

Agree with comments from others about centralizing the definitions etc.

That said, since .sas files are just text files, it looks like for a "quick-and-dirty" approach you could use a text editor to find and replace the old directory paths with the new paths. Most editors (Notepad++, Ultra-Edit, etc.) make it easy to do that for all *.sas files in a specified directory (and subdirectories).  You'll want to make a back up your code first (in my first year of coding, I once accidentally removed ALL the CR/LF in my .sas programs for one project, because I clicked "replace in files" rather than "replace in the currently open file".  : )

While you can use SAS to edit text files, in this cases it's probably easier to use a text editor to edit text files.

But as others have said, if you have time, better to refactor this to pull out the library definitions or path definitions...

[PaigeMiller]

---

@Quentin wrote:

That said, since .sas files are just text files, it looks like for a "quick-and-dirty" approach you could use a text editor to find and replace the old directory paths with the new paths. Most editors (Notepad++, Ultra-Edit, etc.) make it easy to do that for all *.sas files in a specified directory (and subdirectories).  You'll want to make a back up your code first (in my first year of coding, I once accidentally removed ALL the CR/LF in my .sas programs for one project, because I clicked "replace in files" rather than "replace in the currently open file".  : )

While you can use SAS to edit text files, in this cases it's probably easier to use a text editor to edit text files.

---

Great point, Notepad++ and other programs can make bulk find-and-replace operations.