convert character to date

[pacman94]

I have the following dataset where both 'Date' and 'Value' are in character format. I would like to convert Date from character to date field and Value from character to numeric using proc sql; command. I am unable to convert the Date

ID	Date	Value
1	2020-01-01	1
2	2020-04-01	2
3	2020-05-09	1
4	2020-09-04	1

proc sql;

create table test as

select ID, input(Date, mmddyy10.) as DateNew format=mmddyy10.,

input(Value, best.) as Value

from have;

quit;

the Value is being converted properly but not Date. It shows an error that invalid or missing/error

</>

Invalid date value

NOTE: Invalid argument to function INPUT. Missing values may be generated.

[PaigeMiller]

When you have code that is causing an error, SHOW US THE LOG. The entire log for PROC SQL, every single word, in the sequence that it appears, with nothing chopped out. Please format the log properly by clicking on the </> icon and pasting the log as text into the window that appears; this makes the log more readable (and I no longer try to read logs that aren't formatted this way).

[pacman94]

I edited the text

</>

Invalid date value

NOTE: Invalid argument to function INPUT. Missing values may be generated.

[PaigeMiller]

---

@pacman94 wrote:

I edited the text

</>

Invalid date value

NOTE: Invalid argument to function INPUT. Missing values may be generated.

---

This is not the ENTIRE log for PROC SQL that I asked for, and it needs to be formatted properly, as I described. What you have provided is part of the log, detached from the rest of the log and detached from the code that created the error, is not useful.

[Reeza]

Show more of the log as it will contain the rest of the information.
Is it possible the dates are ddmmyy instead?
Or do you have missing dates or values? This code doesn't handle that case and that would generate the error you're seeing as well.

Your code is correct so this is a data specific issue.

[pacman94]

I see . the original dates are in YYYY-MM-DD format. I tried different combinations . There are no missing dates

[Reeza]

If the original dates are YYMMDD you need to use a YYMMDD informat, not a MMDDYY informat in your INPUT function.

[Tom · Accepted solution]

Your strings are not in  Month Day Year order like you told the INPUT() function they were. 

Your strings are in Year Month Day order.

 input(Date, yymmdd10.)