%scan and %nrbquote

[grozzik]

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)

[RW9]

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;

[Ksharp]

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) ;

[Kurt_Bremser]

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.

[grozzik]

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 ';'.

[Kurt_Bremser]

---

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

[grozzik]

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.

[Kurt_Bremser]

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.

[grozzik]

Uh oh, newbie mistake.

Thx a lot! Now it works with scan too.