BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SeptemberX
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

6 REPLIES 6
SeptemberX
Fluorite | Level 6

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

ballardw
Super User

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

Did you try %scan?

SeptemberX
Fluorite | Level 6

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 Smiley Happy

ballardw
Super User

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" .

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 8837 views
  • 8 likes
  • 4 in conversation