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!
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;
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.
%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;
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.
did you make the change here?
create table %scan(&variables.,&i.)_tb
and try running
options mlogic mprint symbolgen;
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.
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;
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!
@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.));
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.