Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Help with Datetime filtering for a large dataset

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 08-23-2021 04:07 PM
(782 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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';

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';

9 REPLIES 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@zouinenoah wrote:

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 `

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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';

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

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.