How to filter date old column from system date

[pepevo]

Hello Experts,

I saw a table with column declared in 8 numeric format datetime10.

if I want to run the query in SAS to put the column with a day old from today.   How do I do that?

Table aaa:

receipt_number     dwh_isrt_dt

MSC123353          07MAY2015:10:31:24

MSC529890          09JUL2017:08:09:12

MSC524141          28AUG2017:08:09:12

MSC258890          09JUL2018:08:09:12

EAC129098          17APR2019:21:34:12

EAC129148          17APR2019:21:34:12

EAC349098          18APR2019:21:34:12

EAC234698          18APR2019:21:34:12

EAC224698          19APR2019:01:33:02

EAC587698          19APR2019:21:34:12

I don't find trunc(sysdate -1) in SAS.  I have tried 

select DISTINCT a.receipt_number, sbmtd_dt_id from aaa
where today(sbmtd_dt_id - 1);

or

select DISTINCT a.receipt_number, sbmtd_dt_id from aaa
having sbmtd_dt_id = max(sbmtd_dt_id - 1);

both queries ares running nonestop w/o result, 

In Oracle I can run 

SELECT DISTINCT a.receipt_number, a.sbmtd_dt_id
FROM APPLICATION_CDIM a
WHERE (trunc(a.sbmtd_dt_id) = trunc(sysdate - 1) ;

even I try to find the last record of the table by using 'max(sbmtd_dt_id)':

select receipt_number, sbmt_dt_id from table aaa where max(sbmtd_dt_id);  

the query doesn't work.  Not sure I am doing a right thing here.  Wondering for Proc SQL code to generate the select with first couple rows and last 5 rows from the millions records?  Also, can you please suggest the best way to go about pull the column fetch in a day, 2, 3 days old?  

thank you for your helps.

Bach-Nga

[PaigeMiller]

I saw a table with column declared in 8 numeric format datetime10.

if I want to run the query in SAS to put the column with a day old from today.   How do I do that?

Explain this in more detail. Show us the output you want.

[pepevo]

the query is running for awhile and give me no result but from log I see:

PROC SQL;

SELECT a.receipt_number, bcf.actn_dt_in_id

FROM EXISDRDM.APPLICATION_CDIM a

INNER JOIN EXISDRDM.BNFT_CURR_FACT bcf

on bcf.application_id = a.application_id

INNER JOIN EXISDRDM.BNFT_HIST_ACTN_LDIM hist

ON bcf.BNFT_HIST_ACTN_ID = hist.BNFT_HIST_ACTN_ID

WHERE bcf.actn_dt_in_id = today(bcf.actn_dt_in_id - 1)

;

WARNING: Function TODAY requires at most 0 argument(s). The extra one(s) will be ignored.

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

real time 1:36.65

cpu time 4:59.46

the other for pulling the last record, still no result but according to the log I have:

proc sql ;

select a.receipt_number, a.dwh_isrt_dt

FROM EBI3CONS.history a

HAVING a.dwh_isrt_dt = max(a.dwh_isrt_dt);

NOTE: The query requires remerging summary statistics back with the original data.

NOTE: There were 1354550678 observations read from the data set EBI3CONS.HISTORY.

ORACLE: Pipelined I/O thread did not timely terminate

NOTE: PROCEDURE SQL used (Total process time):

real time 10:45.26

cpu time 18:15.75

on another hands, I run the query with a day old (not sure it's right) and I get a result but in year 2015.  It should give me none if a day old not match with receipt_number, right?

SELECT a.receipt_number, a.dwh_isrt_dt FROM EBI3CONS.history a WHERE a.receipt_number ='MSC0416310095' AND today(a.dwh_irt_dt -1);

thank you for your help.

v/r,

Bach-Nga

[Kurt_Bremser]

From what you initially posted, it appears that dwh_isrt_dt is a datetime value, not a date value. It counts seconds, not days.

Note that trunc() is a function that works on the binary structure of number (Maxim 1: Read the -linked- Documentation).

To extract the date from a datetime, use the datepart() function.

The today() function has no arguments, it always delivers today's date as a SAS date value (days from 1960-01-01).

To calculate yesterday's date, simply use

today() - 1

[pepevo]

thank you all for helping with sysdate -1.

I tried datepart (column -1) and today (column -1).  Thank you for showing me depart(column) = today() -1 🙂

thank you again and have happy Easter's.

v/r,

Bach-Nga

[Ksharp · Accepted solution]

You want this ?

select DISTINCT a.receipt_number   from aaa
where   datepart( dwh_isrt_dt)= today()-1 ;