ERROR: INPUT function reported 'ERROR: Invalid date value' while processing WHERE clause.

[Rixile106]

Good day expect.

am running the below script and and am getting the this error message

Spoiler

ERROR: INPUT function reported 'ERROR: Invalid date value' while processing WHERE clause.

PROC SQL;
	CREATE TABLE INSTA_A AS
		SELECT INPUT(PUT(COMPRESS(INPUT(PUT(ACC_NO,13.),$13.)||PUT(SERIAL_NO,Z3.)),$16.),16.) AS ACCOUNT
			,A_COMM_DT AS COMM_DT
			,A_EXP_DT AS EXP_DT
			,'A' AS INSTRUCTION
		FROM VBM.INST_A
		WHERE INPUT(PUT(A_COMM_DT,Z8.),YYMMDD8.) > TODAY() + 365
		;
QUIT;

[AMSAS]

Without knowing the A_COMM_DT values, it's hard to tell what is causing the error.

Still my guess is you have an A_COMM_DT value that doesn't fit the YYMMDD8. format e.g. 20210231 (no 31st of Feb)

The following will replicate your error

data have ;
	infile cards ;
	input a_comm_dt ;
cards ;
20230315
20230415
20230515
20230229
;

proc sql ;
	select 
		a_comm_dt as comm_dt
	from
		have
	where 
		input(put(a_comm_dt,z8.),yymmdd8.) > today() + 365 
	;
quit ;

[Rixile106]

here are the values of A_COMM_DT

20220228
20210911

[AMSAS]

Are you sure? You only have 2 observations and those are the 2 values.
If you take those and drop them in the sample code (removing my bad 20230229 date) I supplied you'll see they work (at least they do for me).

[Rixile106]

i have more than 200 observations, i was just showing you an example

20220228
20210911
20210911
20080901
20080128
20170925
20140830
20120728
20020528
20160902
20100413
20110209
20001017
20210101
20210625
20210801
20141028
20040720
20170729
20201201
20060601
20110127
20020829
20110831
20050103
19820809
20201120
20071027
20200229
20191225
20190120
20060627
20041228
20170227
20121030

[Rixile106]

Yes it does replicate my error

[AMSAS]

Provide code and data to replicate the error, like I did.
Then we will be better able to help.

[AMSAS]

@Rixile106 You need to look at the values you have and check for invalid dates.
The error message is clear, it's telling you a date value in your data is not valid e.g. 20211301, 20210229, etc.

Could it be something else? Yes, but that is very unlikely.

I'd start with the obvious, go look at the data, specifically

• Any rows where the day is 29|30|31 
• Any months that are not 01-12 (the 0 needs to be there on the single digits)

[Tom · Accepted solution]

You can make the error MESSAGE go away by adding the ? modifier before the informat.

input(put(a_comm_dt,z8.),?yymmdd8.) > today() + 365

But those records with invalid A_COMM_DT values will be excluded by your WHERE clause because missing values are less than any valid value.

You should look at the observations with the invalid dates and decide what to do with them.

data check;
  set  VBM.INST_A;
  if missing(INPUT(PUT(A_COMM_DT,Z8.),??YYMMDD8.)) and not missing(A_COMM_DT) ;
run;