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

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

ruhickson — Mon, 18 Dec 2017 16:21:32 GMT

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.

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

Reeza — Mon, 18 Dec 2017 16:30:07 GMT

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.

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

ballardw — Mon, 18 Dec 2017 19:17:04 GMT

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.

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

ChrisNZ — Tue, 19 Dec 2017 04:10:07 GMT

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;