ERROR: The function SCAN referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments

[SeptemberX]

Hi,

I am recently learning SAS by doing project for my advisor.

Could anyone can help me figure out what is wrong with my code and how to fix it?

proc sql noprint;                             /*firmid is a macro var containing values of udbnum from temp*/

       select udbnum

       into: firmid separated by ','

       from temp;

quit;

Then I want to refer to elements in firmid using the following code

%let fid=%sysfunc(strip(%sysfunc(scan(&firmid,1,','))));

In the real problem, I actually used a loop to scan all the elements in firmid, but the error is the same when I simply put 1,2,3, ... in the scan function

ERROR: The function SCAN referenced by the %SYSFUNC or %QSYSFUNC macro function has too many

       arguments.

ERROR: The function STRIP referenced by the %SYSFUNC or %QSYSFUNC macro function has too few

       arguments.

I will really appreciate it if someone can help.

[SeptemberX]

It turned out that I need use %bquote rather than %str to mask those commas in the macro firmid

[ballardw]

Interesting comment considering your original post doesn't show %str in it.

Did you try %scan?

[SeptemberX]

The followings are what I tried,

%let fid=%sysfunc(strip(%sysfunc(scan(%str(&firmid),1,','))));  not work

%let fid=%sysfunc(strip(%sysfunc(scan(%bquote(&firmid),1,',')))); this works

I will be happy to know what happened here

[ballardw]

You will find when working with macros that you can simplify this drastically using the %scan function.

%let fid = %scan(&firmid,1,',');

And the macro processor almost never needs "strip" .

[Ksharp]

%let fid = %scan(&firmid,1,%str(,));

[Tom · Accepted solution]

You do not need to resort to %SYSFUNC() to scan since there is a built in macro function %SCAN().

In macro code everything is a string, so there is no need to put quotes around string literals to let the parser know you are representing a string.

There is no need to "strip" macro variables.  If for some strange reason you have managed to create a macro variable that has leading and/or trailing unquoted spaces then you can use a simple %LET statement to remove them.  %let x=   a    ; is the same as %let x=a; since the unquoted spaces are ignored.

It is easier in macro coding if you do not use a comma as your delimiter.

proc sql noprint; 

  select udbnum

    into :firmid separated by '|'

    from temp

  ;

%let nobs=&sqlobs ;

quit;

%do i=1 %to &nobs ;

  %let fid=%scan(&firmid,&i,|) ;

  ...

%end;