BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zouinenoah
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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';

View solution in original post

9 REPLIES 9
Reeza
Super User

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
Fluorite | Level 6

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

@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
Fluorite | Level 6
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
Super User Tom
Super User

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

where timepart(datetime_var) <= '12:00't
zouinenoah
Fluorite | Level 6

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
Super User
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
Fluorite | Level 6
Thank you! That worked, and when I used my proc print I got the results needed. Appreciate your help!
Reeza
Super User

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 9 replies
  • 783 views
  • 3 likes
  • 3 in conversation