to create the start datetime from midnight to 23:59 of a given date

[Santt0sh]

Hi,

I am trying to create a variable which will have the values from 00:00 to 23:59 for a given date, for example

the start date passed is 01Jan2018, then the output should look something like this.

datetme

01jan2018:00:01:00

01jan2018:01:00:00

01jan2018:02:00:00

|              

|   /*till */

|

01Jan2018:23:59:00

kindly suggest me on this.

[Kurt_Bremser]

SAS datetime values are counts of seconds. If you want every minute of a day, you can do this:

data want;
do dt_time =  dhms("01jan2018",0,0,0) to dhms("01jan2018",23,59,59) by 60;
  output;
end;
run;

[data_null__]

Did you forget something? D

---

@Kurt_Bremser wrote:

SAS datetime values are counts of seconds. If you want every minute of a day, you can do this:

data want;
do dt_time =  dhms("01jan2018",0,0,0) to dhms("01jan2018",23,59,59) by 60;
  output;
end;
run;

---

[Kurt_Bremser]

---

@data_null__ wrote:

Did you forget something? D

---

@Kurt_Bremser wrote:

SAS datetime values are counts of seconds. If you want every minute of a day, you can do this:

data want;
do dt_time =  dhms("01jan2018",0,0,0) to dhms("01jan2018",23,59,59) by 60;
  output;
end;
run;

---

---

Ouch. That's what happens when no test is run.

The past two days I've had no SAS at hand, as I'm at the SAS Forum Deutschland and only have my tablet with me.

[Santt0sh]

Hi Kurt,

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 as suggested by you to list , the datetime values for the specified date or date range.

for ex: id the date selected is 01/01/2018 then the table times  has interval of 1 hours

01/01/2018;00:01:00 to 01Jan2018:23:00: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

[Kurt_Bremser]

If you want to determine how may users were logged in during a given hour, try this:

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

data int (keep=usrid hour);
set ds1;
format hour datetime23.;
do hour = intnx('hour',stdtme,0,'b') to intnx('hour',enddtme,0,'b') by 3600;
  output;
end;
run;

proc sql;
create table want as
select hour, count(distinct usrid) as users_logged_in
from int
group by hour;
quit;

[Santt0sh]

Hi Kurt,

Thank you for your quick response,
This will help, when the user provides the required datetime, I have set the default start date when user dsnt provide any input 01/01/1099 start hour 00 , start minute 01 and end date as 12/31/2099 endhour 23 end minute 59
Please suggest

[Kurt_Bremser]

Run an intermediate step that replaces missing values.

[Santt0sh]

Hi Kurt,

I have attached the code which i am working on,

could you please have a look at this and let me know if i am missing anything.

thanks

[Kurt_Bremser]

After some corrections, this code works:

DATA K;
INPUT ID START ANYDTDTM. ENDT ANYDTDTM.;
FORMAT START DATETIME23. ENDT DATETIME23.;
DATALINES;
1 01/01/2018:00:00:01 01/01/2018:09:02:45
2 01/01/2018:00:00:01 01/01/2018:01:01:45
1 01/01/2018:01:01:01 01/01/2018:03:32:32
1 01/01/2018:09:01:00 01/01/2018:10:42:05
3 01/01/2018:09:10:15 01/01/2018:10:38:00
2 01/01/2018:19:01:08 01/01/2018:20:20:32
;
RUN;

DATA K2;
SET K;
format hours datetime18.;
DO I = 0 TO INTCK('HOUR',START,ENDT);
HOURS=INTNX('HOUR',START,I,'B');
OUTPUT;
END;
RUN;

PROC SQL;
SELECT hours, COUNT(distinct ID) AS LOGINS
from k2
group by hours
order by hours;
quit;

So your remaining task is to get your original data into a shape that corresponds to dataset k.

[Santt0sh]

Thank you Kurt, please let me know how will this behave if the user selects a different end date??
Please accept my sincere apologies,
If I had not mentioned in my question posted

[Kurt_Bremser · Accepted solution]

---

@Santt0sh wrote:
Thank you Kurt, please let me know how will this behave if the user selects a different end date??
Please accept my sincere apologies,
If I had not mentioned in my question posted

---

As long as the end date is greater or equal to the start date, the step will work; it's just that it will create more observations.