topic Re: scan last 3 words in SAS Programming

scan last 3 words

sam1231 — Sat, 04 May 2019 06:06:27 GMT

hi,

c:\xx\my\dc\output

i want to scan last 3 words

expected output:

newvar=my\dc\output

How can i do that?

Re: scan last 3 words

Jagadishkatam — Sat, 04 May 2019 07:04:42 GMT

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

Re: scan last 3 words

sam1231 — Sat, 04 May 2019 07:13:54 GMT

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

Re: scan last 3 words

Jagadishkatam — Sat, 04 May 2019 07:34:47 GMT

Please try

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

Re: scan last 3 words

Astounding — Sat, 04 May 2019 08:19:38 GMT

Try:

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

Re: scan last 3 words

FreelanceReinh — Sat, 04 May 2019 08:27:44 GMT

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

Re: scan last 3 words

Ksharp — Sat, 04 May 2019 11:20:27 GMT

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

Re: scan last 3 words

ErikLund_Jensen — Sat, 04 May 2019 13:58:41 GMT

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;

Re: scan last 3 words

FreelanceReinh — Sat, 04 May 2019 13:02:39 GMT

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

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

Re: scan last 3 words

ErikLund_Jensen — Sat, 04 May 2019 14:02:22 GMT

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.

Re: scan last 3 words

FreelanceReinh — Sat, 04 May 2019 14:54:52 GMT

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;

Re: scan last 3 words

ErikLund_Jensen — Sat, 04 May 2019 16:36:38 GMT

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;