BookmarkSubscribeRSS Feed
znhnm
Quartz | Level 8

Hi,

 

I have a column on my dataset that has date values, but the variable is Catregorical. I am looking for a way to get the month values. For example, the data I have is Dec 08 2022 (categorical). I would like to get the 08, using VA interface. I'd use Calculated Item, and use Text operators but then they require numerical inputs. So, this idea didn't work. 

Can I achieve what I am trying to do in VA? If yes, how?

 

Thank you! 

10 REPLIES 10
Sam_SAS
SAS Employee

Hello,

 

This is a common issue, especially if you are importing CSV/TSV data. The Parse() operator is what you need.

 

https://documentation.sas.com/doc/en/vacdc/v_018/vareportdata/p0dovqqp325peyn1dpijpxxmwj83.htm#p1o97...

 

Parse

interprets a numeric or datetime value from the input string. Click the format field to select the format that is used to interpret the string. The output from the Parse operator is either a number or a datetime value, depending on the format that you select.

 

For example, Parse('15JAN2013', 'DATE9.') returns 15JAN2013 as a date value.

 

Let us know if that helps,

Sam

 

znhnm
Quartz | Level 8

Hi Sam,

 

Thanks for the suggestion. Actually, I tried it but then all I had in the calculated item was missings. Here is how I created the Calculated Item using Parse: 

znhnm_1-1680094901661.png

 

This is the result on the left hand side column (all missing) created based on the right hand side column. 

znhnm_0-1680094871319.png

 

Sam_SAS
SAS Employee

Hello,

 

It looks like your date values have spaces in them: 01 JAN 2023

 

You might try different formats to see if there is one that will parse this.

 

You can also try wrapping the date value in the RemoveBlanks() operator.

 

That would look something like this:

Parse(RemoveBlanks('Date'n, _All_), 'Date9.')

znhnm
Quartz | Level 8

Hi Sam, 

 

Thank you again! I understand the logic you suggested. I applied the Remove Blanks. Then I applied Parse but still all I have is missings.Do you have any idea why this could be happenning? 

 

znhnm_0-1680097192899.png

 

 

Result: 

 

znhnm_1-1680097210211.png

 

Tom
Super User Tom
Super User

Those strings are not in a style that the DATE informat will understand.   It wants the day of the month first.

 

Try using the anydtdte11. informat instead.

Sam_SAS
SAS Employee

@Tom wrote:

Those strings are not in a style that the DATE informat will understand.   It wants the day of the month first.

 

Try using the anydtdte11. informat instead.


Yes, this should work and should not require RemoveBlanks.

znhnm
Quartz | Level 8

Hi Sam, 

 

I am sorry, but I still do get the missings. Please let me know if you can see any reasons why his is happenning. 

znhnm_0-1680099353659.png

 

 

Result:

 

znhnm_1-1680099387774.png

 

 

 

Sam_SAS
SAS Employee
It looks like you are applying anydtdte11 format to "BlanksRemoved", is that the value that has had RemoveBlanks() applied to it?

Try parsing the original date.

Also, just for a sanity check, please try this:

Parse('JAN 23 2023', 'ANYDTDTE11.')

That is, just parse this one value and see if it returns 23JAN2023 as we would expect.
znhnm
Quartz | Level 8

Hi Sam,

 

Yes, I'm applying anydtdte11 format to "BlanksRemoved", which is the value that has had RemoveBlanks() applied to it....

 

Then I tried parsing the original date (with blanks) with 'ANYDTDTE11.', and it worked. Thank you so much!! 

Sam_SAS
SAS Employee

Great! Thanks for letting us know 🙂

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 1979 views
  • 3 likes
  • 3 in conversation