topic Re: Looping inside proc sql with scan function error in SAS Programming

Looping inside proc sql with scan function error

boyandhisrobot — Wed, 17 Oct 2018 17:32:42 GMT

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!

Re: Looping inside proc sql with scan function error

boyandhisrobot — Wed, 17 Oct 2018 17:45:16 GMT

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.

Re: Looping inside proc sql with scan function error

novinosrin — Wed, 17 Oct 2018 17:46:45 GMT


%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;

Re: Looping inside proc sql with scan function error

Reeza — Wed, 17 Oct 2018 17:51:37 GMT

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

Re: Looping inside proc sql with scan function error

boyandhisrobot — Wed, 17 Oct 2018 18:23:56 GMT

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.

Re: Looping inside proc sql with scan function error

novinosrin — Wed, 17 Oct 2018 18:25:55 GMT

did you make the change here?

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

and try running

options mlogic mprint symbolgen;

Re: Looping inside proc sql with scan function error

mkeintz — Wed, 17 Oct 2018 18:30:26 GMT

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;

Re: Looping inside proc sql with scan function error

boyandhisrobot — Wed, 17 Oct 2018 18:37:25 GMT

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.

Re: Looping inside proc sql with scan function error

boyandhisrobot — Wed, 17 Oct 2018 18:48:08 GMT

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!

Re: Looping inside proc sql with scan function error

ballardw — Wed, 17 Oct 2018 19:09:26 GMT

@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.));