BookmarkSubscribeRSS Feed
SilinBorro
Quartz | Level 8

Hi, I've got a date field in my table and I need to get the most recent date in it.

I'm working in SAS VA 7.1 and tried creating an item field using the MAX function but gives me error.

any idea how can I get the max date ?

thanks.

Silvia

9 REPLIES 9
ballardw
Super User

Show use what you did and the error.

Also, make sure your dates are SAS date values and not a character variable that looks like a date.

SilinBorro
Quartz | Level 8

fecha_ingreso is a date field (datetime)

Snap 2015-04-27 at 13.24.06.jpg

Sam_SAS
SAS Employee

I think what Silvia is trying to do is this:

max.png

The Max operator expects a measure rather than a date.

Sam_SAS
SAS Employee

If you just want to display the most recent date in a table, the simplest solution is to sort a table in descending order by your date column.

I don't think there is an easy way in the VA expression syntax to calculate the most recent date.

You can use the TreatAs operator in a calculated measure to convert the date into a numeric, and then create an aggregated measure using the Max() operator on that calculated item. This will give you the most recent date, but the date is no longer in date form. It will look like this: 19833.

TejaSurapaneni
Lapis Lazuli | Level 10

Hi Silvia,

If you want to show Max date in VA, you have to create Max date column in SAS table using MAX function.

As per sam you can Calculate Max date using calculated items(Treat As) and Aggregated Measures(_Max_). but the result will be in sas days.




Regards,

Teja Surapaneni

SilinBorro
Quartz | Level 8

thanks Teja!.

SilinBorro
Quartz | Level 8

Thanks SAm!

As a quick solution I did what you are suggesting, I made a ranking.

I 'll try  the TreatAs operator.

Greetings.

Silvia

mhamlett
Quartz | Level 8

so what is the final solution of showing the MAX(Date)

 

i can do the treat as and max and get a result.

how do i get that back to a date?

 

 

itchyeyeballs
Pyrite | Level 9

If you want top see the date in a table

 

  • use the treat as function to create a numeric version
  • create a top 1 rank on the numeric version but put the actual date value into the table

dateranker.GIF

 

then

rank.GIF

 

date table.GIF

 

In the example above I have a second column showing when the data was refereshed by sas, to do this I just added column in my underlying data query which inserts todays date against every row.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 9 replies
  • 10721 views
  • 1 like
  • 6 in conversation