scan last 3 words

[sam1231]

hi,

c:\xx\my\dc\output

i want to scan last 3 words

expected output:

newvar=my\dc\output

How can i do that?

[Jagadishkatam]

data have;
path='c:\xx\my\dc\output';
newvar=substr(path,index(path,scan(path,3,'\')));
run;

[sam1231]

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

[Jagadishkatam]

Please try

%let prodpro=c:\xx\my\dc\output;
%let first=%substr(&prodpro,%index(&prodpro,%scan(&prodpro,3,\)));
%put &first;

[Astounding]

Try:

%let p = c:\xx\my\dc\output;
%let last3 =
%scan(&p,-3,\)\%scan(&p,-2,\)\%scan(&p,-1,\);

[FreelanceReinh]

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

[Ksharp]

data _null_; 
x='c:\xx\my\dc\output';
call scan(x,-3,p,l,'\');
want=substr(x,p);
put x= want=;
run;

[ErikLund_Jensen]

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;

[FreelanceReinh]

The risk of using %index remains that the word is found earlier than intended, e.g.

%let var = c:\amylase\my\dc\output;

[ErikLund_Jensen]

Thang you - You are so right! - I have edited the post to make it better, but call scan seems to be the only secure method. There must be som way to do something similar in macro code or sql expressions, but I havn't found it yet.

[FreelanceReinh]

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;

---

[ErikLund_Jensen]

Hi @FreelanceReinh 

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;