Macro debugging - error in macro code

[manya92]

%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

[PaigeMiller]

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

options mprint;

[manya92]

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]

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?

[Astounding]

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]

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.