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.
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;
It turned out that I need use %bquote rather than %str to mask those commas in the macro firmid
Interesting comment considering your original post doesn't show %str in it.
Did you try %scan?
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
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" .
%let fid = %scan(&firmid,1,%str(,));
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;
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.