BookmarkSubscribeRSS Feed
ruhickson
Calcite | Level 5

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.

3 REPLIES 3
Reeza
Super User

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.


 

ballardw
Super User

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.

 

ChrisNZ
Tourmaline | Level 20

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 704 views
  • 1 like
  • 4 in conversation