topic Re: %SUBSTR is out of range warning in SAS Programming

%SUBSTR is out of range warning

David_Billa — Sat, 12 Jun 2021 07:13:23 GMT

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

Re: %SUBSTR is out of range warning

FreelanceReinh — Sat, 12 Jun 2021 07:48:43 GMT

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

Re: %SUBSTR is out of range warning

David_Billa — Sat, 12 Jun 2021 08:17:25 GMT

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

Re: %SUBSTR is out of range warning

FreelanceReinh — Sat, 12 Jun 2021 10:15:47 GMT

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

Re: %SUBSTR is out of range warning

David_Billa — Sat, 12 Jun 2021 09:54:24 GMT

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

Re: %SUBSTR is out of range warning

FreelanceReinh — Sat, 12 Jun 2021 10:25:59 GMT

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

Re: %SUBSTR is out of range warning

David_Billa — Sat, 12 Jun 2021 15:40:24 GMT

Why we need that minus sign in second argument?

Re: %SUBSTR is out of range warning

Tom — Sat, 12 Jun 2021 16:08:49 GMT

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.

Re: %SUBSTR is out of range warning

Tom — Sat, 12 Jun 2021 16:10:57 GMT

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.

Re: %SUBSTR is out of range warning

FreelanceReinh — Sat, 12 Jun 2021 16:50:05 GMT

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