Hello,
I am trying to troubleshoot a fairly simple macro logic. The code is here -
%macro loop(values);
/* Count the number of values in the string */
%let count=%sysfunc(countw(&values));
/* Loop through the total number of values */
%do i = 1 %to &count;
%let value=%qscan(&values,&i);
%put &value;
PROC SQL;
CREATE TABLE DEMOGRAPHICS_BASE_&VALUE AS
(SELECT (COUNT ( DEMOGRAPHICS_BASE.DEMO_PARTICIPATING_USERS)) AS DEMO_PRTCPTNG_USERS
,DEMOGRAPHICS_BASE.DEMO_&VALUE._GROUPING
FROM WORK.DEMOGRAPHICS_BASE AS DEMOGRAPHICS_BASE
INNER JOIN WORK.DEMOGRAPHICS_BASE_100 AS DEMOGRAPHICS_BASE_100 ON (DEMOGRAPHICS_BASE.DEMO_PRCHSR_CODE = DEMOGRAPHICS_BASE_100.DEMO_PRCHSR_CODE)
GROUP BY DEMOGRAPHICS_BASE.DEMO_&VALUE._GROUPING
);
QUIT;
%end;
%mend;
%loop(%str(AGE ETHN GDR))
Here is the log -
MPRINT(LOOP): PROC SQL;
SYMBOLGEN: Macro variable VALUE resolves to AGE
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the invoked macro "LOOP".
56 PROC SQL; CREATE
56 ! TABLE DEMOGRAPHICS_BASE_&VALUE AS (SELECT (COUNT ( DEMOGRAPHICS_BASE.DEMO_PARTICIPATING_USERS)) AS DEMO_PRTCPTNG_USERS
__
202
ERROR 202-322: The option or parameter is not recognized and will be ignored.
NOTE: Line generated by the macro variable "VALUE".
56 DEMOGRAPHICS_BASE_AGE
___
78
SYMBOLGEN: Macro variable VALUE resolves to AGE
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "VALUE".
56 DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING
___ _________
22 202
SYMBOLGEN: Macro variable VALUE resolves to AGE
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
ERROR 78-322: Expecting a '.'.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
NOTE: Line generated by the macro variable "VALUE".
56 DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING
3 The SAS System 10:10 Monday, July 18, 2011
___ _________
22 202
MPRINT(LOOP): CREATE TABLE DEMOGRAPHICS_BASE_AGE AS (SELECT (COUNT ( DEMOGRAPHICS_BASE.DEMO_PARTICIPATING_USERS)) AS
DEMO_PRTCPTNG_USERS ,DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING FROM AS DEMOGRAPHICS_BASE INNER JOIN WORK.DEMOGRAPHICS_BASE_100 AS
DEMOGRAPHICS_BASE_100 ON (DEMOGRAPHICS_BASE.DEMO_PRCHSR_CODE = DEMOGRAPHICS_BASE_100.DEMO_PRCHSR_CODE) GROUP BY
DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING );
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, EXCEPT, GE, GET, GT, GTT, HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN,
OR, OUTER, UNION, ^, ^=, |, ||, ~, ~=.
ERROR 202-322: The option or parameter is not recognized and will be ignored.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(LOOP): QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 48k
Page Faults 1
Page Reclaims 35
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0
SYMBOLGEN: Macro variable VALUES resolves to AGE ETHN GDR
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable VALUE resolves to ETHN
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
ETHN
Note that both DEMOGRAPHICS_BASE_100 and DEMOGRAPHICS_BASE are working fine and are created prior to this macro. I am trying hard for several hours to pinpoint the bug but not able to do so. Appreciate any help
Thanks,
saspert
Making the change I suggested SHOULD have removed at least one of the errors. Is there a reason you use %qscan rather than %scan?. Your log indicates something about certain characters being omitted that may have been masked.
Let's start with the first one. Put quotes around the macro variable in the early line where you are establishing the count macro variable.
Change the line to
%let count=%sysfunc(countw("&values"));
HTH,
Art
Hi Art297,
Thank you for your suggestion. I tried your suggestion but the errors do not change at all. I get the exact same errors in the log.
saspert.
Looks more like an SQL issue than a macro issue.
Try taking the generated command from the MPRINT line and running it standalone.
I get the following error message:
ERROR: File WORK.AS.DATA does not exist.
Hi Tom,
If I understand you correctly, you want to check the SQL query and see if it runs properly (outside of the macro). Is that correct?
14 PROC SQL;
15 CREATE TABLE DEMOGRAPHICS_BASE_AGE AS
16 (SELECT (COUNT ( DEMOGRAPHICS_BASE.DEMO_PARTICIPATING_USERS)) AS DEMO_PRTCPTNG_USERS
17 ,DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING
18 FROM WORK.DEMOGRAPHICS_BASE AS DEMOGRAPHICS_BASE
19 INNER JOIN WORK.DEMOGRAPHICS_BASE_100 AS DEMOGRAPHICS_BASE_100 ON (DEMOGRAPHICS_BASE.DEMO_PRCHSR_CODE =
19 ! DEMOGRAPHICS_BASE_100.DEMO_PRCHSR_CODE)
20 GROUP BY DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING
21 );
NOTE: Table WORK.DEMOGRAPHICS_BASE_AGE created, with 4 rows and 2 columns.
22 QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 1560k
Page Faults 0
Page Reclaims 330
Page Swaps 0
Voluntary Context Switches 4
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0
Thanks,
saspert
Making the change I suggested SHOULD have removed at least one of the errors. Is there a reason you use %qscan rather than %scan?. Your log indicates something about certain characters being omitted that may have been masked.
saspert
Here is your mprint line from your original code (above):
MPRINT(LOOP): CREATE TABLE DEMOGRAPHICS_BASE_AGE AS (SELECT (COUNT ( DEMOGRAPHICS_BASE.DEMO_PARTICIPATING_USERS)) AS
DEMO_PRTCPTNG_USERS ,DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING FROM AS DEMOGRAPHICS_BASE INNER JOIN WORK.DEMOGRAPHICS_BASE_100 AS
DEMOGRAPHICS_BASE_100 ON (DEMOGRAPHICS_BASE.DEMO_PRCHSR_CODE = DEMOGRAPHICS_BASE_100.DEMO_PRCHSR_CODE) GROUP BY
DEMOGRAPHICS_BASE.DEMO_AGE_GROUPING );
Note the "FROM AS" (emphasis added). There is something wrong with your original SQL code that is causing
WORK.DEMOGRAPHICS_BASE to be dropped. I can't see why, but that's were the problem is.
Doc Muhlbaier
Duke
Hi Art,
When I try %scan instead of %qscan, all the errors were resolved. I guess the original code was trying to parse through AGE,GDR,ETHN and had a qscan for that reason. But, when I adapted that code in my EG project, I took out the commas but did not change the %qscan to %scan. Does that sound like the root cause? Appreciate your tip very much.
But to answer your question, your original suggestion of putting the quotes did not help.
Hi Doc,
I looked at the code again. What you pointed was a definitely a bug. But when I am reviewing the code now , it looks as if that is taken care of. Thank you very much.
Tom,Art, Doc - appreciate all the help guys.
Regards,
saspert
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.