How to add hours as a detail selection in to a date segment

[sarahzhou]

Hi,

The latest data_load_dt is 03NOV2022:19:57:51.251 in my system, and today is 4NOV2022 in my local time, it is near 11am now.

I want to select the data of data_load date from yesterday 7am to today 7am, that is 03NOV2022:07:00:00.000 to 04NOV2022:07:00:00.000.

How can I code the where condition?

where data_load_date<'today7am'.d and data_load_date>'yesterday7am'.d

How do I express today 7am and yesterday 7am in SAS language?

Previously I used today and yesterday macro as below:

%let today=%sysfunc(intnx(day,%sysfunc(today()),0),date9.);
%let yesterday=%sysfunc(intnx(day,%sysfunc(today()),-1),date9.);

However, I don't know how to add the hours into the date as a selection critical

Kindly advise.
Thank you

[sarahzhou · Accepted solution]

Hi, I think I fixed this by

"&yesterday.:07:00"DT.

🙂

[Kurt_Bremser]

You can do this in data step code:

where dhms(today() - 1,7,0,0) < data_load_date < dhms(today(),7,0,0)

If the "today" and "yesterday" values need to be determined independent of the result of the TODAY() function, you can use macro variables as arguments to the DHMS functions. Also see Maxim 28.