topic Re: "WHERE clause could not be parsed as written" error message in SAS Programming

"WHERE clause could not be parsed as written" error message

jblack38 — Tue, 25 Feb 2020 18:15:26 GMT

I am running EG v7.15 HF8 in Windows environment with SAS 9.4.

When I run this code:

libname STULASR  sasiola SIGNER='http://sas01mdc.admin.ad.cnm.edu:80/SASLASRAuthorization' HOST='sasanalytics.admin.ad.cnm.edu' PORT=10017;

proc sql;

SELECT		SFRRSTS_TERM_CODE

FROM 		STULASR.SFRRSTS 

WHERE 		SFRRSTS_RSTS_CODE = 'RW' 
  AND 		datepart(SFRRSTS_START_DATE) <= input("&SYSDATE", date7.)
;
quit;

I get the error

ERROR: The WHERE clause '((SFRRSTS_RSTS_CODE='RW') and (DATEPART(SFRRSTS_START_DATE)<=21970))' could not be parsed as written.

Thanks for your help!

Jerry Black

Re: "WHERE clause could not be parsed as written" error message

PGStats — Tue, 25 Feb 2020 18:47:23 GMT

I think that error is covered in this note :

http://support.sas.com/kb/54/319.html

Re: "WHERE clause could not be parsed as written" error message

jblack38 — Tue, 25 Feb 2020 18:47:00 GMT

I should mention that I only get this error when I reference the table from the LASR library. When I reference it from our Oracle library or from the .sas7bdat file, I don't get the error.

Re: "WHERE clause could not be parsed as written" error message

PGStats — Tue, 25 Feb 2020 19:39:07 GMT

Try this instead

int(SFRRSTS_START_DATE/'24:00:00't) <= input("&SYSDATE", date7.)

Re: "WHERE clause could not be parsed as written" error message

jblack38 — Tue, 25 Feb 2020 20:36:36 GMT

I get the same error when I change it to this code

Re: "WHERE clause could not be parsed as written" error message

jblack38 — Tue, 25 Feb 2020 21:01:13 GMT

The only way I can get the comparison to work is to include the calculation of the datepart as one of result columns. In my case this doesn't give me what I want because I'm trying to get a distinct list of SFRRSTS_Term_Code's. But I can get what I want by encapsulating this as a subquery.

proc sql;

select 		distinct SFRRSTS_TERM_CODE
from
			(SELECT		SFRRSTS_TERM_CODE,
						datepart(SFRRSTS_START_DATE) as date_only
			FROM 		stulasr.SFRRSTS 

			WHERE 		SFRRSTS_RSTS_CODE = 'RW' 
				and	 (calculated date_only ) <= input("&SYSDATE", date7.)
			)
;
quit;

I was hoping it was just something I was doing wrong and there would be a cleaner solution.

Thanks for your suggestions!

Jerry