Macro in Where within Tabulate?

[laura6728]

I have the code below that I am trying to run, but keep getting errors on the where clause. The error is "Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT,  LE, LT, NE, NOT, OR, ^, ^=, |, ||, ~, ~="  Is there something else I can do to get this to work? I'm new to SAS so I'm open to suggestions if this is possible.

%let DEAN1A = 1; /* Dean of Arts & Sciences Code (1) */

PROC TABULATE

DATA=GRDDIST.&STERM.vpi_secgcat;

    BY DIV;

    WHERE DEAN = &DEAN1A.;

    VAR CAMPUS;

       CLASS grdcat DPT COURSE name_1 SEC3 /    ORDER=UNFORMATTED MISSING;

    CLASSLEV grdcat;

    CLASSLEV DPT / STYLE=[OUTPUTWIDTH=1in];

    CLASSLEV COURSE / STYLE=[OUTPUTWIDTH=.5in];

    CLASSLEV name_1 / STYLE=[OUTPUTWIDTH=2in];

    CLASSLEV SEC3 / STYLE=[OUTPUTWIDTH=.5in];

    TABLE

                 /* ROW Statement */

         DPT={LABEL=""}*COURSE={LABEL=""}*name_1={LABEL=""}*SEC3={LABEL=""},

        /* COLUMN Statement */

        grdcat={LABEL=""} *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

         * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)

        all = 'Total'  *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

         * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)    ;

    ;

RUN; QUIT;

[Reeza]

What line is generating the error? Can you post the full log?

[Quentin]

Yes, please post the full log.

If you are getting that error from the WHERE statement, my guess is you forgot to run the %LET statement  (you should get warning about macro variable DEAN1A not existing), or for some reason the macro variable has a weird value.

Suggest add

  %put DEAN1A = >>&DEAN1A<< ;

Immediately before your tabulate step, and see if the value is what you expect.

[laura6728]

Adding   %put DEAN1A = >>&DEAN1A<< ; before my tabulate didn't work. I thought it had, but now i reran it to show a coworker and it populated the same error.

[Quentin · Accepted solution]

The %put DEAN1A suggestion was just to show the value of &DEAN1A, which you've accomplished with turning on SYMBOLGEN, so no need for that now.

The error about where clause requiring compatible variables means what it says (in SAS-speak : )

Your code will become:

  where DEAN = 1;

That would work if DEAN is a numeric variable, but not if DEAN is a character variable.  If DEAN is a character variable, then you could use:

  where DEAN = "1";

So you could change your %LET statement to

  %let DEAN1A = "1";

Or leave your %let statement as is and instead change your WHERE statement to

  where DEAN = "&DEAN1A" ;

[laura6728]

Ugh such a rookie mistake!! DEAN is character not numeric, so I needed the ""!  I also didn't have the "" in the where statement.  I've ran it 4 times now and it is working like a champ.  Thank you so much!

[laura6728]

I made a couple changes and now get the following error log:

7959  PROC TABULATE

7960  DATA=GRDDIST.&STERM.vpi_secgcat;

SYMBOLGEN:  Macro variable STERM resolves to FA14

7961      BY DIV;

7962      WHERE DEAN = &DEAN1A.;

SYMBOLGEN:  Macro variable DEAN1A resolves to 1

ERROR: WHERE clause operator requires compatible variables.

7963      VAR CAMPUS;

7964      CLASS grdcat DPT COURSE name_1 SEC3 /    ORDER=UNFORMATTED MISSING;

7965      CLASSLEV grdcat;

7966      CLASSLEV DPT / STYLE=[OUTPUTWIDTH=1in];

7967      CLASSLEV COURSE / STYLE=[OUTPUTWIDTH=.5in];

7968      CLASSLEV name_1 / STYLE=[OUTPUTWIDTH=2in];

7969      CLASSLEV SEC3 / STYLE=[OUTPUTWIDTH=.5in];

7970

7971      TABLE

7972        /* ROW Statement */

7973           DPT={LABEL=""}*COURSE={LABEL=""}*name_1={LABEL=""}*SEC3={LABEL=""},

7974          /* COLUMN Statement */

7975          grdcat={LABEL=""} *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

7976           * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)

7977          all = 'Total'  *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

7978           * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)    ;

7979      ;

7980  RUN;

CODE:

%let DEAN1A = 1; /* Dean of Arts & Sciences Code (1) */

PROC TABULATE

DATA=GRDDIST.&STERM.vpi_secgcat;

    BY DIV;

    WHERE DEAN = &DEAN1A.;

    VAR CAMPUS;

    CLASS grdcat DPT COURSE name_1 SEC3 /    ORDER=UNFORMATTED MISSING;

    CLASSLEV grdcat;

    CLASSLEV DPT / STYLE=[OUTPUTWIDTH=1in];

    CLASSLEV COURSE / STYLE=[OUTPUTWIDTH=.5in];

    CLASSLEV name_1 / STYLE=[OUTPUTWIDTH=2in];

    CLASSLEV SEC3 / STYLE=[OUTPUTWIDTH=.5in];

    TABLE

      /* ROW Statement */

         DPT={LABEL=""}*COURSE={LABEL=""}*name_1={LABEL=""}*SEC3={LABEL=""},

        /* COLUMN Statement */

        grdcat={LABEL=""} *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

         * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)

        all = 'Total'  *(CAMPUS={LABEL=""} * N={LABEL="#" S=[CELLWIDTH=.5in]} CAMPUS={LABEL=""}

         * RowPctN={LABEL="%" S=[CELLWIDTH=.5in]}*f=pctfmt.)    ;

    ;

RUN; QUIT;