BookmarkSubscribeRSS Feed
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

I ahve two dates:

   Date1=  06JUN2017:00:00:00.000,

    Date=   12JUN2017:00:00:00.000

 

Need calculate days between:

 

intck('DAY', Date1, Date2) format=comma10. AS TAT,

 

No result, it is .

 

Please advise.

 

Thank you

 

10 REPLIES 10
art297
Opal | Level 21

Those are datetime variables. Try:

intck('DAY', datepart(Date1), datepart(Date2)) format=comma10. AS TAT,

Art, CEO, AnalystFinder.com

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

Thank you it works.

PeterClemmensen
Tourmaline | Level 20

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;
JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

Thank you , it works.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

And yet another:

 

dif = intck('dtday',date1,date2);

most of the date intervals will work for datetimes when prefixed with DT.

 

art297
Opal | Level 21

@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

 

JHE
Obsidian | Level 7 JHE
Obsidian | Level 7

Yes, will consider the performance.

 

I have a another wired issue, related.

 

Mnth=19JUN2017:00:00:00.000

 

data Work.test1;

set test1;

format Mnth mmyys10.;

Mnth=datepart(Mnth);

 

PROC SQL:

   Create Table TestA  as

    Select Mnth

   from test1;

QUIT;

 

 Print data= TestA;

    06/2017

 

But as soon as I Join table test1 with any other tables,

 

 Print data=TestA

 result would be as this : 01/1753

 

WHy this happend, how stranged ?

 

 

 

 

ballardw
Super User

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.

 

 

art297
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1172 views
  • 4 likes
  • 5 in conversation