DATA Step, Macro, Functions and more

Macro errors

Accepted Solution Solved
Reply
Super Contributor
Posts: 275
Accepted Solution

Macro errors

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


Accepted Solutions
Solution
‎07-19-2011 10:53 AM
PROC Star
Posts: 7,471

Re: Macro errors

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.

View solution in original post


All Replies
PROC Star
Posts: 7,471

Macro errors

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

Super Contributor
Posts: 275

Re: Macro errors

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.

Super User
Super User
Posts: 7,042

Re: Macro errors

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.

Super Contributor
Posts: 275

Re: Macro errors

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

Solution
‎07-19-2011 10:53 AM
PROC Star
Posts: 7,471

Re: Macro errors

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.

Trusted Advisor
Posts: 2,115

Re: Macro errors

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

Super Contributor
Posts: 275

Re: Macro errors

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 4331 views
  • 3 likes
  • 4 in conversation