Problem with scan function inside a macro

[chris2377]

Hi,

I've written the following macro to import all Excel sheets from a file to SAS (see below - the file contains 3 sheets: my_sheet1, my_sheet2, my_sheet3). But when I run it, I get the following "ERROR: Macro function %SCAN has too many arguments". Any ideas why?

options mprint mlogic symbolgen;
%macro read_sheets (file);

	libname mydata xlsx "&file";

	proc sql noprint;
		select distinct memname into :sheets_list separated by ","
		from dictionary.tables 
		where libname = "MYDATA";
	quit; /*this gives me a list of sheets*/

	proc sql noprint;
		select count(distinct memname) into :sheets_count
		from dictionary.tables 
		where libname = "MYDATA";
	quit; /*this gives me a number of sheets*/

	%do i = 1 %to &sheets_count; 
		/* The line below should give me the i-th element from the sheet_list, i.e. the name of the i-th sheet, but it gives error instead*/
		%let sheet = %scan(&sheets_list, &i, ","); 
		
		proc import datafile = "&file" out=data_&sheet dbms = xlsx replace;
			sheet = "&sheet";
		run;

	%end;

%mend;

%read_sheets (C:\my_file.xlsx);

Just in case the problem was with the third argument of the function (but I don't why), I removed it,

options mprint mlogic symbolgen;
%macro read_sheets (file);

	libname mydata xlsx "&file";

	proc sql noprint;
		select distinct memname into :sheets_list separated by ","
		from dictionary.tables 
		where libname = "MYDATA";
	quit; /*this gives me a list of sheets*/

	proc sql noprint;
		select count(distinct memname) into :sheets_count
		from dictionary.tables 
		where libname = "MYDATA";
	quit; /*this gives me a number of sheets*/

	%do i = 1 %to &sheets_count; 
		/* No more third argument*/
		%let sheet = %scan(&sheets_list, &i); 
		
		proc import datafile = "&file" out=data_&sheet dbms = xlsx replace;
			sheet = "&sheet";
		run;

	%end;

%mend;

%read_sheets (C:\my_file.xlsx);

but this time got error:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
operand is required. The condition was: MY_SHEET2
ERROR: Argument 2 to macro function %SCAN is not a number.
ERROR: The macro READ_SHEETS will stop executing.

[Tom · Accepted solution]

The visible issue is why are you telling scan to use both comma and the double quote character as delimiters?  

The less obvious one is the value of the macro variable contains commas, which the macro processor is seeing as the separator used by the %SCAN() function.  So you need to add some macro quoting to the string being scanned.

%let sheet = %scan(%quote(&sheets_list), &i, %str(,)); 

And finally if the sheet names are longer than 27 characters then adding a five character prefix might yield dataset names that are longer than the 32 character limit.

[chris2377]

Thanks @Tom 

I've simply changed the separator to semicolon and it works now. I didn't think that using comma can be interpreted this way by SAS. Thanks for this explanation.

Regarding your first question: "why are you telling scan to use both comma and the double quote character as delimiters"

I thought the separators were supposed to be provided in the quotes. And, after changing separator to semicolon this code works:

%let sheet = %scan(&sheets_list, &i, ";"); 

but this one (without quotes) doesn't:

%let sheet = %scan(&sheets_list, &i, ;); 

But I guess it's similar issue here - SAS reads the semicolon as the end of the command?

But when I use as delimiter something different than tricky commas or semicolons, e.g. $, both versions (with and without "") work fine. So, should I use "" or not in the future? Or it doesn't matter?

%let sheet = %scan(&sheets_list, &i, $); 

%let sheet = %scan(&sheets_list, &i, "$"); 

[Tom]

When you tell SCAN or %SCAN() to use multiple characters then any of the characters will be treated as a delimiter.

So consider the string:

a,10,"He said ""hello"" to me",45

If you specify comma as the delimiter you have 4 "words".

If you specify both comma and quotes then you have 6 "words"  'a' '10' 'He said ' 'hello' ' to me' and '45'.

If the variable you are scanning can never contain the quotes then adding the quotes does no harm in terms of scanning.  But it does mask the comma or semicolon from the macro processor so that it is seen as data and not part of the syntax.

Just use %STR() to mask the delimiter if it is something that would cause confusion.  Such as a space or comma or semicolon or even & or % 

In your use case using | is probably the best since it is not seen as anything special to the macro processor but also cannot be part of dataset name or sheetname.

[chris2377]

@Tom
Many thanks for the explanations

[chris2377]

@Tom 

One more question related to this, as I'm getting confused. Does this what you wrote about using/not using quotation marks apply only to using scan inside macro? I'm asking because I've tried to provide modifier to scan function in data step and it does not work unless I wrap it inside quotation mark:

data have;
input var1 $9.;
datalines;
some|text
;
run;

/* No quotes - this does not work*/
data want1;
	set have;
	firstword = scan(var1, 1, |);
run;

/* This works when I add ""*/
data want2;
	set have;
	firstword = scan(var1, 1, "|");
run;

[Quentin]

Yes.  But note the difference is not using SCAN inside a macro. The difference is using %SCAN, which is a macro function, vs SCAN, which is a SAS language function.  They are two different functions, in two different languages.

The macro language does not use quotes to indicate a string literal.  So "|" represents a string in the macro language that is three characters long.  

The SAS language (e.g. data step language) does use quotes to indicate a string literal. So "|" represents a string in the SAS language that is one character long.

The SAS language needs to use quotes to indicate a string because in the SAS language, the quotes are needed to differentiate between a variable name and a string, e.g.:

Name="Mary"; *Mary is a String;
Name=Mary; *Mary is a dataset variable;

In the macro language, quotes are not needed because the & identifies macro variables:

%let name=Mary;  *Mary is a string;
%let name=&Mary; *Mary is a macro variable;

[chris2377]

@Quentin 

Thanks a lot. Now I get it. 

[Tom]

Note there is no need to run the query twice to count how many observations it found.  PROC SQL already does that for you.

/*this gives me a list of sheets*/
proc sql noprint;
	select distinct memname into :sheets_list separated by ","
	from dictionary.tables 
	where libname = "MYDATA"
  ;
%let sheets_count=&sqlobs;
quit; 

PS Placing comments about what the code is doing before the code instead of after  makes it easier for someone reviewing the code to understand.