Help using Base SAS procedures

Macro in Where within Tabulate?

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Macro in Where within Tabulate?

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;


Accepted Solutions
Solution
‎04-14-2015 03:08 PM
PROC Star
Posts: 1,230

Re: Macro in Where within Tabulate?

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" ;

View solution in original post


All Replies
Super User
Posts: 17,774

Re: Macro in Where within Tabulate?

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

PROC Star
Posts: 1,230

Re: Macro in Where within Tabulate?

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.

Contributor
Posts: 24

Re: Macro in Where within Tabulate?

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.

Solution
‎04-14-2015 03:08 PM
PROC Star
Posts: 1,230

Re: Macro in Where within Tabulate?

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" ;

Contributor
Posts: 24

Re: Macro in Where within Tabulate?

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!

Contributor
Posts: 24

Re: Macro in Where within Tabulate?

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 273 views
  • 0 likes
  • 3 in conversation