ERROR: Macro function %SCAN has too many arguments. after using prxchange

[bianco]

After having modified the field entries using prxchange function and saving the result in a macro variable the macro function %scan()

gives the following error: ERROR: Macro function %SCAN has too many arguments.

Here a working example:

data AAA;
input name &$20. age ;
datalines;
Philip- Russel 14
Ronald- Marc 15
Mary- Jane 16
;

proc sql;
create table AAA as
select name, prxchange('s/(\w+)- (\w+)/$1, $2/',-1, name) as name_mod, age
from AAA;
quit;

proc print;
run;

proc sql;
select name, name_mod, age
into :name separated by "~",
:name_mod separated by "~",
:age separated by "~"

from AAA;
quit;

%let sep="~";
%put &name;
%put &name_mod;
%put &age;
%put %scan(&name,2,&sep);
%put %scan(&name_mod,2,&sep);
%put %scan(&age,2,&sep);

Does anyone know why it is happening?

Thank you in advance for your help!

[bianco]

I found the answer:
http://stackoverflow.com/questions/39889137/error-in-simple-sas-macro

[RW9]

Your macro variable NAME_MOD is resolving to a string which contains a comma:

57 %put &name_mod;
Philip, Russel 14

Hence the %scan() looks like:
%scan(Philip, Russel 14,2,&sep.);

and Russel 14 is not a number which the second parameter should be.

The question really however is why your doing it this way in the first place.  You have a dataset - something which contains data - and then you are putting it in macro - which is just there to generate text.  Doesn't make sense.  Macro is not executable code, use Base SAS which is what its for.

[Ksharp]

Use %bquote() to mask comma which is special character for macro facility.


data AAA;
input name &$20. age ;
datalines;
Philip- Russel  14
Ronald- Marc  15
Mary- Jane  16
;
proc sql;
create table AAA as
select name, prxchange('s/(\w+)- (\w+)/$1, $2/',-1, name) as name_mod, age
from AAA;
quit;
proc print;
run;

proc sql;
select name, name_mod, age
into :name separated by "~",
:name_mod separated by "~",
:age separated by "~"

from AAA;
quit;
%let sep=~;
%put &name;
%put &name_mod;
%put &age;
%put %scan(&name,2,&sep);
%put %scan(%bquote(&name_mod),2,&sep);
%put %scan(&age,2,&sep);