Help using Base SAS procedures

Macro in Where within Tabulate?

Accepted Solution Solved
Reply
Contributor
Posts: 27
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,322

Re: Macro in Where within Tabulate?

Posted in reply to laura6728

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: 19,770

Re: Macro in Where within Tabulate?

Posted in reply to laura6728

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

PROC Star
Posts: 1,322

Re: Macro in Where within Tabulate?

Posted in reply to laura6728

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: 27

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,322

Re: Macro in Where within Tabulate?

Posted in reply to laura6728

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: 27

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: 27

Re: Macro in Where within Tabulate?

Posted in reply to 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;

🔒 This topic is solved and locked.

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

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