BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
David_Billa
Rhodochrosite | Level 12

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
1 ACCEPTED SOLUTION

Accepted Solutions
David_Billa
Rhodochrosite | Level 12
Great.Don't we get the same results even if we remove %scan function in
your proposed code?

View solution in original post

9 REPLIES 9
FreelanceReinh
Jade | Level 19

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
Rhodochrosite | Level 12

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

FreelanceReinh
Jade | Level 19

@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_tableFinally, %SCAN selects the second "word" from the resulting string, using default delimiters (which include the period, ".") as word delimiters.

David_Billa
Rhodochrosite | Level 12
Great.Don't we get the same results even if we remove %scan function in
your proposed code?
FreelanceReinh
Jade | Level 19

@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
Rhodochrosite | Level 12
Why we need that minus sign in second argument?
FreelanceReinh
Jade | Level 19

@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
Super User Tom
Super User

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
Super User Tom
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2824 views
  • 9 likes
  • 3 in conversation