%SUBSTR is out of range warning

[David_Billa]

If I execute the code below, I'm getting the out of range  warning with %SUBSTR. Any help to fix this warning?

Also I would like to know if there is way to replace tranwrd with parsing function in this same program.

%* Set example input values ;
%let _output0=IFRSITSS.META_DATA;
%let _output1=IFRSITSS.INSURANCE;
%let _output2=IFRSITSS.AUDIT_TRAIL;

%* Set the FIXED values ;
%let meta_table=IFRSITSS.META_DATA;
%let audit_table=IFRSITSS.AUDIT_TRAIL;

%* Calculate TRANS_TABLE by removing the FIXED table names from the list of all three ;
%let trans_table=&_output0 &_output1 &_output2;
%let trans_table=%sysfunc(tranwrd(&trans_table,&meta_table,));
%let trans_table=%sysfunc(tranwrd(&trans_table,&audit_table,));
%let trans_table_sql=%substr(&trans_table.,10,%length(&trans_table.));

%put #### Trans_Table is &Trans_Table.;
%put #### Trans_Table_sql is &Trans_Table_sql.;

Log:

27         %let _output0=IFRSITSS.META_DATA;
28         %let _output1=IFRSITSS.INSURANCE;
29         %let _output2=IFRSITSS.AUDIT_TRAIL;
30         
31         %* Set the FIXED values ;
32         %let meta_table=IFRSITSS.META_DATA;
33         %let audit_table=IFRSITSS.AUDIT_TRAIL;
34         
35         %* Calculate TRANS_TABLE by removing the FIXED table names from the list of all three ;
36         %let trans_table=&_output0 &_output1 &_output2;
37         %let trans_table=%sysfunc(tranwrd(&trans_table,&meta_table,));
38         %let trans_table=%sysfunc(tranwrd(&trans_table,&audit_table,));
39         %let trans_table_sql=%substr(&trans_table.,10,%length(&trans_table.));
WARNING: Argument 3 to macro function %SUBSTR is out of range.
40         
41         %put #### Trans_Table is &Trans_Table.;
#### Trans_Table is IFRSITSS.INSURANCE
42         %put #### Trans_Table_sql is &Trans_Table_sql.;
#### Trans_Table_sql is INSURANCE

[FreelanceReinh]

Hello @David_Billa,

To take the substring starting at the 10th character, just omit the third argument of the %SUBSTR function, which does not specify the position of the last character of the substring to be extracted, but the length of the substring (see documentation):

%let trans_table_sql=%substr(&trans_table.,10);

[David_Billa]

@FreelanceReinh Thank you. Another question is can we deal this program parsing function instead of tranwrd?

[FreelanceReinh]

---

@David_Billa wrote:

Another question is can we deal this program parsing function instead of tranwrd?

---

How about using PRXCHANGE and %SCAN?

%let trans_table_sql=%scan(%sysfunc(prxchange(s/&meta_table|&audit_table//,-1,&trans_table)),2);

Here, the effect of PRXCHANGE is similar to that of TRANWRD in your code: It deletes &meta_table and &audit_table from &trans_table. Finally, %SCAN selects the second "word" from the resulting string, using default delimiters (which include the period, ".") as word delimiters.

[David_Billa · Accepted solution]

Great.Don't we get the same results even if we remove %scan function in
your proposed code?

[FreelanceReinh]

---

@David_Billa wrote:
Great.Don't we get the same results even if we remove %scan function in
your proposed code?

---

Not with your sample data:

122  %put Without '%SCAN': %sysfunc(prxchange(s/&meta_table|&audit_table//,-1,&trans_table));
Without '%SCAN':  IFRSITSS.INSURANCE
123  %put With '%SCAN': %scan(%sysfunc(prxchange(s/&meta_table|&audit_table//,-1,&trans_table)),2);
With '%SCAN': INSURANCE

(Note the inserted minus sign in the second argument of the PRXCHANGE function.)

[David_Billa]

Why we need that minus sign in second argument?

[FreelanceReinh]

---

@David_Billa wrote:
Why we need that minus sign in second argument?

---

Since we need two replacements (in your example), PRXCHANGE must apply the regular expression to the remaining string after the first matched substring has been replaced (i.e. deleted). The -1 in the second argument tells SAS to continue the replacements until the end of the string in the third argument is reached, so it would work for more than two replacements as well.

[Tom]

If you want to extract the member name from a dataset reference in LIBREF.MEMNAME form use the %SCAN() function.

%let memname=%scan(&dsname,-1,.);

Note this will work whether or not the DSNAME macro variable has the libref prefix or not.

[Tom]

You appear to be trying to get the member name from a dataset reference.  Just use %SCAN().

%let trans_table_sql=%scan(&trans_table.,-1,.);

Which will take everything after the last period.  If there is no period (you are using a one level name to reference a WORK dataset) then the whole string is returned.  Which is what you want.