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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.