Macro error for indata, if then statement

[grace999]

I would like to group small levels for a list of categorical variable using macro in SAS EG7.15, but it does not work. the same step worked before. I wonder whether others encountered the same issue. Thanks!

The &indata. cannot be invoked by SAS even I give a value. Here is error. if I replace the &indata with the true name. no following errors. 

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

ERROR 22-322: Syntax error, expecting one of the following: GROUP, ORDER.

Error under group_level_&var.

____________________
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

160: LINE and COLUMN cannot be determined.

error under if then in the data step:

ERROR 160-185: No matching IF-THEN clause.

 Here is the code:

%macro grp(indata=, outdata=, threshold=);
proc sql;
select count(*) into:tot from &indata.; quit;

/* Count the number of values in the string */
%let count=%sysfunc(countw(&var_list.));
/* Loop through the total number of values */
%do i = 1 %to &count;
%let var=%qscan(&var_list,&i,%str( ));
%put &var.;

proc sql;
create table %unquote(frq_&VAR.) as
select "&var." as var format=$30., &var. as %unquote(level_&VAR.) format=$30., count(*) as N
, calculated n/&tot. as Percent format=percent7.0
from &indata.
group by 1,2
order by 1,2 desc; quit;

data %unquote(fgrp_&VAR.2); set %unquote(frq_&VAR.); if n<&threshold. then %unquote(grp_level_&VAR.) = 'Other';
else %unquote(grp_level_&VAR.) = %unquote(level_&VAR.);run;
%end;
%mend;
%grp(indata=lib.abc(where=(TVH2 NE ''))), outdata=lib.out, threshold=800);
%grp;

[Tom]

Turn on the MPRINT option.  Copy the text from the SAS log. Make sure to include the statements that the error is complaining about.  

When pasting the lines into your post make sure to use the Insert Code button on the editor.  Otherwise this forum will treat it as paragraphs to be reflowed and we will lose the connection between the error message and the line of code it is complaining about.

[Tom]

It is hard to read the code the way you posted it. But let's take a stab at it.

Your macro is referencing macro variables that are not inputs. 

It appears to be using the value of VAR_LIST. 

It is also changing the values of macro variables TOT, COUNT, I, VAR without defining them as LOCAL.

You are calling the macro twice. Once with values for the inputs, and then again without. 

Is the issue the second call to the macro?

[grace999]

 Here is the code:

%macro grp(indata=, outdata=, threshold=);
proc sql;
select count(*) into:tot from &indata.; quit;

/* Count the number of values in the string */
%let count=%sysfunc(countw(&var_list.));
/* Loop through the total number of values */
%do i = 1 %to &count;
%let var=%qscan(&var_list,&i,%str( ));
%put &var.;

proc sql;
create table %unquote(frq_&VAR.) as
select "&var." as var format=$30., &var. as %unquote(level_&VAR.) format=$30., count(*) as N
, calculated n/&tot. as Percent format=percent7.0
from &indata.
group by 1,2
order by 1,2 desc; quit;

data %unquote(fgrp_&VAR.2); set %unquote(frq_&VAR.); if n<&threshold. then %unquote(grp_level_&VAR.) = 'Other';
else %unquote(grp_level_&VAR.) = %unquote(level_&VAR.);run;
%end;
%mend;
%grp(indata=lib.abc(where=(TVH2 NE ''))), outdata=lib.out, threshold=800);
%grp;

NOTE: Line generated by the invoked macro "GRP".
66 select count(*) into:tot from &indata.;
_
22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

65 ! %unquote(grp_level_&VAR.) = 'Other'; else if n>=&threshold. then %unquote(grp_level_&VAR.) = %unquote(level_&VAR.);run;
____
160
ERROR 160-185: No matching IF-THEN clause.

NOTE: Line generated by the macro function "UNQUOTE".
65 grp_level_Var
____________________
22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, /, ;, <, <=, <>, =, >, ><, >=, AND, EQ, GE,
GT, IN, LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, [, ^=, {, |, ||, ~=.

[Tom · Accepted solution]

You asked SAS macro processor to use this code to generate an SQL SELECT statement where the dataset name comes from a macro variable.

select count(*) into:tot from &indata.;

You then called the macro with the value of INDATA set to **NOTHING**. So it generated this statement.

select count(*) into:tot from ;

So the error is saying SAS needs to see something there after FROM.

To fix this just remove the extra line from the end of your source code:

%grp;

[Astounding]

A couple of issues are helping to hide the problem.  Clear these up first.

%grp(indata=lib.abc(where=(TVH2 NE ''))), outdata=lib.out, threshold=800);

There is an extra right-hand parenthesis.  Change ))) to ))

There is absolutely no reason that I can see to use %qscan.  Use %scan instead, and you can get rid of all the %unquote functions.

These might or might not solve the problems, but it will make the results a little clearer.

[grace999]

Thanks to both Tom and Astounding!