BookmarkSubscribeRSS Feed
gauravkodmalwar
Calcite | Level 5

data loop2;

m=1;

     do while(m<=5);

       proc sql;

            select * from temp;

       quit;

     m+1;

     end;

run;

************ Log ************

96          proc sql;

           -

           117

ERROR 117-185: There was 1 unclosed DO block.

97               select * from temp;

98          quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

99        m+1;

100       end;

101  run;

******** Another code ********

data _null_;

do i=1 TO 5 by 1;

%put &i.

end;

run;

************ Log ************

88   data _null_;

89   do i=1 TO 5 by 1;

90   %put &i.

WARNING: Apparent symbolic reference I not resolved.

91   end;

&i. end

92   run;

92   run;

        -

        117

ERROR 117-185: There was 1 unclosed DO block.

6 REPLIES 6
Reeza
Super User

None of the above is valid SAS code.

You can't embed proc sql in a data step in that manner and the i in your second data step is a variable, not a macro variable so you cant refer to it as &i.

Start smaller with whatever you're trying to do. 

gauravkodmalwar
Calcite | Level 5

Thanks Reeza. Reason for second error I am aware of but for first one, why can't I use proc sql inside loop of data step? If not, do I need to write separate macro for proc sql and then call macro in loop? Will that be allowed to loop macro of proc sql?

SASKiwi
PROC Star

There is a significant learning curve with macro and some of the concepts are tricky to understand. Firstly macro is a code generator and any code generated must have correct syntax.

Many macro statements can only be used inside a macro - this includes the %DO statement.

To get you started here is a very simple macro that just writes messages to the log. Note there is no "normal" SAS code in this example just macro statements.

%macro my_message;

  %do i = 1 %to 10;

   %put Hello World Number &i;

   %end;

%mend;

%my_message;

I suggest you try this example and then experiment with it. Also refer to the SAS macro documentation and try running the examples given.

Kurt_Bremser
Super User

Base SAS code (not macro language!) works in steps. A step is either a data step or a proc step. As soon as proc ... or data ... appear in the code, this constitutes a step boundary, and all code before that needs to be valid for the step that was just completed. So, for example, all open do blocks need to have had their complementary end statement before that step boundary.

Repeatedly running steps is one of the basic examples of problems best solved with the macro language.

ie:

%macro repeat_step;

%do i = 1 %to 5;

proc sql;

select * from temp;

quit;

%end;

%mend;

&repeat_step;

Note that the macro variable i is created by the %do macro statement. Macro variables can only be created by macro statements or the data step interface of the macro language (call symput).

gauravkodmalwar
Calcite | Level 5

Thanks Kurt, now I am able to loop proc sql using macro. I should complete basic training first before playing with SAS language but it's really vast to learn & then play. Smiley Happy

Kurt_Bremser
Super User

As for the other code:

data _null_;

do i=1 TO 5 by 1;

%put &i.

end;

run;

Note what SAS will do here:

- the data and do statements are prepared for the data step compiler by the main SAS interpreter

- as soon as the macro language marker % is encountered, the macro processor is invoked and processes everything up to the next semicolon, which comes after the end. Therefore "end" is nothing but a word that is written to the log; since the macro variable i has not been created, a WARNING message is written to the log by the macro processor. No code is generated by the macro processor, which then subsequently hands control back to the main SAS interpreter.

- The SAS interpreter encounters "run;" which constitutes both a step boundary and a "run block"; The data step compiler then detects the missing end for the do and delivers the ERROR message.

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
  • 6 replies
  • 2638 views
  • 1 like
  • 4 in conversation