How do you count the number of times a specific time of day (for example 23:59, i.e. 11:59PM) occurs in a datetime interval?
data have;
input ID $ begDate:datetime13. endDate:datetime13.;
format begDate endDate datetime13.;
cards;
aaa 10NOV19:01:49 10NOV19:03:49
bbb 10NOV19:22:49 11NOV19:03:49
ccc 10NOV19:22:49 12NOV19:03:49
;
run;
If your test is to see how many times midnight is in the interval, then the INTCK function does the job.
data want;
set have;
incl_midnight = intck('dtday',begdate,enddate);
run;
If you want some other time, let's say 10:45pm, I believe (untested), you subtract '22:45:00't from both begdate and enddate and then use the INTCK function.
Hi @dataMart87 , it may depend on what your use case is but an easy method would be to use the TIMEPART() function then do a count distinct. See example below
*Load data;
data have;
input ID $ begDate:datetime13. endDate:datetime13.;
format begDate endDate datetime13.;
cards;
aaa 10NOV19:01:49 10NOV19:03:49
bbb 10NOV19:22:49 11NOV19:03:49
ccc 10NOV19:22:49 12NOV19:03:49
;
run;
*Extract time;
data want;
set have;
begTimePart = timepart(begDate);
format begTimePart time.;
run;
*Get count of time appearance;
proc sql;select distinct begTimePart,count(begTimePart)
from work.want group by begTimePart;quit;
If your test is to see how many times midnight is in the interval, then the INTCK function does the job.
data want;
set have;
incl_midnight = intck('dtday',begdate,enddate);
run;
If you want some other time, let's say 10:45pm, I believe (untested), you subtract '22:45:00't from both begdate and enddate and then use the INTCK function.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.