Convert Char to Datetime20 error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Convert Char to Datetime20 error

Hi,

I have a dataset where LoginDt is created as Char27 format and i need to convert it to Datetime20. format. I tried input statement but it gives error:

PROC SQL;

CREATE table logout01  as

SELECT input(LoginDt,datetime20.) as logindt_cst,    User_Id

FROM test.dataset_20150402

;

quit;

 

NOTE: Invalid month value

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

Any help is really appreciated...

Thanks...


Accepted Solutions
Solution
‎04-06-2015 01:17 PM
Grand Advisor
Posts: 10,258

Re: Convert Char to Datetime20 error

It looks like you may want to use the ymddttm24. informat.

View solution in original post


All Replies
Grand Advisor
Posts: 17,474

Re: Convert Char to Datetime20 error

Those are NOTES not ERRORS.

Are all value's valid, you have no missing LoginDT? 

Can you post the full code/log?

Grand Advisor
Posts: 10,258

Re: Convert Char to Datetime20 error

Since the datetime informat is looking for values like: 16mar2008:11:23:07 if any of the "month" values aren't (case insenstivie) jan, feb, mar, apr, may , jun, jul, aug, sep, oct, nov or dec then you will generate that message and a missing result.

I would modify your code to include the original value and then look for records where the result for the new variable is missing.

Offhand, I might guess you have JUNE vs JUN or JULY vs JUL or possibly some numeric months if you have data entered by different people or using a data source that doesn't validate the entered date portions very well.

Occasional Contributor
Posts: 17

Re: Convert Char to Datetime20 error

Hi, it shows this warning messages and not populating the values in the output. The input dataset has values like '2015-04-02 19:47:30.1050000'

Solution
‎04-06-2015 01:17 PM
Grand Advisor
Posts: 10,258

Re: Convert Char to Datetime20 error

It looks like you may want to use the ymddttm24. informat.

Occasional Contributor
Posts: 17

Re: Convert Char to Datetime20 error

Initially, we had similar values in the input dataset but as Numeric column (input column was defined as Numeric)  and the below code worked - Now the input format is changed to Character and i could not convert it to Datetime20. format.

LoginDt as logindt_cst format datetime20.,

Occasional Contributor
Posts: 17

Re: Convert Char to Datetime20 error

I changed as you suggested, its working... you are awesome. Thank you so much...

SELECT  input(LoginDt,ymddttm24. ) as logindt_cst format datetime20. ,

   User_Id from test.dataset_01;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 247 views
  • 0 likes
  • 3 in conversation