DATA Step, Macro, Functions and more

Replace a string inside a macro variable value

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Replace a string inside a macro variable value

[ Edited ]
%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.


Accepted Solutions
Solution
‎04-06-2016 09:34 AM
Super User
Posts: 7,832

Re: Replace a string inside a macro variable value

Posted in reply to buechler66
%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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Frequent Contributor
Posts: 76

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

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

 

Frequent Contributor
Posts: 76

Re: Replace a string inside a macro variable value

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

Respected Advisor
Posts: 3,799

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

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

Regular Contributor
Posts: 212

Re: Replace a string inside a macro variable value

Posted in reply to data_null__
Hmm...doesn't seem to be working.
Respected Advisor
Posts: 3,799

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

Hmm I left the testing for you.

 


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

 

 

Regular Contributor
Posts: 218

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

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

Regular Contributor
Posts: 212

Re: Replace a string inside a macro variable value

Posted in reply to AhmedAl_Attar
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. Smiley Sad
Frequent Contributor
Posts: 76

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

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

Regular Contributor
Posts: 218

Re: Replace a string inside a macro variable value

Posted in reply to buechler66

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.

 

 

 

Frequent Contributor
Posts: 134

Re: Replace a string inside a macro variable value

[ Edited ]
Posted in reply to AhmedAl_Attar

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

Solution
‎04-06-2016 09:34 AM
Super User
Posts: 7,832

Re: Replace a string inside a macro variable value

Posted in reply to buechler66
%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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 773 views
  • 1 like
  • 6 in conversation