topic Re: convert character to date in SAS Programming

convert character to date

pacman94 — Fri, 19 Mar 2021 17:00:20 GMT

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.

Re: convert character to date

PaigeMiller — Fri, 19 Mar 2021 16:34:20 GMT

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).

Re: convert character to date

pacman94 — Fri, 19 Mar 2021 16:45:24 GMT

I edited the text

</>

Invalid date value

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

Re: convert character to date

Reeza — Fri, 19 Mar 2021 16:55:43 GMT

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.

Re: convert character to date

pacman94 — Fri, 19 Mar 2021 17:07:30 GMT

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

Re: convert character to date

Reeza — Fri, 19 Mar 2021 17:13:24 GMT

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

Re: convert character to date

PaigeMiller — Sat, 20 Mar 2021 12:20:31 GMT

@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.

Re: convert character to date

Tom — Sat, 20 Mar 2021 15:09:54 GMT

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.)