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

[Santt0sh]

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

[bobpep212]

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

[Santt0sh]

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