🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lapis Lazuli | Level 10

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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

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

11 REPLIES 11
Super User

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

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;``````
Jade | Level 19

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

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

Super User

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

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

Lapis Lazuli | Level 10

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

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

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

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;``````
Lapis Lazuli | Level 10

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

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

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

Run an intermediate step that replaces missing values.

Lapis Lazuli | Level 10

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

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

Super User

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

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.

Lapis Lazuli | Level 10

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

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

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

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

Discussion stats
• 11 replies
• 4713 views
• 6 likes
• 3 in conversation