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

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

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

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

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__
Jade | Level 19

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

 


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

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

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

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

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
Lapis Lazuli | Level 10
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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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