Macro errors

[saspert]

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

[art297]

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

[saspert]

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.

[Tom]

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.

[saspert]

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

[art297 · Accepted solution]

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.

[Doc_Duke]

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

[saspert]

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