BookmarkSubscribeRSS Feed
manya92
Fluorite | Level 6
%macro icodes ;
 403        
 404        proc sql ;
 405        create table _01_itable2 as
 406        %do i = 1 %to 14 ;
 407        select distinct enrolid, dx&i. as code
 408        from derived._01_itable
 409        %if &i. le 14 %then %do ;
 410        union
 411        %end ;
 412        %end ;
 413        ;
 414        quit;
 415        
 416        %mend icodes ;
 417        %icodes;/*7716186*/
 NOTE: Line generated by the invoked macro "ICODES".
 417           ;
               _
               22
               76
 ERROR 22-322: Syntax error, expecting one of the following: (, ALL, CORR, CORRESPONDING, SELECT.  
 
 ERROR 76-322: Syntax error, statement will be ignored.

How to solve this kind of an error

 

5 REPLIES 5
PaigeMiller
Diamond | Level 26

Once again, I advise you to issue the command BEFORE you run the macro

 

options mprint;

  

--
Paige Miller
manya92
Fluorite | Level 6

I used that and thats how the macro resolved :- 

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         options mprint ;
 74         %macro ocodes ;
 75         
 76         proc sql ;
 77         create table _01_otbale2 as
 78         %do i = 1 %to 4 ;
 79         select distinct enrolid, dx&i. as code
 80         from derived._01_itable ;
 81         %if &i. le 4 %then %do ;
 82         union;
 83         %end ;
 84         %end ;
 85         ;
 86         quit;
 87         
 88         %mend ocodes ;
 89         %ocodes;/*2617763*/
 MPRINT(OCODES):   proc sql ;
 MPRINT(OCODES):   create table _01_otbale2 as select distinct enrolid, dx1 as code from derived._01_itable ;
 NOTE: Table WORK._01_OTBALE2 created, with 675137 rows and 2 columns.
 
 NOTE: Line generated by the invoked macro "OCODES".
 89              union;
                 _____
                 180
 MPRINT(OCODES):   union;
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 MPRINT(OCODES):   select distinct enrolid, dx2 as code from derived._01_itable ;
 NOTE: Statement not executed due to NOEXEC option.
 NOTE: Line generated by the invoked macro "OCODES".
 89              union;
                 _____
                 180
 MPRINT(OCODES):   union;
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 MPRINT(OCODES):   select distinct enrolid, dx3 as code from derived._01_itable ;
 NOTE: Statement not executed due to NOEXEC option.
 NOTE: Line generated by the invoked macro "OCODES".
 89              union;
                 _____
                 180
 MPRINT(OCODES):   union;
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 MPRINT(OCODES):   select distinct enrolid, dx4 as code from derived._01_itable ;
 NOTE: Statement not executed due to NOEXEC option.
 NOTE: Line generated by the invoked macro "OCODES".
 89              union;
                 _____
                 180
 MPRINT(OCODES):   union;
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 MPRINT(OCODES):   ;
 MPRINT(OCODES):   quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.22 seconds
       cpu time            0.34 seconds
       
 90         
 91         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 104        
PaigeMiller
Diamond | Level 26

And now the error (and the fix) should be obvious. Is it not?

 

By the way, your code has changed from the first post in this thread to the one just above ... why is that?

--
Paige Miller
Astounding
PROC Star

You're changing the code each time you run it.  Makes it pretty difficult to debug because you could be introducing errors each time you change it.

 

Originally you had this inside the %DO loop:

 

union

 

That's correct.  Then you changed it to:

 

union;

 

That's incorrect.  (At the same time, you decreased the number of iterations from 14 to 4.)

 

The original problem was that you added "union" too many times.  When you combine 14 data sets, you need "union" only 13 times.  The original code should have read:

 

%if &i. lt 14 %then %do;

 

Using "le" instead of "lt" was the original mistake.

Reeza
Super User

You have line numbers in that code or just posted here?

 

When a macro is not working you need to use the debugging options such as:

options mprint symbolgen;

BEFORE you run the macros. Then the log will have the full code and show what values are resolving to. 

This makes it much, much easier to debug. 

 

As others have mentioned, a PROC TRANSPOSE is faster and easier though. 

 

PS. Please note that I've updated your subject lines for you today. Please try and provide descriptive subject lines to help future users when they're searching for answers. This forum is intended to answer your question, but also provide a repository for users who can then find solutions when they have similar problems. 

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
  • 5 replies
  • 717 views
  • 0 likes
  • 4 in conversation