Intck function is not working

[Sandeep77]

Hi all,

I am using intck function in a code to find the difference between two dates but in the output result it just shows a dot. Could you please let me know the mistake I am doing in my code? Date format is Datetime22.3 (e.g. 30JUL2023:00:00:00.000) and type is Numeric.

proc sql;
create table All_Mortgage_application as 
select 
    t1.ApplicationDate,
		   t2.SourceUpdateDate,
		   intck('day', t2.SourceUpdateDate, t1.ApplicationDate) as DaysBetween
from BDATA.vw_ExCreditApplicationCurrent as t1
inner join BDATA.vw_CreditAccountHeaderCurrent as t2 on t1.PersonID = t2.PersonID
where AccountTypeCode in ('3','12','13','14','16','25','30','31','32','33','34','35','50','69')
and ApplicationDate >= &Three_Months_Ago
    and upcase(ApplicationTypeDesc) contains 'MORTGAGE'
and  t2.SourceUpdateDate >= t1.ApplicationDate;
quit;
Log:
29         proc sql;
30         create table All_Mortgage_application as
31         select
32             t1.ApplicationDate,
33         		   t2.SourceUpdateDate,
34         		   intck('day', t2.SourceUpdateDate, t1.ApplicationDate) as DaysBetween
35         from BDATA.vw_ExCreditApplicationCurrent as t1
36         inner join BDATA.vw_CreditAccountHeaderCurrent as t2 on t1.PersonID = t2.PersonID
37         where AccountTypeCode in ('3','12','13','14','16','25','30','31','32','33','34','35','50','69')
38         and ApplicationDate >= &Three_Months_Ago
39             and upcase(ApplicationTypeDesc) contains 'MORTGAGE'
40         and  t2.SourceUpdateDate >= t1.ApplicationDate;
NOTE: Invalid argument to function INTCK. Missing values may be generated.
NOTE: Compressing data set WORK.ALL_MORTGAGE_APPLICATION increased size by 28.57 percent. 
      Compressed is 9 pages; un-compressed would require 7 pages.
NOTE: Table WORK.ALL_MORTGAGE_APPLICATION created, with 16758 rows and 3 columns.

41         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2:02.80
      user cpu time       3.51 seconds
      system cpu time     0.14 seconds
      memory              5458.09k
      OS Memory           34648.00k
      Timestamp           09/11/2023 09:51:32 AM
      Step Count                        6  Switch Count  52
      

2                                                          The SAS System                           08:53 Monday, September 11, 2023

42         
43         %LET _CLIENTTASKLABEL=;
44         %LET _CLIENTPROCESSFLOWNAME=;
45         %LET _CLIENTPROJECTPATH=;
46         %LET _CLIENTPROJECTPATHHOST=;
47         %LET _CLIENTPROJECTNAME=;
48         %LET _SASPROGRAMFILE=;
49         %LET _SASPROGRAMFILEHOST=;
50         
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53         
54         
55         QUIT; RUN;
56         

[PaigeMiller · Accepted solution]

If the values are datetimes, then you have to tell INTCK to use datetime intervals. 'DAY' is a date (not datetime) interval, and so it will not work. To tell SAS to use datetime intervals, use

intck('dtday', t2.SourceUpdateDate, t1.ApplicationDate)