BookmarkSubscribeRSS Feed
Obsidian | Level 7

Dear all,

Here is my situation : I have a datasource with a row for each connection of a username in a system.

I'm building a report with many statistics, and in the last tab, I want to display in a table the most recent connection date for each users.

I've been fighting with this and can't stand it now.

Using Rank does not work, since it either shows multiple dates for each users or only the group of users that gets connected on the most recent date (like today or yesterday). For those who got connected earlier, they are treated as "others" and not displayed !

I found a way to display the table properly but it ONLY WORKS when the date is converted to a number TreasAs... 


First : basic table with names & date, you see that there are as many rows as date & username.



Second, if i try to create a rank on a TreasAs Date (number), then it only shows the group of people that get connected on the most recent day together, the others are treated separetly.





Then if I only put in the table the TreasAs Date (number), it WORKS ! I have my most recent date for each and all users, but in a numeric format !!!






Super User

Please do NOT post data in pictures. See the links in my footnote for advice on posting example data.

A data step allows all possible helpers to recreate your dataset with a simple copy/paste and submit.


A proc sql solution would look like

proc sql;
create table want as
select userid, max(connection_date) as maxdate
from have
group by userid;
Obsidian | Level 7

ok thanks for the advice.


the proc solution is not a good one, since it will remove all the "detailed data" i need for other graphs and table in my report.


Obsidian | Level 7

That's what I thought also, but the date format is not available :




And I also tried to re-create a calculated item from the numbered date format to a real date format using but then the behaviour comes back to the detailed view and not the (max) date.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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
  • 4 replies
  • 2 in conversation