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

Santt0sh — Thu, 21 Jun 2018 14:52:52 GMT

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:

.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
.	.	.	0
4	01JAN2018:14:25:15	01JAN2018:15:05:01	3
1	01JAN2018:14:25:30	01JAN2018:17:05:01	3
1	01JAN2018:14:05:10	01JAN2018:16:05:01	3
1	01JAN2018:14:05:10	01JAN2018:16:05:01	2
1	01JAN2018:14:25:30	01JAN2018:17:05:01	2
1	01JAN2018:14:25:30	01JAN2018:17:05:01	1
.	.	.	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

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

bobpep212 — Thu, 21 Jun 2018 15:12:59 GMT

...deleted a prior post since I realize I wasn't giving you what you were asking for... sorry.

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

Santt0sh — Wed, 27 Jun 2018 14:27:01 GMT

This gave me an idea to solve my issue, working on this.
Thanks

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

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

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

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