topic Re: Replace a string inside a macro variable value in SAS Programming

Replace a string inside a macro variable value

buechler66 — Tue, 05 Apr 2016 20:57:56 GMT

%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.

Re: Replace a string inside a macro variable value

ptimusk — Tue, 05 Apr 2016 21:04:55 GMT

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#n0frf578x6vno8n1w26b6qn2wlt5.htm

Re: Replace a string inside a macro variable value

data_null__ — Tue, 05 Apr 2016 21:13:41 GMT

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));

Re: Replace a string inside a macro variable value

ptimusk — Tue, 05 Apr 2016 21:23:45 GMT

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

Re: Replace a string inside a macro variable value

AhmedAl_Attar — Tue, 05 Apr 2016 21:31:29 GMT

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);

Re: Replace a string inside a macro variable value

buechler66 — Tue, 05 Apr 2016 21:55:31 GMT

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

Re: Replace a string inside a macro variable value

buechler66 — Tue, 05 Apr 2016 21:57:03 GMT

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. 😞

Re: Replace a string inside a macro variable value

ptimusk — Tue, 05 Apr 2016 22:03:38 GMT

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

Re: Replace a string inside a macro variable value

AhmedAl_Attar — Tue, 05 Apr 2016 22:17:45 GMT

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.

Re: Replace a string inside a macro variable value

Kurt_Bremser — Wed, 06 Apr 2016 07:22:50 GMT

%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;

Re: Replace a string inside a macro variable value

ronan — Wed, 06 Apr 2016 14:43:39 GMT

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.

Re: Replace a string inside a macro variable value

data_null__ — Wed, 06 Apr 2016 14:50:51 GMT

Hmm I left the testing for you.

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