BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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).
DanielSantos
Barite | Level 11
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2559 views
  • 0 likes
  • 3 in conversation