topic Re: two proc sql in macro loop in SAS Enterprise Guide

two proc sql in macro loop

Andres_Fuentes1 — Tue, 05 Apr 2022 20:11:35 GMT

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;

Re: two proc sql in macro loop

PaigeMiller — Tue, 05 Apr 2022 19:48:26 GMT

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.

Re: two proc sql in macro loop

ballardw — Tue, 05 Apr 2022 20:53:52 GMT

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;

Re: two proc sql in macro loop

Michael_Harper — Tue, 05 Apr 2022 20:08:28 GMT

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, ...

Re: two proc sql in macro loop

Andres_Fuentes1 — Tue, 05 Apr 2022 20:10:11 GMT

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

Re: two proc sql in macro loop

ballardw — Tue, 05 Apr 2022 20:50:53 GMT

@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.

Re: two proc sql in macro loop

Tom — Tue, 05 Apr 2022 21:53:10 GMT

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.