Proc SQL Do While Loop

Reply
New User
Posts: 1

Proc SQL Do While Loop

Hello,

 

I'm interested in creating a do loop while a macro variable shows there is a error grater than 0.

I'm setting the macro variable as the result of a proc sql query.

here what my code look like:

 

 

%Macro MacroSample(RL=)

%let TableName1 = "1&RL. Table1Name"n;

%let TableName2 = "2&RL. Table2Name"n;

PROC SQL;

CREATE TABLE WORK.&tablename2. AS

SELECT

(SUM(t1.'Connection Difference'n)) AS 'Connection Difference'n

 Into : CheckFlag

FROM WORK.&tablename1. t1;

Where t1.Field1 = &RL.;

%Let CheckFlag = &CheckFlag;

 

QUIT;

/* Macro Begins if Connection Mismaches Fails */

%Do %while (&CheckFlag. >0);

 

/* Begin loop by updating the original table until sum of error is 0*/

PROC SQL;

CREATE TABLE WORK.&tablename1. AS

SELECT

Libref1.Field1,

Libref1.Field2

Calculation1 AS 'Connection Difference'n

FROM WORK.&TableName1. t1;

 QUIT;

 CREATE TABLE WORK.&tablename2. AS

SELECT

(SUM(t1.'Connection Difference'n)) AS 'Connection Difference'n

 Into : CheckFlag

FROM WORK.&tablename1. t1;

%Let CheckFlag = &CheckFlag;

 

%End

%Mend MacroSample;

%MacroSample(RL= "Low")

Super User
Posts: 6,005

Re: Proc SQL Do While Loop

What is the question you are trying to ask?

 

If you are looking for where the errors lie, here is a list for starters.

 

The %MACRO statement is missing a semicolon.

 

The final %END statement is also missing a semicolon.

 

The final PROC SQL is missing a QUIT statement.

 

The first PROC SQL has an extra semicolon.  (The WHERE clause should be part of the SELECT statement, not a separate statement.)

 

Calling the macro with "Low" as the value for &RL gives you incorrect table names, such as  "1"Low" Table1Name"n  Given that you call the macro without quotes around "Low", you would have to change the WHERE clause:

 

Where t1.Field1 = "&RL.";

 

What in the world is Libref1?  Do  you mean t1, such as t1.Field1 ?

 

Of course, there may be more.  That's all that jumped out at first glance.

Ask a Question
Discussion stats
  • 1 reply
  • 107 views
  • 0 likes
  • 2 in conversation