two proc sql in macro loop

[Andres_Fuentes1]

hi to all, i want to create a loop using two proc sql with macro variable in sas guide. 

i try this but cant compile.

%MACRO SQLLLOP;
%DO FECHA=22646 %TO  22650;
PROC SQL;
CREATE TABLE WORK.TEST_TABLE&FECHA.  AS
SELECT

t1.V1,
t1.SEX,
t1.V2

FROM DATA_HAVE;

WHERE t1.V1 = &FECHA.;

QUIT;

/* OTHER PROC SQL*/

PROC SQL;

CREATE TABLE WORK.GROUP_TABLE&FECHA.  AS

SELECT t1.SEX,

COUNT(t1.V1) AS TOTAL,

FROM  WORK.TEST_TABLE&FECHA.

GROUP BY t1.SEX;

QUIT;

%END

%MEND;

[PaigeMiller]

Please turn on the macro debugging option.

options mprint;

Then run your code again and SHOW US the log. We need to see the ENTIRE log for this macro, with nothing chopped out, every single line for this macro.

Please copy the log as text and paste it into the window that appears when you click on the </> icon.

[ballardw]

A Proc SQL Create Table  or Select statement has exactly one ;  at the end. Remove the highlighted one below.

AND supply the Alias:

PROC SQL;
CREATE TABLE WORK.TEST_TABLE&FECHA. AS
SELECT

t1.V1,
t1.SEX,
t1.V2

FROM DATA_HAVE AS T1;

WHERE t1.V1 = &FECHA.;

QUIT;

/* OTHER PROC SQL*/

PROC SQL;

CREATE TABLE WORK.GROUP_TABLE&FECHA. AS

t1.SEX,

COUNT(t1.V1) AS TOTAL,

FROM WORK.TEST_TABLE&FECHA. AS T1

GROUP BY t1.SEX;

QUIT;

[Michael_Harper]

In your 2nd PROC SQL, you are missing a SELECT

CREATE TABLE WORK.GROUP_TABLE&FECHA.  AS

SELECT

t1.SEX,

COUNT(t1.V1) AS TOTAL, ...

[Andres_Fuentes1]

Sorry, i forgot copy that, but thats not the problem. i'm ediing the post.

[ballardw]

---

@Andres_Fuentes1 wrote:
Sorry, i forgot copy that, but thats not the problem. i'm ediing the post.

---

Save your self some work. Submit the code for compilation. Then go to the LOG and copy the submitted code along with all the notes, messages and errors. Then on the forum open a text box using the </> icon at the top of the message window and paste the copied text.

That way there is NO question about what was actually submitted. When you retype stuff all sorts of other errors and problems appear that hide the actual problem with your code. The LOG does not lie. It may confuse on occasion but doesn't lie.

[Tom · Accepted solution]

The macro will not compile because the %END statement is missing the closing semicolon.

The SAS code the macro is generating is gibberish also but it is the mistake with the %END statement that is causing the macro not be compiled.

1162  %MACRO SQLLLOP;
1163  %DO FECHA=22646 %TO  22650;
1164  PROC SQL;
1165  CREATE TABLE WORK.TEST_TABLE&FECHA.  AS
1166  SELECT
1167
1168  t1.V1,
1169  t1.SEX,
1170  t1.V2
1171
1172  FROM DATA_HAVE;
1173
1174  WHERE t1.V1 = &FECHA.;
1175
1176  QUIT;
1177
1178  /* OTHER PROC SQL*/
1179
1180  PROC SQL;
1181
1182  CREATE TABLE WORK.GROUP_TABLE&FECHA.  AS
1183
1184  SELECT t1.SEX,
1185
1186  COUNT(t1.V1) AS TOTAL,
1187
1188  FROM  WORK.TEST_TABLE&FECHA.
1189
1190  GROUP BY t1.SEX;
1191
1192  QUIT;
1193
1194  %END
1195
1196  %MEND;
NOTE: Extraneous text on %END statement ignored.

Since the %END statement "ate" the %MEND statement you need to submit another one.  Or restart your SAS session and try again.