DATA Step, Macro, Functions and more

Using SAS to alter all occurences of a string in a SQL script

Reply
New Contributor
Posts: 3

Using SAS to alter all occurences of a string in a SQL script

So we're upgrading our databases, and there are a lot of legacy scripts that are, for the most part, entirely salvageable, but they will all need to be updated in the near future.

 

Essentially, I'm looking for a way for SAS to read a .sql script, and change any entries of, say VEGETABLE.BANANA to FRUIT.BANANA.

 

I know I could change it in notepad or MySQL manually, but I'm looking to see if SAS has a way to do it, as it's a functionality that I feel could be useful in reducing workload down the road.

Super User
Posts: 23,992

Re: Using SAS to alter all occurences of a string in a SQL script

[ Edited ]
Posted in reply to ruhickson

I wouldn't use SAS, use PowerShell or a scripting language. They're much faster with this type of work.

 

EDIT: If you really do want to do this using SAS though, you can search on here for examples, this question has been asked and answered. 


ruhickson wrote:

So we're upgrading our databases, and there are a lot of legacy scripts that are, for the most part, entirely salvageable, but they will all need to be updated in the near future.

 

Essentially, I'm looking for a way for SAS to read a .sql script, and change any entries of, say VEGETABLE.BANANA to FRUIT.BANANA.

 

I know I could change it in notepad or MySQL manually, but I'm looking to see if SAS has a way to do it, as it's a functionality that I feel could be useful in reducing workload down the road.


 

Super User
Posts: 13,924

Re: Using SAS to alter all occurences of a string in a SQL script

Posted in reply to ruhickson

I have done this for a few smallish scripts BUT there are things to look out for. First is does "VEGETABLE." ever occur as part of another compound such as "LEGUMEVEGETABLE."? if so then order of replacement becomes an issue.

Second is case ever important? "Vegetable" is not "VEGETABLE" is not "vEGetaBLE". If source case doesn't need to be retained but may want to replace "vegetable" and "VEGETABLE" then the Upcase may be of interest. But if you have other text, such as Label statements or attributes, you may not want to upcase those.

 

Most important is never ever replace code in place unless you have made good copies elsewhere.

 

A very brief example is:

data _null_;

   infile "your script file" lrecl=1000;

   file "outputscriptfile"  lrecl=1000;

    input;

   tranwrd(_infile_,'VEGETABLE.','FRUIT.');

   put _infile_;

run;

Lrecl should be the length of the longest line expected. If your code doesn't run more that 256 on any line then should be okay.

 

Multiple changes could be made with multiple tranwrd statements but order may matter.

 

Super User
Posts: 2,505

Re: Using SAS to alter all occurences of a string in a SQL script

Slightly arranged.

 

data _null_;
   infile "your script file" lrecl=1000;
   file   "outputscriptfile" lrecl=1000;
   input;
   do while(index(_infile_,' VEGETABLE.'));
     _infile_=tranwrd(_infile_,' VEGETABLE.',' FRUIT.');
   end;
   put _infile_;
run;
Ask a Question
Discussion stats
  • 3 replies
  • 135 views
  • 1 like
  • 4 in conversation