topic Re: Invalid argument to function QUOTE in SAS Programming

Invalid argument to function QUOTE

deleted_user — Wed, 16 Sep 2009 18:40:05 GMT

I'm currently getting this error:
NOTE: Invalid argument to function QUOTE. Missing values may be generated.
NOTE: PROCEDURE SQL used (Total process time):
NOTE: Line generated by the macro variable "EXLCL".
64 ,,
_
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
a missing value, -.
ERROR 76-322: Syntax error, statement will be ignored.

when I run this code:
proc summary data=test NWAY completetypes;
class job_class inj_yr;
var avg_emp;
output out=out1 (where=(inj_yr='2008' and avg_emp in(.,0))) sum= ;
run;
proc sql noprint;
select quote(job_class)
into: exlcl separated by ','
from out1;
quit;
%put &exlcl;

proc report data=test nowd
style(header)=[font_weight=bold];
options missing='0';
where job_class not in (&exlcl);
columns job_class year, (avg_emp);

The variable job_class is a categorical variable. Some of its categories are: Plant Equipment, Engineer, and Clerical Worker.

The file out1 appears to be generated correctly.
Any ideas?

Thanks,
Jason

Re: Invalid argument to function QUOTE

sbb — Wed, 16 Sep 2009 18:52:07 GMT

Do you have an observation where the JOB_CLASS variable is blank? Also, you may want to consider adding DISTINCT to the SELECT in PROC SQL, so that you only get one of each JOB_CLASS value generated in your macro variable. And, there is a max length limit for a SAS macro variable - just in case you might exceed that with your PROC SQL, by the way.

Scott Barry
SBBWorks, Inc.

Re: Invalid argument to function QUOTE

deleted_user — Wed, 16 Sep 2009 20:19:14 GMT

None of the observations in job_class are blank.
I tried adding DISTINCT and I received the same error:
NOTE: Invalid argument to function QUOTE. Missing values may be generated.
where job_class not in (&exlcl);
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value.

I also did some testing and found that I still get the same error when I use jobs with 1 word titles (eg Engineer).

Re: Invalid argument to function QUOTE

DanielSantos — Thu, 17 Sep 2009 11:30:24 GMT

Very strange.

You may expect this kind of error message when you are trying to hold a quoted expression in a variable which is too small in length. Say you have variable B="AAA" with a $3 length, B=quote('AAA') would produce that error, because B cannot hold ' "AAA" '
The SQL expression is completely different and should dynamically store the result of the quote function. But, anyway try this mod to see if it works.

proc sql noprint;
select quote(job_class) length=50
into: exlcl separated by ','
from out1;
quit;

Adjust the length to a large enough size for the the job_class variable.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Invalid argument to function QUOTE

sbb — Thu, 17 Sep 2009 12:40:59 GMT

The DISTINCT suggestion is unrelated to your error - it was a suggestion to avoid extraneous duplicates being generated in your macro variable. I suggest diagnosing the problem by removing the QUOTE function just to see what gets generated as shown in the %PUT statement that follows. Then maybe you can determine what SAS is complaining about, based on your data. These are suggestions for you to help diagnose the warning/error.

Here's another coding technique to consider - maybe you will not get the error/warning:

proc sql noprint;
select '"' !! trim(job_class) !! '"'
into: exlcl separated by ','
from out1;
quit;
%put &exlcl;

Scott Barry
SBBWorks, Inc.