topic Re: How to filter date old column from system date in SAS Programming

topic Re: How to filter date old column from system date in SAS Programming https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552450#M153574 <P>From what you initially posted, it appears that <SPAN>dwh_isrt_dt is a </SPAN>datetime value, not a date value. It counts seconds, not days.</P> <P>Note that <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=n1lfp83m4yht68n1luxn5tnmuhp3.htm&locale=en" target="_blank" rel="noopener">trunc()</A> is a function that works on the binary structure of number (Maxim 1: Read the -linked- Documentation).</P> <P>To extract the date from a datetime, use the <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p0rttbu7w62xgzn1damccyuwpld8.htm&locale=en" target="_blank" rel="noopener">datepart()</A> function.</P> <P>The <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p0hm9egy8s7mokn1mz0yxng80ax5.htm&locale=en" target="_blank" rel="noopener">today()</A> function has no arguments, it always delivers today's date as a SAS date value (days from 1960-01-01).</P> <P>To calculate yesterday's date, simply use</P> <PRE>today() - 1</PRE> Fri, 19 Apr 2019 14:23:42 GMT Kurt_Bremser 2019-04-19T14:23:42Z How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552426#M153563 <DIV><DIV class="ia-splitter"><DIV class=" aui-page-panel"><DIV class="page view"><DIV class="wiki-content"><DIV class="table-wrap">Hello Experts,</DIV><DIV class="table-wrap"> </DIV><DIV class="table-wrap">I saw a table with column declared in 8 numeric format datetime10.</DIV></DIV></DIV></DIV></DIV></DIV><P>if I want to run the query in SAS to put the column with a day old from today.   How do I do that?</P><P>Table aaa:</P><P>receipt_number     dwh_isrt_dt</P><P>MSC123353          07MAY2015:10:31:24</P><P>MSC529890          09JUL2017:08:09:12</P><P>MSC524141          28AUG2017:08:09:12</P><P>MSC258890          09JUL2018:08:09:12</P><P>EAC129098          17APR2019:21:34:12</P><P>EAC129148          17APR2019:21:34:12</P><P>EAC349098          18APR2019:21:34:12</P><P>EAC234698          18APR2019:21:34:12</P><P>EAC224698          19APR2019:01:33:02</P><P>EAC587698          19APR2019:21:34:12</P><P> </P><P>I don't find trunc(sysdate -1) in SAS.  I have tried </P><P>select DISTINCT a.receipt_number, sbmtd_dt_id from aaa<BR />where today(sbmtd_dt_id - 1);</P><P>or</P><P>select DISTINCT a.receipt_number, sbmtd_dt_id from aaa<BR />having sbmtd_dt_id = max(sbmtd_dt_id - 1);</P><P> </P><P>both queries ares running nonestop w/o result, </P><P>In Oracle I can run </P><TABLE><TBODY><TR><TD><P>SELECT DISTINCT a.receipt_number, a.sbmtd_dt_id<BR />FROM APPLICATION_CDIM a<BR />WHERE (trunc(a.sbmtd_dt_id) = trunc(sysdate - 1) ;</P><P> </P></TD></TR></TBODY></TABLE><P> </P><P>even I try to find the last record of the table by using 'max(sbmtd_dt_id)':</P><P>select receipt_number, sbmt_dt_id from table aaa where max(sbmtd_dt_id);  </P><P>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, <SPAN>can you please suggest the best way to go about </SPAN>pull the column fetch in a day, 2, 3 days old?  </P><P> </P><P>thank you for your helps.</P><P> </P><P>Bach-Nga</P><P> </P> Fri, 19 Apr 2019 13:07:44 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552426#M153563 pepevo 2019-04-19T13:07:44Z Re: How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552428#M153564 <DIV> <DIV class="ia-splitter"> <DIV class=" aui-page-panel"> <DIV class="page view"> <DIV class="wiki-content"> <BLOCKQUOTE> <DIV class="table-wrap">I saw a table with column declared in 8 numeric format datetime10.</DIV> </BLOCKQUOTE> </DIV> </DIV> </DIV> </DIV> </DIV> <BLOCKQUOTE> <P>if I want to run the query in SAS to put the column with a day old from today.   How do I do that?</P> </BLOCKQUOTE> <P><BR />Explain this in more detail. Show us the output you want.</P> Fri, 19 Apr 2019 13:11:47 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552428#M153564 PaigeMiller 2019-04-19T13:11:47Z Re: How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552444#M153571 <P>the query is running for awhile and give me no result but from log I see:</P><P> </P><DIV class="sasSource">PROC SQL;</DIV><DIV class="sasSource">SELECT a.receipt_number, bcf.actn_dt_in_id</DIV><DIV class="sasSource">FROM EXISDRDM.APPLICATION_CDIM a</DIV><DIV class="sasSource">INNER JOIN EXISDRDM.BNFT_CURR_FACT bcf</DIV><DIV class="sasSource">on bcf.application_id = a.application_id</DIV><DIV class="sasSource">INNER JOIN EXISDRDM.BNFT_HIST_ACTN_LDIM hist</DIV><DIV class="sasSource">ON bcf.BNFT_HIST_ACTN_ID = hist.BNFT_HIST_ACTN_ID</DIV><DIV class="sasSource">WHERE bcf.actn_dt_in_id = today(bcf.actn_dt_in_id - 1)</DIV><DIV class="sasSource">;</DIV><DIV class="sasWarning">WARNING: Function TODAY requires at most 0 argument(s). The extra one(s) will be ignored.</DIV><DIV class="sasNote">NOTE: No rows were selected.</DIV><DIV class="sasNote"><DIV class="sasNote">NOTE: PROCEDURE SQL used (Total process time):</DIV><DIV class="sasNote">real time 1:36.65</DIV><DIV class="sasNote">cpu time 4:59.46</DIV></DIV><P>the other for pulling the last record, still no result but according to the log I have:</P><P> </P><DIV class="sasSource">proc sql ;</DIV><DIV class="sasSource">select a.receipt_number, a.dwh_isrt_dt</DIV><DIV class="sasSource">FROM EBI3CONS.history a</DIV><DIV class="sasSource">HAVING a.dwh_isrt_dt = max(a.dwh_isrt_dt);</DIV><DIV class="sasSource"> </DIV><DIV class="sasNote">NOTE: The query requires remerging summary statistics back with the original data.</DIV><DIV class="sasNote">NOTE: There were 1354550678 observations read from the data set EBI3CONS.HISTORY.</DIV><DIV class="sasSource">ORACLE: Pipelined I/O thread did not timely terminate</DIV><DIV class="sasSource"><DIV class="sasNote">NOTE: PROCEDURE SQL used (Total process time):</DIV><DIV class="sasNote">real time 10:45.26</DIV><DIV class="sasNote">cpu time 18:15.75</DIV></DIV><P> </P><P> </P><P>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?</P><P> </P><P><STRONG>SELECT</STRONG> a.receipt_number, a.dwh_isrt_dt <STRONG>FROM</STRONG> EBI3CONS.history a <STRONG>WHERE</STRONG> a.receipt_number ='MSC0416310095' <STRONG>AND</STRONG> today(a.dwh_irt_dt -1);</P><P><SPAN class="confluence-embedded-file-wrapper"><IMG src="https://sens3.leidos.com/confluence/download/attachments/15631183/image2019-4-18_14-39-54.png?version=1&modificationDate=1555612794270&api=v2" border="0" /></SPAN></P><P>thank you for your help.</P><P> </P><P>v/r,</P><P>Bach-Nga</P><P> </P> Fri, 19 Apr 2019 14:06:54 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552444#M153571 pepevo 2019-04-19T14:06:54Z Re: How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552449#M153573 <P>You want this ?</P> <P> </P> <P> </P> <P><SPAN>select DISTINCT a.receipt_number   from aaa</SPAN><BR /><SPAN>where   datepart( dwh_isrt_dt)= today()-1 ;</SPAN></P> Fri, 19 Apr 2019 14:17:50 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552449#M153573 Ksharp 2019-04-19T14:17:50Z Re: How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552450#M153574 <P>From what you initially posted, it appears that <SPAN>dwh_isrt_dt is a </SPAN>datetime value, not a date value. It counts seconds, not days.</P> <P>Note that <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=n1lfp83m4yht68n1luxn5tnmuhp3.htm&locale=en" target="_blank" rel="noopener">trunc()</A> is a function that works on the binary structure of number (Maxim 1: Read the -linked- Documentation).</P> <P>To extract the date from a datetime, use the <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p0rttbu7w62xgzn1damccyuwpld8.htm&locale=en" target="_blank" rel="noopener">datepart()</A> function.</P> <P>The <A href="https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarget=p0hm9egy8s7mokn1mz0yxng80ax5.htm&locale=en" target="_blank" rel="noopener">today()</A> function has no arguments, it always delivers today's date as a SAS date value (days from 1960-01-01).</P> <P>To calculate yesterday's date, simply use</P> <PRE>today() - 1</PRE> Fri, 19 Apr 2019 14:23:42 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552450#M153574 Kurt_Bremser 2019-04-19T14:23:42Z Re: How to filter date old column from system date https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552467#M153580 <P>thank you all for helping with sysdate -1.</P><P> </P><P>I tried datepart (column -1) and today (column -1).  Thank you for showing me depart(column) = today() -1 <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P> </P><P>thank you again and have happy Easter's.</P><P> </P><P>v/r,</P><P>Bach-Nga</P> Fri, 19 Apr 2019 15:27:36 GMT https://communities.sas.com/t5/SAS-Programming/How-to-filter-date-old-column-from-system-date/m-p/552467#M153580 pepevo 2019-04-19T15:27:36Z