topic Re: Categorical (DateTime) Format to Date Format in SAS Visual Analytics

Categorical (DateTime) Format to Date Format

znhnm — Thu, 18 May 2023 06:43:46 GMT

Hi,

I have a categorical variable that looks like this.

I would like to convert it to the date format so that I will create another variable to take the month and year part, looks like this:

I tried the parsing:

But I get missing values. What can I do in VA to solve this?

Thanks!

Categorical DateTime to Date

znhnm — Thu, 18 May 2023 07:04:36 GMT

Hi,

I had a cave a categorical variable that has date and time values, how can I remove the time part and have the date part only?

e.g. From this:

To this:

Thank you!

Re: Categorical (DateTime) Format to Date Format

Stu_SAS — Thu, 18 May 2023 13:00:01 GMT

Hey @znhnm! The day of the week is what's causing your issue. anydtdte cannot parse that particular pattern, but it will parse the string if you remove the day of the week. You can fix this by removing the first word:

parse(removeword('CatDateTime'n, 1), 'anydtdte32.')

Note that anydtdte returns a date while anydtdtm returns a datetime. If you return a datetime, you can still format it as a month and retain the original datetime data by duplicating the column.

Re: Categorical (DateTime) Format to Date Format

Sam_SAS — Thu, 18 May 2023 13:00:53 GMT

Another option would be to use the NLDATMWw informat as part of your data preparation to read in the values as SAS datetime values. After that you can apply formats in VA.

Re: Categorical (DateTime) Format to Date Format

znhnm — Thu, 18 May 2023 13:30:47 GMT

Thank you so much for the reply. I tried the RemoveWord but I get missing values so apllying Parsing afterwards is not giving the result I wanted.

Re: Categorical (DateTime) Format to Date Format

Sam_SAS — Thu, 18 May 2023 14:12:46 GMT

Hello,

Feeding RemoveWord() into Parse() should work. Can you show us the entire expression editor dialog? I am interested in the Result Type and Format fields at the top.

Re: Categorical (DateTime) Format to Date Format

znhnm — Thu, 18 May 2023 14:19:52 GMT

Here it is:

Re: Categorical (DateTime) Format to Date Format

Sam_SAS — Thu, 18 May 2023 14:31:58 GMT

And that expression is giving you a missing value for every row? It looks like it should work. But what if you change the Result Type to DateTime? Even though your desired result is a date, you might need to make a DateTime value and the apply a Date format to it.

If that doesn't help, what happens if you substitute a single value for the CatDateTime variable?

Like so:

'Wednesday, 29 September 2022 13:44:00'

Re: Categorical (DateTime) Format to Date Format

znhnm — Thu, 18 May 2023 14:45:44 GMT

Hi, again thanks but still I have missing values for all the rows, when changing the result type. However, what do you mean by `what happens if you substitute a single value for the CatDateTime variable?`

Re: Categorical (DateTime) Format to Date Format

Sam_SAS — Thu, 18 May 2023 15:01:04 GMT

Click the button at the top right to view the expression as text code, then replace the reference to CatDateTime with just the character value 'Wednesday, 29 September 2022 13:44:00'

Re: Categorical (DateTime) Format to Date Format

znhnm — Thu, 18 May 2023 15:17:56 GMT

Hi, thank you so much for the help. There is an unexpected thing happened. I just refreshed the SAS session and the categorical turned to be a Date, Time format automatically. So this is now in the format that I wanted it to be but I don't have an explanation why it was not in this format in the first place. Thank you for the support!!

Re: Categorical (DateTime) Format to Date Format

Sam_SAS — Thu, 18 May 2023 15:39:47 GMT

Thanks so much for letting us know, glad to hear this is working like you want it to now.

I have had odd things happen in my report state sometimes. If a calculated item is used in anything (calculations, report objects, etc) generally you can't change the data type or format, but I'm not sure if that could have been the case here.

Thanks,
Sam