Is someone able to tell me why SAS is giving me an error on this macro?
3705 %SCALETABLE2 ('01. WebSat', q1_web_sat_1 ,qtouch_websat_1 ,qgeneric_websat,
3705! TEMP_WEBSAT_US );
NOTE: Line generated by the invoked macro "SCALETABLE2".
3 AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0
3 ! end)) AS TopTwoBox FORMAT=percent9.1, SUM(CASE WHEN ((&VAR1 EQ ('5')) AND (&VAR2 IN ('5'))
-
22
200
3 ! AND (&VAR3 IN ('5'))) THEN 1 ELSE 0 end)/ SUM(CASE WHEN ((&VAR1 IN ('1',
NOTE: Line generated by the invoked macro "SCALETABLE2".
5 '2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN
5 ! ('1','2','3','4','5'))) THEN 1 ELSE 0 end)) AS TopBox FORMAT=percent9.1, SUM(CASE WHEN
-
22
200
5 ! ((&VAR1 EQ ('4')) AND (&VAR2 EQ ('4')) AND (&VAR3 EQ ('4'))) THEN 1 ELSE 0 end)/ SUM(CASE
5 ! WHEN ((
NOTE: Line generated by the invoked macro "SCALETABLE2".
6 &VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN
6 ! ('1','2','3','4','5'))) THEN 1 ELSE 0 end)) AS Four FORMAT=percent9.1, SUM(CASE WHEN ((&VAR1
-
22
200
6 ! EQ ('3')) AND (&VAR2 EQ ('3')) AND (&VAR3 EQ ('3'))) THEN 1 ELSE 0 end)/ SUM(
NOTE: Line generated by the invoked macro "SCALETABLE2".
8 CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3
8 ! IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end)) AS Three FORMAT=percent9.1, SUM(CASE WHEN
-
22
200
8 ! ((&VAR1 IN ('1','2')) AND (&VAR2 IN ('1','2')) AND (&VAR3 IN ('1','2')))
NOTE: Line generated by the invoked macro "SCALETABLE2".
9 THEN 1 ELSE 0 end) / SUM (CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN
9 ! ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end)) AS
-
22
200
9 ! BottomTwoBox FORMAT=percent9.1, SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +,
',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT,
FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE,
LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.
ERROR 200-322: The symbol is not recognized and will be ignored.
The actual Code is below:
%MACRO SCALETABLE2 (TTL, VAR1, VAR2, VAR3, DST_NM);
PROC SQL;
CREATE TABLE &DST_NM AS
SELECT &TTL as Category,
MTH AS Month FORMAT=MONYY7.,
SUM(CASE WHEN ((&VAR1 IN ('4','5')) AND (&VAR2 IN('4','5')) AND (&VAR3 IN ('4','5'))) THEN 1 ELSE 0 end) / SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS TopTwoBox FORMAT=percent9.1,
SUM(CASE WHEN ((&VAR1 EQ ('5')) AND (&VAR2 IN ('5')) AND (&VAR3 IN ('5'))) THEN 1 ELSE 0 end)/ SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS TopBox FORMAT=percent9.1,
SUM(CASE WHEN ((&VAR1 EQ ('4')) AND (&VAR2 EQ ('4')) AND (&VAR3 EQ ('4'))) THEN 1 ELSE 0 end)/ SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS Four FORMAT=percent9.1,
SUM(CASE WHEN ((&VAR1 EQ ('3')) AND (&VAR2 EQ ('3')) AND (&VAR3 EQ ('3'))) THEN 1 ELSE 0 end)/ SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS Three FORMAT=percent9.1,
SUM(CASE WHEN ((&VAR1 IN ('1','2')) AND (&VAR2 IN ('1','2')) AND (&VAR3 IN ('1','2'))) THEN 1 ELSE 0 end) / SUM (CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS BottomTwoBox FORMAT=percent9.1,
SUM(CASE WHEN ((&VAR1 IN ('1','2','3','4','5')) AND (&VAR2 IN ('1','2','3','4','5')) AND (&VAR3 IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS Base
FROM TEMP
GROUP BY 2
;
QUIT;
RUN;
%MEND;
Syntax works for me. I suspect that the actual error is earlier in the program. Most likely missmatched () or quotes.
326 data temp;
327 length q1_web_sat_1 qtouch_websat_1 qgeneric_websat $1;
328 length mth 8;
329 run;
NOTE: Variable q1_web_sat_1 is uninitialized.
NOTE: Variable qtouch_websat_1 is uninitialized.
NOTE: Variable qgeneric_websat is uninitialized.
NOTE: Variable mth is uninitialized.
NOTE: The data set WORK.TEMP has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
330 options mprint;
331 %SCALETABLE2 ('01. WebSat', q1_web_sat_1 ,qtouch_websat_1 ,qgeneric_websat,
332 TEMP_WEBSAT_US );
MPRINT(SCALETABLE2): PROC SQL;
MPRINT(SCALETABLE2): CREATE TABLE TEMP_WEBSAT_US AS SELECT '01. WebSat' as Category, MTH AS Month FORMAT=MONYY7., SUM(CASE WHEN ((q1_web_sat_1 IN
('4','5')) AND (qtouch_websat_1 IN('4','5')) AND (qgeneric_websat IN ('4','5'))) THEN 1 ELSE 0 end) / SUM(CASE WHEN ((q1_web_sat_1 IN
('1','2','3','4','5')) AND (qtouch_websat_1 IN ('1','2','3','4','5')) AND (qgeneric_websat IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS TopTwoBox
FORMAT=percent9.1, SUM(CASE WHEN ((q1_web_sat_1 EQ ('5')) AND (qtouch_websat_1 IN ('5')) AND (qgeneric_websat IN ('5'))) THEN 1 ELSE 0 end)/ SUM(CASE
WHEN ((q1_web_sat_1 IN ('1','2','3','4','5')) AND (qtouch_websat_1 IN ('1','2','3','4','5')) AND (qgeneric_websat IN ('1','2','3','4','5'))) THEN 1
ELSE 0 end) AS TopBox FORMAT=percent9.1, SUM(CASE WHEN ((q1_web_sat_1 EQ ('4')) AND (qtouch_websat_1 EQ ('4')) AND (qgeneric_websat EQ ('4'))) THEN 1
ELSE 0 end)/ SUM(CASE WHEN ((q1_web_sat_1 IN ('1','2','3','4','5')) AND (qtouch_websat_1 IN ('1','2','3','4','5')) AND (qgeneric_websat IN
('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS Four FORMAT=percent9.1, SUM(CASE WHEN ((q1_web_sat_1 EQ ('3')) AND (qtouch_websat_1 EQ ('3')) AND
(qgeneric_websat EQ ('3'))) THEN 1 ELSE 0 end)/ SUM(CASE WHEN ((q1_web_sat_1 IN ('1','2','3','4','5')) AND (qtouch_websat_1 IN ('1','2','3','4','5'))
AND (qgeneric_websat IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS Three FORMAT=percent9.1, SUM(CASE WHEN ((q1_web_sat_1 IN ('1','2')) AND
(qtouch_websat_1 IN ('1','2')) AND (qgeneric_websat IN ('1','2'))) THEN 1 ELSE 0 end) / SUM (CASE WHEN ((q1_web_sat_1 IN ('1','2','3','4','5')) AND
(qtouch_websat_1 IN ('1','2','3','4','5')) AND (qgeneric_websat IN ('1','2','3','4','5'))) THEN 1 ELSE 0 end) AS BottomTwoBox FORMAT=percent9.1,
SUM(CASE WHEN ((q1_web_sat_1 IN ('1','2','3','4','5')) AND (qtouch_websat_1 IN ('1','2','3','4','5')) AND (qgeneric_websat IN ('1','2','3','4','5')))
THEN 1 ELSE 0 end) AS Base FROM TEMP GROUP BY 2 ;
NOTE: Table WORK.TEMP_WEBSAT_US created, with 1 rows and 8 columns.
MPRINT(SCALETABLE2): QUIT;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.63 seconds
cpu time 0.06 seconds
MPRINT(SCALETABLE2): RUN;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.