BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
saspert
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

7 REPLIES 7
art297
Opal | Level 21

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
Pyrite | Level 9

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
Super User Tom
Super User

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
Pyrite | Level 9

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
Opal | Level 21

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
Rhodochrosite | Level 12

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
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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