2

I have a table named case_DataTable_d in which column name value_dt have different date values. I want get number of day difference between that date and date of today.

This is my code

proc sql noprint;
 create table daystoOverdue_list as
 select distinct business_object_rk , DateDiff(DAY, value_dt, Today()) as value_dt
 from case_DataTable_d as tbl
 where tbl.cust_field_nm eq "x_case_dte_dd"
 and datepart(tbl.value_dt) < today();
quit;

I'm having errors that

Day is not any column name

function DateDiff could not be located.

Azeem112
  • 337
  • 1
  • 8
  • 23

1 Answers1

3

DateDiff is not a valid SAS function. Try intck:

%let today=%sysfunc(date());
proc sql noprint;
 create table daystoOverdue_list as
 select distinct business_object_rk 
    , intck('DAY', datepart(tbl.value_dt), &today) as value_dt
 from case_DataTable_d as tbl
 where tbl.cust_field_nm eq "x_case_dte_dd"
 and datepart(tbl.value_dt) < &today;

This function returns the number of interval boundaries of a given kind that lie between two dates, times, or datetime values (see documentation).

Azeem112
  • 337
  • 1
  • 8
  • 23
Allan Bowe
  • 12,306
  • 19
  • 75
  • 124
  • I don't know why but It's only giving me a period . means a missing value – Azeem112 Dec 12 '17 at 11:49
  • And there are lots of records who due date is less then today – Azeem112 Dec 12 '17 at 11:49
  • My bad - `today()` isn't a SAS function either. Should be `date()`. – Allan Bowe Dec 12 '17 at 12:25
  • also, running the function on the where clause is inefficient - will fire for every record. Better to make a constant and use that via a macro variable. – Allan Bowe Dec 12 '17 at 12:26
  • Still not working but thank you for giving your precious time. – Azeem112 Dec 12 '17 at 12:59
  • 1
    Try changing this `intck('DAY', DATEPART(value_dt), &today)` . You're mixing date and datetime variables is likely why you're running into these issues. Make sure everything is date or datetime and use the appropriate intervals, ie DAY if date, DTDAY if datetime. – Reeza Dec 12 '17 at 17:45
  • aah - should have deduced that from the `datepart` function in the OP's example. Thanks @Reeza! – Allan Bowe Dec 13 '17 at 08:25