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 |
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: