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;
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.
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.
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;
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 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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.