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
|
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
You want this ?
select DISTINCT a.receipt_number from aaa
where datepart( dwh_isrt_dt)= today()-1 ;
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.
the query is running for awhile and give me no result but from log I see:
the other for pulling the last record, still no result but according to the log I have:
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
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
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
You want this ?
select DISTINCT a.receipt_number from aaa
where datepart( dwh_isrt_dt)= today()-1 ;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.