intck function will not get my desired result

[imdickson]

I am trying to apply a where clause filter in a proc sql statement to only output result that is from today to 2 years earlier.

so today is 5th Oct 2018. I want my data to have data from 1st Oct 2016 to 5th oct 2018.

However, my result is always NULL.

Here are my code:

proc sql;          
create table work.W40RESKE as
            select*
           from dm.ncp_load_tnbt_profiling
             where newdatetime  >=  intck('year',newdatetime,-2) and newdatetime  <= today()
          ;
		  quit;

May I know which part gone terribly wrong? I have read alot of intck example online and it seems like i interpreted wrongly here.

[Kurt_Bremser]

Maxim 1: read the documentation.

intck() requires three arguments: an interval designator, and two SAS dates if a date interval is specified.

-2 as a SAS date is 1959-12-30:

data test;
format mydate yymmddd10.;
mydate = -2;
run;

You may have wanted to use the intnx() function instead, which returns a date (or datetime) from a date and an interval.

[Ksharp]

You should use INTNX()

           where newdatetime  >= intnx('month', intnx('year',newdatetime,-2,'s'),0) and newdatetime  <= today()

[imdickson]

Hi SIr, thanks for your reply. I tried putting it into the where clause but it gave me 0 observations.

27         proc sql;
28         create table work.W40RESKE as
29                     select*
30                    from ncpdm.ncp_load_tnbt_profiling
31         /*             where newdatetime  =  intck('year',newdatetime,-2,) /*and newdatetime  <= today()-365*/
32         where newdatetime  >= intnx('month', intnx('year',newdatetime,-2,'s'),0) and newdatetime  <= today()
33         
34         ;
NOTE: Table WORK.W40RESKE created, with 0 rows and 31 columns.

The data in the source table has records from 2014 until today and it has more than 50 mil records....

Do you think by any chance a small tweak to the statement will work?

[Kurt_Bremser]

Are you sure you have SAS dates in newdatetime? The code works:

data have;
newdatetime = today();
run;

proc sql;
create table work.W40RESKE as
select *
from have
where newdatetime >= intnx('month', intnx('year',newdatetime,-2,'s'),0) and newdatetime  <= today()
;
quit;

Log:

31         proc sql;
32         create table work.W40RESKE as
33         select *
34         from have
35         where newdatetime >= intnx('month', intnx('year',newdatetime,-2,'s'),0) and newdatetime  <= today()
36         ;
NOTE: Table WORK.W40RESKE created, with 1 rows and 1 columns.

Maxim 3: know your data.

[imdickson]

My bad everyone. I forgot to tell that "newdatetime" is actually datetime format, not date.

@Ksharp @Kurt_Bremser

Here is the thing. If i want the where clause to ignore datetime entirely without changing my original source and target column named "newdatetime" . Is there a way to do so?

[imdickson]

My bad everyone. I forgot to tell that "newdatetime" is actually datetime format, not date.

@Ksharp @Kurt_Bremser

Here is the thing. If i want the where clause to ignore datetime entirely without changing my original source and target column named "newdatetime" . Is there a way to do so?

[Kurt_Bremser]

Use the datepart() function to extract dates from datetimes.

[imdickson]

@Kurt_Bremser @Ksharp

I applied datepart to newdatetime column in where clause. However, the where clause doesnt work.

Here is my log

27         proc sql;
28         create table work.W40RESKE as
29                     select*
30
31                    from ncpdm.ncp_load_tnbt_profiling
32         
33         where newdatetime  >= intnx('month', intnx('year',datepart(newdatetime),-2,'s'),0) and datepart(newdatetime)  <= today()
34         
35         ;
NOTE: Compressing data set WORK.W40RESKE decreased size by 53.76 percent. 
      Compressed is 313316 pages; un-compressed would require 677637 pages.
NOTE: Table WORK.W40RESKE created, with 71829414 rows and 31 columns.

36         		  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           5:52.00
      cpu time            5:51.98

Which part do you think i did wrongly?

[Patrick]

@imdickson

newdatetime >= intnx('month', intnx('year',datepart(newdatetime),-2,'s')

1. you're using the same column but on the left with a SAS datetime value and on the right with a SAS data value. So that can't work.

You can use DT... directives with intnx when dealing with datetime values - so 'dtyear' instead of 'year' and then not datepart() function

2. But besides of the above: what the heck is the logic you're trying to implement here. If you wouldn't have mixed up date with datetime then this condition would always be true as you're shifting the same column you're comparing with. That's logic like: Variable >= Variable -2

[Kurt_Bremser]

This

newdatetime  >= intnx('month', intnx('year',datepart(newdatetime),-2,'s'),0)

will be true for any datetime after 1960-01-01, and false for any datetime before 1959-12-31.

If you used the datepart function on both parts:

datepart(newdatetime) >= intnx('month', intnx('year',datepart(newdatetime),-2,'s'),0)

it would always be true.

And this

datepart(newdatetime)  <= today()

will of course be true for any datetime value that is not in the future.