%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.
%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;
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...
If you do use PERL can you makr my reply as a solution. thanks.
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));
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);
The PERL solution can help you be dynamic or 'on the fly'
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.
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.
%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;
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.