DATA Step, Macro, Functions and more

Macro Help

Reply
N/A
Posts: 1

Macro Help

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;

Super User
Super User
Posts: 6,499

Re: Macro Help

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;

Ask a Question
Discussion stats
  • 1 reply
  • 149 views
  • 0 likes
  • 2 in conversation