Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Max connection date of users in a table : works only with TreastAs number ?!

Reply
Occasional Contributor
Posts: 18

Max connection date of users in a table : works only with TreastAs number ?!

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.

2018-04-25_15-29-24.png

 

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.

 

 

2018-04-25_15-31-01.png2018-04-25_15-30-17.png

 

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

HELP !!

thanks

 

2018-04-25_15-28-16.png

 

Super User
Posts: 10,570

Re: Max connection date of users in a table : works only with TreastAs number ?!

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;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Max connection date of users in a table : works only with TreastAs number ?!

Posted in reply to KurtBremser

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.

 

Super User
Posts: 10,570

Re: Max connection date of users in a table : works only with TreastAs number ?!

About the display of dates: you need to assign a date format to the newly created variable, which would be yymmdd10. in your case.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 18

Re: Max connection date of users in a table : works only with TreastAs number ?!

Posted in reply to KurtBremser

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

 

2018-04-25_18-32-05.png

 

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.

Ask a Question
Discussion stats
  • 4 replies
  • 112 views
  • 0 likes
  • 2 in conversation