Invalid argument to function QUOTE
in [SAS]
|
Prev: Summay Data by Proc Sql --- Easy Question. I think my braind
Next: Agresti-Coull confidence intervals
From: Arthur Tabachneck on 16 Sep 2009 14:45 Jason, Your sql code looks ok, but your proc summary doesn't. Specifically, you have a variable in your where clause that doesn't exist in the file. Did you mean to use inj_yr rather than year? Art ------- On Wed, 16 Sep 2009 11:29:47 -0700, J M <jasonm(a)UCLA.EDU> wrote: >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=(year='2008' and avg_emp in(.,0))) sum= ; >run; >proc sql noprint; > select quote(job_class) > into: exl separated by ',' > from out1; >quit; >%put &exl; > >proc report data=test nowd > style(header)=[font_weight=bold]; > options missing='0'; > where job_class not in (&exl); > 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
From: J M on 16 Sep 2009 15:00 Sorry, you're right. That should be year, not inj_yr. It's written correclty in my code. Somehow that got transferred over to my post incorrectly (ie aside from changing inj_yr to year my question is still the same). I've tested code similar to this when job_class=a,b,c. So, the one theory I have at the moment is that there is a problem with using job_class=Plant Equipment or job_class=Clerical Worker. But I could be wrong. Thank you. Quoting Arthur Tabachneck <art297(a)NETSCAPE.NET>: > Jason, > > Your sql code looks ok, but your proc summary doesn't. Specifically, you > have a variable in your where clause that doesn't exist in the file. Did > you mean to use inj_yr rather than year? > > Art > ------- > On Wed, 16 Sep 2009 11:29:47 -0700, J M <jasonm(a)UCLA.EDU> wrote: > >> 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=(year='2008' and avg_emp in(.,0))) sum= ; >> run; >> proc sql noprint; >> select quote(job_class) >> into: exl separated by ',' >> from out1; >> quit; >> %put &exl; >> >> proc report data=test nowd >> style(header)=[font_weight=bold]; >> options missing='0'; >> where job_class not in (&exl); >> 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 >
From: Gerhard Hellriegel on 16 Sep 2009 15:10 if year is a variable in dataset TEST, you cannot use it as ds-option in OUT1. In this case, if you want to subset by year, you could use a simple global WHERE clause: .....summary....; var... class... out... WHERE year="2008"; .... or you use it as option in the input dataset TEST(WHERE=...) which is the same. The variable is known in the PDV (from input dataset), but not in the output dataset. Gerhard On Wed, 16 Sep 2009 14:45:53 -0400, Arthur Tabachneck <art297(a)NETSCAPE.NET> wrote: >Jason, > >Your sql code looks ok, but your proc summary doesn't. Specifically, you >have a variable in your where clause that doesn't exist in the file. Did >you mean to use inj_yr rather than year? > >Art >------- >On Wed, 16 Sep 2009 11:29:47 -0700, J M <jasonm(a)UCLA.EDU> wrote: > >>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=(year='2008' and avg_emp in(.,0))) sum= ; >>run; >>proc sql noprint; >> select quote(job_class) >> into: exl separated by ',' >> from out1; >>quit; >>%put &exl; >> >>proc report data=test nowd >> style(header)=[font_weight=bold]; >> options missing='0'; >> where job_class not in (&exl); >> 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
From: Gerhard Hellriegel on 16 Sep 2009 15:18 somehow it's not clear to me: the error message talks about a macro variable "EXLCL". I can't find that macro variable in your code. The name of the macro variable in your code is exl: where job_class not in (&exl); What is that? Gerhard On Wed, 16 Sep 2009 12:00:21 -0700, J M <jasonm(a)UCLA.EDU> wrote: >Sorry, you're right. >That should be year, not inj_yr. >It's written correclty in my code. Somehow that got transferred over >to my post incorrectly (ie aside from changing inj_yr to year my >question is still the same). >I've tested code similar to this when job_class=a,b,c. >So, the one theory I have at the moment is that there is a problem >with using job_class=Plant Equipment or job_class=Clerical Worker. >But I could be wrong. >Thank you. > > >Quoting Arthur Tabachneck <art297(a)NETSCAPE.NET>: > >> Jason, >> >> Your sql code looks ok, but your proc summary doesn't. Specifically, you >> have a variable in your where clause that doesn't exist in the file. Did >> you mean to use inj_yr rather than year? >> >> Art >> ------- >> On Wed, 16 Sep 2009 11:29:47 -0700, J M <jasonm(a)UCLA.EDU> wrote: >> >>> 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=(year='2008' and avg_emp in(.,0))) sum= ; >>> run; >>> proc sql noprint; >>> select quote(job_class) >>> into: exl separated by ',' >>> from out1; >>> quit; >>> %put &exl; >>> >>> proc report data=test nowd >>> style(header)=[font_weight=bold]; >>> options missing='0'; >>> where job_class not in (&exl); >>> 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 >>
From: Kevin Viel on 17 Sep 2009 09:35
On Wed, 16 Sep 2009 11:29:47 -0700, J M <jasonm(a)UCLA.EDU> wrote: >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=(year='2008' and avg_emp in(.,0))) sum= ; >run; >proc sql noprint; > select quote(job_class) > into: exl separated by ',' > from out1; >quit; >%put &exl; > >proc report data=test nowd > style(header)=[font_weight=bold]; > options missing='0'; > where job_class not in (&exl); > 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? Why not populated EXL using TEST instead of OUT1? proc sql noprint; select DISTINCT quote( STRIP( job_class )) into: exl separated by ',' from TEST WHERE year='2008' AND avg_emp in (.,0) /* and ~Missing( job_class ) and ~Missing( inj_yr ) */ ; quit; One might wonder why you are interested in avg_emp in (.,0) and using the MEANS/SUMMARY procedure and not, say, FREQ? Potentially, you meant "not in"? Also, is year truly a character variable? A few data management issue seem to exist, which is another great advantage of posting to this list-suggestions that you might not have anticipated.... HTH, Kevin |