topic Re: %scan and %nrbquote in SAS Programming

%scan and %nrbquote

grozzik — Thu, 29 Sep 2016 10:00:36 GMT

Hello, experts.

I have a variable text, that contains string '%let blablabla select * from tablename where blablabla; asdsad'.

And i want to cut all before word 'select' and after first semicolon.

I try next statements:

%let text2 = %scan(%nrbquote(&text), 1, %str(; ));

%let ind = %index(%qupcase(&text2), SELECT);

%let cut_text = %substr(%nrbquote(&text2), &ind);

And after %scan function my variable text2 contains nothing judging by log. If i try another string in text (for example '%%asd select ...;') it works, but with %let at start doesn't.

Which function have i to use instead %nrbquote and %scan? Help me plz)

Re: %scan and %nrbquote

RW9 — Thu, 29 Sep 2016 10:04:14 GMT

Why are you putting code in a macro variable? To my mind you are opening a whole can of worms. Code can contain any number of special characters, syntax etc. all of which will break macro.

You will find that doing this data processing is far easier in Base SAS, macro is never simpler and always makes more complicated code:

data want;
  tmp='%let blablabla select * from tablename where blablabla; asdsad';
  result=substr(tmp,index(tmp,'select')+6);
run;

Re: %scan and %nrbquote

Ksharp — Thu, 29 Sep 2016 10:18:47 GMT

How about this one:


%let text=%nrstr(%let blablabla select * from tablename where blablabla; asdsad);
%put %nrbquote(&text) ;

%let text2 = %qscan(%nrbquote(&text), 1, %str(;));
%put %nrbquote(&text2) ;

%let ind = %index(%qupcase(&text2),%str( SELECT ));
%put %nrbquote(&ind) ;

%let cut_text = %qsubstr(%nrbquote(&text2),&ind);
%put %nrbquote(&cut_text) ;

Re: %scan and %nrbquote

Kurt_Bremser — Thu, 29 Sep 2016 10:23:16 GMT

And to satisfy your second requirement, just add a scan() call to @RW9s code:

data want;
  tmp='%let blablabla select * from tablename where blablabla; asdsad';
  result=scan(substr(tmp,index(tmp,'select')+6),1,';');
run;

That looks much neater than the eye-cancer-causing macro construct. And works. If you need the result in a macro variable for further use, just use call symput('cut_text',trim(result)) at the end.

Re: %scan and %nrbquote

grozzik — Thu, 29 Sep 2016 11:31:29 GMT

Thx to all! My problem's solved.

Do all text transformations in data step is a really good idea.

But because scan function cuts very large text (i think it has limit for one 'word'), i use substr(text, index_select, index_delim - index_select) where index_select is index of 'select' word and index_delim is index of ';'.

Re: %scan and %nrbquote

Kurt_Bremser — Thu, 29 Sep 2016 12:01:51 GMT

@grozzik wrote:

Thx to all! My problem's solved.

Do all text transformations in data step is a really good idea.

But because scan function cuts very large text (i think it has limit for one 'word'), i use substr(text, index_select, index_delim - index_select) where index_select is index of 'select' word and index_delim is index of ';'.

As long as there is only one semicolon, scan(inval,1,';') should work, no matter how long your string is.

Try this:

data test;
length inval outval $4000;
do i = 1 to 4000;
  substr(inval,i,1) = byte(mod(i,27)+97);
  if mod(i,27) = 26 then substr(inval,i,1) = ' ';
end;
substr(inval,3995,1) = ';';
outval = scan(inval,1,';');
i = length(outval);
run;

To create a very long string with "words" separated by blanks and 1 randomly set semicolon. i (length of outval) will come out as 3994.

Re: %scan and %nrbquote

grozzik — Thu, 29 Sep 2016 12:55:39 GMT

Whole my code with scan looks like this:

data _null_;

length text $32767;

retain text '';

infile "&filepath" end = last;

input;

text = cats(text, _infile_);

if last then do;

ind = find(text, 'select', 'i');

result = scan(substr(text, ind), 1, ';');

call symput ('cut_text', result);

end;

run;

This code doesn't work correct since scan cuts text to ~225 symbols. I don't know why.

Re: %scan and %nrbquote

Kurt_Bremser — Thu, 29 Sep 2016 13:10:56 GMT

You have to include result in the length statement, or SAS will implicitly define it with the default length for scan().

Excerpt from Functions and Call Routines: SCAN Function:

In a DATA step, if the SCAN function returns a value to a variable that has not yet been given a length, then that variable is given a length of 200 characters. If you need the SCAN function to assign to a variable a word that is longer than 200 characters, then you should explicitly specify the length of that variable.

Re: %scan and %nrbquote

grozzik — Thu, 29 Sep 2016 13:18:57 GMT

Uh oh, newbie mistake.

Thx a lot! Now it works with scan too.