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!
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.
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.