DATA Step, Macro, Functions and more

need to get the list of users logged on a hourly basis between the time range given.

Reply
Occasional Contributor
Posts: 12

need to get the list of users logged on a hourly basis between the time range given.

Hi,

 

I am trying to list the users who have logged in or are active during the specified datetime range on a hourly basis.

data ds1;

input usrid stdtme anydtdtm. enddtme anydtdtm.;
format stdtme datetime23. enddtme datetime23.;
 datalines;
1 01/01/2018:14:05:10 01/01/2018:16:05:10
2 01/01/2018:14:15:10 01/01/2018:14:55:10
1 01/01/2018:14:25:30 01/01/2018:17:05:10
4 01/01/2018:14:25:15 01/01/2018:15:05:10
;

I have created a table which lists the datetime values for the specified date or date range.

for ex: id the date selected is 01/01/2018 then the table times  would have

01/01/2018;00:01:00 to 01Jan2018L:23:59:00

 

I am trying to join the two tables in order to get the number of users logged for each hour during the specified datetime range.

PROC SQL;

select usrid, stdtme,  enddtme, COUNT(usrid) 
from times
left outer join ds1
    on stdtme < hour  and hour < enddtme
group by hour
order by hour;
QUIT;

 

I am unable to get the desired report.

my output from the above query:

u
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
...0
401JAN2018:14:25:1501JAN2018:15:05:013
101JAN2018:14:25:3001JAN2018:17:05:013
101JAN2018:14:05:1001JAN2018:16:05:013
101JAN2018:14:05:1001JAN2018:16:05:012
101JAN2018:14:25:3001JAN2018:17:05:012
101JAN2018:14:25:3001JAN2018:17:05:011
...0
...0
...0
...0
...0
...0

 

desired output:

hour|num_logins
------
+-----------
...|0
13:00|0
14:00|1
15:00|1
16:00|2
17:00|0
...|0
...   | 0 
Occasional Contributor
Posts: 11

Re: need to get the list of users logged on a hourly basis between the time range given.

...deleted a prior post since I realize I wasn't giving you what you were asking for... sorry.
Occasional Contributor
Posts: 12

Re: need to get the list of users logged on a hourly basis between the time range given.

This gave me an idea to solve my issue, working on this.
Thanks
Ask a Question
Discussion stats
  • 2 replies
  • 60 views
  • 0 likes
  • 2 in conversation