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 |
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.