Proc SQL Do While Loop

RickGNYC — Tue, 23 Jan 2018 18:53:19 GMT

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")

Re: Proc SQL Do While Loop

Astounding — Tue, 23 Jan 2018 20:10:06 GMT

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.

topic Re: Proc SQL Do While Loop in SAS Programming

Proc SQL Do While Loop

Re: Proc SQL Do While Loop