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.
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.