hi,
c:\xx\my\dc\output
i want to scan last 3 words
expected output:
newvar=my\dc\output
How can i do that?
data have;
path='c:\xx\my\dc\output';
newvar=substr(path,index(path,scan(path,3,'\')));
run;
%let first=%sysfunc(substr(&prodpro,index(&prodpro,scan(&prodpro,3,'\'))));
%put &first;
its giving me error
ERROR: Argument 2 to function SUBSTR referenced by the %SYSFUNC or %QSYSFUNC macro function is
not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
Please try
%let prodpro=c:\xx\my\dc\output;
%let first=%substr(&prodpro,%index(&prodpro,%scan(&prodpro,3,\)));
%put &first;
Hi @sam1231,
If the number of words in the path (&prodpro) may vary (e.g. c:\xx\yy\zz\dc\output) or if duplicate words may be involved (e.g. c:\xx\xx\dc\output), I would prefer
%let first=%scan(&prodpro,-3,\)\%scan(&prodpro,-2,\)\%scan(&prodpro,-1,\);
Edit: I see, Astounding was faster. 🙂
data _null_;
x='c:\xx\my\dc\output';
call scan(x,-3,p,l,'\');
want=substr(x,p);
put x= want=;
run;
Hi @sam1231
Scan from end of string to cope with a different number of path elements. Using -3 will give the third element seen from end of string.
If data step functions are used in macro code, as you do in your first answer, all functions must be inclosed in %sysfunc, not the whole expression, as shown in the second example.
Here are 4 different ways of getting the last 3 elements. Note that only the data step is "secure", the other 3 will give a wrong result if there are several occurences of the same directory name in the path.
Edited because @FreelanceReinh pointed out a major flaw in the code. it worked not on 'c:\amylase\yy\my\dc\output', because the index function found 'my' in 'amylase'. This is fixed so index is searching for '\my\'
/* Macro code */
%let var = c:\amylase\yy\my\dc\output;
* with macro functions;
%let newvar = %substr(&var,%index(&var,\%scan(&var,-3,\)\)+1);
%put &=newvar;
* with sysfunc + data step functions;
%let newvar = %sysfunc(substr(&var,%sysfunc(index(&var,\%sysfunc(scan(&var,-3,\))\))+1));
%put &=newvar;
/* From existing data */
data have;
var = 'c:\amylase\yy\my\dc\output';
run;
* Data step;
data want; set have;
call scan(var,-3,p,l,'\');
newwar = substr(var,p);
run;
* Proc sql;
proc sql;
create table want2 as select
var,
substr(var,index(var,'\'||scan(var,-3,'\')||'\')+1) as newvar
from have;
quit;
The risk of using %index remains that the word is found earlier than intended, e.g.
%let var = c:\amylase\my\dc\output;
Yes, even "\word\" could be found prematurely if it occurs more than once (as in c:\xx\xx\dc\output).
Here's a macro version of the CALL SCAN solution:
%let n=-3;
%let p=.;
%let l=.;
%let d=\;
%syscall scan(prodpro,n,p,l,d);
%let first=%substr(&prodpro,&p);
%put &=first;
Thank you.
I just came to think of another method to avoid the index function:
data _null_;
var = 'c:\aa\bb\ff\tt\dir';
newvar = prxchange('s/(.*\\)(\w*\\\w*\\\w*$)/$2/',-1,trim(var));
put newvar;
run;
%let var = c:\aa\bb\ff\tt\dir;
%let newvar = %sysfunc(prxchange(s/(.*\\)(\w*\\\w*\\\w*$)/$2/,-1,&var));
%put &newvar;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: