topic Re: About getting the max date in a table field in SAS Visual Analytics

About getting the max date in a table field

SilinBorro — Mon, 27 Apr 2015 14:22:24 GMT

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

Re: About getting the max date in a table field

ballardw — Mon, 27 Apr 2015 14:53:29 GMT

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.

Re: About getting the max date in a table field

SilinBorro — Mon, 27 Apr 2015 16:28:58 GMT

fecha_ingreso is a date field (datetime)

Re: About getting the max date in a table field

Sam_SAS — Mon, 27 Apr 2015 17:30:40 GMT

I think what Silvia is trying to do is this:

The Max operator expects a measure rather than a date.

Re: About getting the max date in a table field

Sam_SAS — Mon, 27 Apr 2015 21:39:52 GMT

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.

Re: About getting the max date in a table field

TejaSurapaneni — Tue, 28 Apr 2015 05:14:51 GMT

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

Re: About getting the max date in a table field

SilinBorro — Wed, 29 Apr 2015 13:10:33 GMT

Thanks SAm!

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

I 'll try the TreatAs operator.

Greetings.

Silvia

Re: About getting the max date in a table field

SilinBorro — Wed, 29 Apr 2015 13:12:25 GMT

thanks Teja!.

Re: About getting the max date in a table field

mhamlett — Thu, 27 Apr 2017 17:47:51 GMT

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?

Re: About getting the max date in a table field

itchyeyeballs — Fri, 28 Apr 2017 12:18:25 GMT

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

then

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.