filter on character date in proc sql

xliu1 — Sat, 20 Jun 2020 20:28:37 GMT

I need to add a filter using a date variable which is in character format (e.g., 2017-07-01 00:00:00). Here is my sas code. I keep getting error message in sas log: ERROR: CLI cursor fetch error: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string. I know there is something wrong with the date format in where clause but not sure how to modify it. Anyone can help me with this? Thanks.

proc sql;

create table EMPLOYEE as

select A.EMPLID,
       A.USF_ID,
	   A.LAST_NAME,
	   A.FIRST_NAME,
       A.SEX_DESC,
       A.ETHNIC_GROUP_DESC,
	   A.JOB_EFFDT,
	   A.HIRE_DATE,
       A.REHIRE_DATE,
	   A.SERVICE_DATE,
	  A.TERMINATION_DATE,
	   A.POSITION_NBR,
	   A.POSITION_EFFDT,
	   A.POSITION_DESC,
	   A.POSITION_ACTION_DESC,
	   A.POSITION_ACTION_DATE,
	   A.DEPTID,
	   A.DEPTID_DESC,
	   A.VP_AREA_DESC,
	   P.PAY_END_DT as PayPeriodEndDate 
 
FROM Dwhouse.Gems_ps_pay_check_d3 P
LEFT JOIN Dwhouse.Gems_appointment_d3 A on P.EMPLID = A.EMPLID
where A.LOCATION_CODE = '04'
     and P.PAY_END_DT >= '2019-07-01'

order by EMPLID, SERVICE_DATE;

quit;

Re: filter on character date in proc sql

novinosrin — Sat, 20 Jun 2020 20:44:23 GMT

Can you try--

If numeric

 P.PAY_END_DT >= '01jul2019'd

If char

 input(P.PAY_END_DT,yymmdd10.) >= '01jul2019'd

Re: filter on character date in proc sql

xliu1 — Sat, 20 Jun 2020 21:23:26 GMT

Thanks! This works out as I expected.

topic Re: filter on character date in proc sql in SAS Procedures

filter on character date in proc sql

Re: filter on character date in proc sql

Re: filter on character date in proc sql