06-22-2017 09:31 AM
I ahve two dates:
Need calculate days between:
intck('DAY', Date1, Date2) format=comma10. AS TAT,
No result, it is .
06-22-2017 09:35 AM - edited 06-22-2017 09:35 AM
The variables you have listed are datetime variables, not dates. You can do like this do extract the date part and calculate the number of days between them
data _null_; DateTime1= '06JUN2017:00:00:00.000'dt; DateTime2= '12JUN2017:00:00:00.000'dt; Date1 = datepart(DateTime1); Date2 = datepart(DateTime2); daysbetween = intck('day', Date1, Date2); put daysbetween; run;
06-22-2017 09:50 AM
Just to add to the already great info, you don't actually need to ue intck at all as dates are days since 01jan1960, so you can simply do:
datepart(date2) - datepart(date1)
The Subtraction should be quicker than the function call.
06-22-2017 10:42 AM
@ballardw: Yes, that will work, and requires less code than including the datepart function, but doesn't run much faster than using the datepart function. Conversely, like @RW9 suggested, just doing the subtraction is faster than either.
However, faster yet would be to leave the datepart function out all together (i.e., (date2-date1)/86400):
62 data have (drop=i); 63 Date1= '06JUN2017:00:00:00.000'dt; 64 Date2= '12JUN2017:00:00:00.000'dt; 65 do i=1 to 10000000; 66 output; 67 end; 68 run; NOTE: The data set WORK.HAVE has 10000000 observations and 2 variables. NOTE: DATA statement used (Total process time): real time 0.73 seconds cpu time 0.29 seconds 69 70 proc sql noprint; 71 create table want1 as 72 select intck('DAY', datepart(Date1), datepart(Date2)) format=comma10. AS TAT 73 from have 74 ; NOTE: Table WORK.WANT1 created, with 10000000 rows and 1 columns. 75 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.96 seconds cpu time 1.95 seconds 76 77 proc sql noprint; 78 create table want2 as 79 select datepart(Date2)-datepart(Date1) format=comma10. AS TAT 80 from have 81 ; NOTE: Table WORK.WANT2 created, with 10000000 rows and 1 columns. 82 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.23 seconds cpu time 1.14 seconds 83 84 proc sql noprint; 85 create table want3 as 86 select (Date2-Date1)/86400 format=comma10. AS TAT 87 from have 88 ; NOTE: Table WORK.WANT3 created, with 10000000 rows and 1 columns. 89 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.88 seconds cpu time 0.87 seconds 90 91 proc sql noprint; 92 create table want4 as 93 select intck('DTDAY', Date1, Date2) format=comma10. AS TAT 94 from have 95 ; NOTE: Table WORK.WANT4 created, with 10000000 rows and 1 columns. 96 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.11 seconds cpu time 1.79 seconds
Art, CEO, AnalystFinder.com
06-22-2017 10:53 AM
Yes, will consider the performance.
I have a another wired issue, related.
format Mnth mmyys10.;
Create Table TestA as
Print data= TestA;
But as soon as I Join table test1 with any other tables,
result would be as this : 01/1753
WHy this happend, how stranged ?
06-22-2017 11:00 AM
We would likely need to see data from both sets including existing formats and such.
My initial guess would be mixing opertions between a date value, MNTH, and a datetime value somewhere.
06-22-2017 11:14 AM
Since you used proc sql:, rather than proc sql;, my guess is that your code simply didn't run. The following worked for me:
data test1; Mnth='19JUN2017:00:00:00.000'dt; run; data Work.test1; set test1; format Mnth mmyys10.; Mnth=datepart(Mnth); run; PROC SQL; Create Table TestA as Select Mnth from test1 ; QUIT; proc sql; create table want as select * from sashelp.class, TestA ; quit;
Art, CEO, AnalystFinder.com