Categorical (DateTime) Format to Date Format

[znhnm]

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!  

[znhnm]

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!

[Stu_SAS]

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.

[Sam_SAS]

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.

[znhnm]

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.

[Sam_SAS]

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.

[znhnm]

Here it is: 

[Sam_SAS]

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'

[znhnm]

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?`

[Sam_SAS]

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'

[znhnm]

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!!

[Sam_SAS]

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