Looping inside proc sql with scan function error

[boyandhisrobot]

Hi, All,

I'm looking to loop through a list of variables and create a table for each variable.  I'm getting some errors due to the scan function (I think?) and I was hoping somebody could point out where I'm going wrong. Here's my code:

%macro table_creator;
	proc sql;
		%do i=1 %to 33;

		create table 
			%scan(&variables.,&i.)_tab
		as select distinct
			%scan(&variables.,&i.),
			sum(paid_amount) as paid_amount,
                        paid_date
		from 
			add_descriptions5
		group by 
			%scan(&variables.,&i.),paid_date;
	%end;
	quit;
%mend;

I'm receiving a series of notes stating that the tables were created, but then there's the following error:

___22             ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
a missing value, *, BTRIM, INPUT, PUT, SUBSTRING, USER.

after the %scan in my select statement.  and again after the %scan in my group by clause.

Any help would be greatly appreciated.

Thanks!

[boyandhisrobot]

This query seems to run fine if I run it outside of a loop for just one variable.  There's something about the loop that is affecting my scan function.

proc sql; 
	create table %scan(&variables.,1)
	as select distinct 
			%scan(&variables.,1), 		
			sum(paid_amount) as paid_amount,
			paid_date
		from 
			add_descriptions5
		group by 
			%scan(&variables.,1),paid_date;
quit;

This seems to work fine.

[novinosrin]

%macro table_creator;
	proc sql;
		%do i=1 %to 33;

		create table 
			%scan(%superq(variables),&i.)_tab
		as select distinct
			%scan(%superq(variables),&i.),
			sum(paid_amount) as paid_amount,
                        paid_date
		from 
			add_descriptions5
		group by 
			%scan(%superq(variables),&i.),paid_date;
	%end;
	quit;
%mend;

[boyandhisrobot]

Hi, novinosrin,

This didn't seem to work either.  This results in the same exact error.

 create table     %scan(&variables.,&i.)_tb   as select distinct    %scan(%superq(variables),&i.),
                                                                                                               _
                                                                                                               22
45       ! sum(paid_amount) as paid_amount,     paid_date   from
45       !    add_descriptions5   group
3                                                          The SAS System                          11:41 Wednesday, October 17, 2018

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, *, BTRIM, INPUT, PUT, SUBSTRING, USER.  

NOTE 137-205: Line generated by the invoked macro "table_creator".
45          by     %scan(%superq(variables),&i.),paid_date;
                                                _
                                                22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.  

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE 137-205: Line generated by the invoked macro "table_creator".
45             create table     %scan(&variables.,&i.)_tb   as select distinct    %scan(%superq(variables),&i.),
                                                                                                               _
                                                                                                               22
45       ! sum(paid_amount) as paid_amount,    paid_date   from
45       !    add_descriptions5   group
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, *, BTRIM, INPUT, PUT, SUBSTRING, USER.  

NOTE 137-205: Line generated by the invoked macro "table_creator".
45          by     %scan(%superq(variables),&i.),paid_date;
                                                _
                                                22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.  

NOTE: The SAS System stopped processing this step because of errors.

[novinosrin]

did you make the change here?

create table     %scan(&variables.,&i.)_tb 

 and try running

options mlogic mprint symbolgen;

[boyandhisrobot]

I did, yes.  Sorry.  The log seems to state that it is now creating 33 tables titled "_tb" prior to the same error occurring in the select statement.

[Reeza]

PROC MEANS would also do this in a single procedure, probably a bit more straightforward than a macro.

[mkeintz · Accepted solution]

Set options mprint.  Do the loop from 1 to 2, which will give you a good chance to see what is not working in the log.

You can also use the mfile option (and an mfile filename statement) to write the generated sas program text to a file.  Read that back and you'll find the unadulterated code.

I hesitate to endorse the %superq idea, unless there is something very pathological about the VARIABLE macrovar.  What does that macrovar look like?

I made a synthetic dataset and used your macro for I=1 to 2, and got no errors:

data add_descriptions5;
  set sashelp.class (rename=(height=paid_date weight=paid_amount));
run;

%let variables=age name sex;
options mprint;
%macro table_creator;
	proc sql;
		%do i=1 %to 2;

		create table 
			%scan(&variables.,&i.)_tab
		as select distinct
			%scan(&variables.,&i.),
			sum(paid_amount) as paid_amount,
                        paid_date
		from 
			add_descriptions5
		group by 
			%scan(&variables.,&i.),paid_date;
	%end;
	quit;
%mend;
%table_creator;

[boyandhisrobot]

This was helpful, thank you!  I was able to identify that the issue was with the macro variable and not the sql code.  Thank you very much!

[ballardw]

---

@boyandhisrobot wrote:

This was helpful, thank you!  I was able to identify that the issue was with the macro variable and not the sql code.  Thank you very much!

---

Fixed loop index, I= 1 to 33 may not actually match you created macro variable list of "words" that are found with %scan.

Did you find that &variables had fewer than 33 "words"?

To make code like that a bit more flexible %do I = 1 %to %sysfunc(countw(&variables.));