Help with Datetime filtering for a large dataset

[zouinenoah]

I have a very large dataset with a column that has a Datetime column with the time part such as "02JAN2021:14:43:00.000" and "02JAN2021:12:44:00.000". I'm trying to filter my data so that way I only have the results in my dataset that have Datetime's up until the Time "12:00:00.000" and print out the new dataset showing only these results.

New to sas so I'm not quite sure how to go about doing this. Any help for what is probably
something really simple would be really appreciated.

Thanks

[Reeza]

Are you only interested in the time portion then, regardless of date?

If so, look at the TIMEPART and HOUR functions.
TIMEPART will extract the time component and HOUR() will convert that to an hour so your criteria can be:

data want;
set have;
where hour(dateTimeVariable) < 12;
run;

EDIT: Apparently hour will work on datetime directly so you don't need the TIMEPART.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p1wo6rbbreow6cn0zig8ajg31qq8.h....

[zouinenoah]

Yes, I'm only looking at the time portion. The date doesn't matter because when pulling in the data I already have it set to particular dates. Just needed to now filter the time portion next. Thanks!

[Tom]

---

@zouinenoah wrote:

Yes, I'm only looking at the time portion. The date doesn't matter because when pulling in the data I already have it set to particular dates. Just needed to now filter the time portion next. Thanks!

---

Seems strange.  If you select two days worth of data and then exclude the afternoon records from the first day what is it that you have left?

Are you sure you are not asking to do something like:

where datetime_var between LOWER and UPPER

Where LOWER and UPPER are datetime values ?

For example this will limit to a 36 hour window.

where datetime_var between '01JAN2021:00:00'dt and '02JAN2021:12:00:00'dt 

[zouinenoah]

Well I'm pulling in 7 months worth of data from January to July, but am only interested in time(s) that are up until 12pm. This looks like it will work, I will go ahead and try to see.

[Tom]

So you want just the AM records, regardless of the date?

where timepart(datetime_var) <= '12:00't

[zouinenoah]

I'm using the statement right before that to limit the months of interest:

if mdy(01,01,2021)<=datepart(DischargeFullDate)<=mdy(07,31,2021); then I wrote the statement:

where timepart(DischargeFullDate) <= '12:00't 

but then get there error NOTE: WHERE CLAUSE HAS BEEN REPLACED.

since after I have another "if" statement: if "900<=CompanyCode<="916"

                                                                 where AttendingSpecialty='Hospitalist';

and then when I run a proc print to see if my results are what I wanted, it doesn't work. Is there a way to possibly fix that?

[Reeza · Accepted solution]

You have 2 WHERE statements.

where timepart(DischargeFullDate) <= '12:00't
where AttendingSpecialty='Hospitalist';

Combine them using an AND.
You have 2 WHERE statements.

where timepart(DischargeFullDate) <= '12:00't
and AttendingSpecialty='Hospitalist';

[zouinenoah]

Thank you! That worked, and when I used my proc print I got the results needed. Appreciate your help!

[Reeza]

You can specify your date, time and datetime constants as follows for comparisons. Note that you must have the character after the quotes to tell SAS how to interpret it, and it must follow the specified format, ie DDMONYY for a date constant which is 01Jan21, with the exception that the year can often be 2 or 4 digit years. I recommend against ever using 2 digit years unless you absolutely have to 🙂

Characters That Follow a Character Constant	Possible Interpretations	Examples
b	bit testing constant	'00100000'b
d	date constant	'01jan04'd
dt	datetime constant	'18jan2005:9:27:05am'dt
n	name literal	'My Table'n
t	time constant	'9:25:19pm't
x	hexadecimal notation	'534153'x

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/p0cq7f0icfjr8vn19vyunwmmsl7m.htm