topic Re: Macro in Where within Tabulate? in SAS Procedures

Macro in Where within Tabulate?

laura6728 — Tue, 14 Apr 2015 18:34:16 GMT

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;

Re: Macro in Where within Tabulate?

Reeza — Tue, 14 Apr 2015 18:46:49 GMT

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

Re: Macro in Where within Tabulate?

Quentin — Tue, 14 Apr 2015 18:54:44 GMT

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.

Re: Macro in Where within Tabulate?

laura6728 — Tue, 14 Apr 2015 18:55:25 GMT

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;

Re: Macro in Where within Tabulate?

laura6728 — Tue, 14 Apr 2015 18:57:04 GMT

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.

Re: Macro in Where within Tabulate?

Quentin — Tue, 14 Apr 2015 19:08:06 GMT

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

Re: Macro in Where within Tabulate?

laura6728 — Tue, 14 Apr 2015 19:11:46 GMT

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!