VA format is not working with parsing a datetime

[mipate]

I have a date time in a CSV, and VA recognizes it as a character when I upload the csv. Okay, so then I'm trying to backtrace as to what im doing wrong since even when I attempt to format/parse it into a datetime, it just shows up as an empty dot for each row of the newly created calculated column.

So then I go back to the simplest case and use the parse function to parse the example date that VA gives:

Parse('June 25, 2014 08:15:00 AM', 'DATETIME40.')

But this fails and it still shows a dot in every row for this calculated column! How can this possibly be the case that I am using the example that VA gives and it is unable to parse it?

[mipate · Accepted solution]

UPDATE:

I have found a solution to the issue.

I figure that I will not be the last one to run into the same difficulties, so here is where I went wrong. I was opening my csv files in excel prior to uploading and the column that had the date in it seemed to be formatted fine (which is technically was). However, if I used a text edit to manually inspect the csv itself, I found that each date entry was enclosed in quotations.

I didn't initially think this was a problem since VA allows for csv uploads and the example datetime format had a comma in it (which implies that the entire datetime would need to be enclosed in quotations to avoid the comma being treated as the next cell). However, it turns out that this was exactly the problem. So my solution was to change the format to MM/DD/YYYY HH:MM:SS AM on my raw data and then upload that to VA.

However, that doesn't quite mark the end of the solution; Visual Analytics still read my datetime as a string. However, making a new calculated column and casting the string to a date time worked as intended this time around.

If anyone ever runs into a similar issue, let me know and I can try help! (Would also recommend talking to support, they were the incredible ones who knew this was the issue!)

[nurShahirah]

Hi Mipate, 

I encounter almost the same problem with you when I try to parse my datetime data. 

I uploaded my data from a csv file and convert the format of the date column in SAS Viya into DDMMYY. format. But when I tried to parse the converted date column, the parse month and year did not gave me the value I was expecting.

Eg: original date is 20/8/2020, but the parse month is 1 and parse year is 47.

Can you advise on this?

Thanks.

Nur.

[PetriRoine]

Hello @mipate 

Please create a New calculated item and utilize Parse operator with ANYDTDTE. Here's a code snippet which you can copy-paste and see how it behaves:

Parse('20/8/2020', 'ANYDTDTE9.')

Best regards,

Petri