Replace a string inside a macro variable value

[buechler66]

%let RULE=A.ROWID IN (SELECT TABLE_ROWID FROM IV_RPT.COMPARE_ERROR);

I have a macro variable defined as above.  I need to search this value sometimes and replace ' IV_RPT.' with

' IVPRL.' before the macro variable is referenced later in my program.  Does SAS offer a Replace function of some type?  Any suggestions would be greatly appreciated.  Thanks.

[ptimusk]

I would think to use PERL. PRXChange could work here.  Here is a paper on this http://analytics.ncsu.edu/sesug/2012/CT-03.pdf and here is some documentation on it http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#n0frf578x6v...

[ptimusk]

If you do use PERL can you makr my reply as a solution. thanks.

[data_null__]

Would it be easier to make another macro variable to hold that part of the string and set that value?

You can TRANSTRN using SYSFUNC:  %SYSFUNC(TRANSTRN(source,from,to)) 

%let rule = %sysfunc(transtrn(%superq(rule),IV_PRT,IVPRL));

[buechler66]

Hmm...doesn't seem to be working.

[data_null__]

Hmm I left the testing for you.

---

@buechler66 wrote:
Hmm...doesn't seem to be working.

---

[AhmedAl_Attar]

Here is a simpler solution

%let RULE=A.ROWID IN (SELECT TABLE_ROWID FROM IV_RPT.COMPARE_ERROR);
%let RULE=%sysfunc(tranwrd(%superq(RULE),IV_RPT,IVRPT));
%put RULE=%superq(RULE);

[buechler66]

It has to be done on the fly because the macro variable value actually comes from an Oracle table that stores the string. So I'm going to have to figure out a way to convert it. 😞

[ptimusk]

The PERL solution can help you be dynamic or 'on the fly'

[AhmedAl_Attar]

IF you are extracting the string value from a table, then you can do the string replacement within the sql using the TRANWRD/TRANSTRN functions!!

Depending on what kinda of SQL statement you are using, Implecit vs. Explicit, the function call location need to change to ensure optimal performance.

[ronan]

I agree : unknowingly, I came up with the same solution. 😉
%superq is not necessary, though. %bquote is sufficient.
The requester is always right, they say.

[Kurt_Bremser · Accepted solution]

%let rule=A.ROWID IN (SELECT TABLE_ROWID FROM IV_RPT.COMPAE_ERROR);

data _null_;
rule = tranwrd("&rule","IV_RPT.","IVPRL.");
call symput("rule",trim(rule));
run;

%put rule=&rule;

If you want it to be more parameterized, wrap it into a macro:

%let rule=A.ROWID IN (SELECT TABLE_ROWID FROM IV_RPT.COMPARE_ERROR);

%macro tran_mac(macvar,previous,replace);
%global &macvar;
data _null_;
length string $1000; * make big enough for all use cases;
string = tranwrd("&&&macvar.","&previous","&replace");
call symput("&macvar",trim(string));
run;
%mend;

%tran_mac(rule,IV_RPT.,IVPRL.);

%put rule=&rule;