topic Re: to create the start datetime from midnight to 23:59 of a given date in SAS Programming

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

Santt0sh — Thu, 21 Jun 2018 06:11:51 GMT

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.

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

Kurt_Bremser — Thu, 21 Jun 2018 07:49:52 GMT

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;

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

Santt0sh — Thu, 21 Jun 2018 11:15:02 GMT

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

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

Kurt_Bremser — Thu, 21 Jun 2018 11:17:55 GMT

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

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

data_null__ — Thu, 21 Jun 2018 15:48:20 GMT

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;

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

Kurt_Bremser — Thu, 21 Jun 2018 15:55:53 GMT

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

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

Santt0sh — Thu, 21 Jun 2018 20:11:42 GMT

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:

.	.	.	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: to create the start datetime from midnight to 23:59 of a given date

Kurt_Bremser — Wed, 27 Jun 2018 06:25:02 GMT

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;

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

Santt0sh — Wed, 27 Jun 2018 14:33:15 GMT

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

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

Kurt_Bremser — Wed, 27 Jun 2018 14:43:51 GMT

Run an intermediate step that replaces missing values.

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

Santt0sh — Wed, 27 Jun 2018 14:48:55 GMT

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

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

Kurt_Bremser — Thu, 28 Jun 2018 07:49:38 GMT

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.