DATA Step, Macro, Functions and more

Invalid argument to function QUOTE

Reply
N/A
Posts: 0

Invalid argument to function QUOTE

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Invalid argument to function QUOTE

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.
N/A
Posts: 0

Re: Invalid argument to function QUOTE

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).
Super Contributor
Posts: 474

Re: Invalid argument to function QUOTE

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Invalid argument to function QUOTE

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.
Ask a Question
Discussion stats
  • 4 replies
  • 528 views
  • 0 likes
  • 3 in conversation