DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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.


Accepted Solutions
Solution
a month ago
Super User
Posts: 10,258

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,258

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 3,852

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

Posted in reply to KurtBremser

Did you forget something? D

 


@KurtBremser 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
Posts: 10,258

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

Posted in reply to data_null__

 


@data_null__ wrote:

Did you forget something? D

 


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

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

Posted in reply to KurtBremser

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
401JAN2018:14:25:1501JAN2018:15:05:013
101JAN2018:14:25:3001JAN2018:17:05:013
101JAN2018:14:05:1001JAN2018:16:05:013
101JAN2018:14:05:1001JAN2018:16:05:012
101JAN2018:14:25:3001JAN2018:17:05:012
101JAN2018:14:25:3001JAN2018:17:05:011
...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
Posts: 10,258

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

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

Posted in reply to KurtBremser
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
Super User
Posts: 10,258

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

Run an intermediate step that replaces missing values.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

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

Posted in reply to KurtBremser

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
Posts: 10,258

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

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
Solution
a month ago
Super User
Posts: 10,258

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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