DATA Step, Macro, Functions and more

Calculate Days different

Reply
Frequent Contributor
Frequent Contributor
Posts: 98

Calculate Days different

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

 

PROC Star
Posts: 7,484

Re: Calculate Days different

Those are datetime variables. Try:

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

Art, CEO, AnalystFinder.com

Frequent Contributor
Frequent Contributor
Posts: 98

Re: Calculate Days different

Thank you it works.

PROC Star
Posts: 763

Re: Calculate Days different

[ Edited ]

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;
Frequent Contributor
Frequent Contributor
Posts: 98

Re: Calculate Days different

Thank you , it works.

Super User
Super User
Posts: 7,977

Re: Calculate Days different

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.

Super User
Posts: 11,343

Re: Calculate Days different

And yet another:

 

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

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

 

PROC Star
Posts: 7,484

Re: Calculate Days different

@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

 

Frequent Contributor
Frequent Contributor
Posts: 98

Re: Calculate Days different

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 ?

 

 

 

 

Super User
Posts: 11,343

Re: Calculate Days different

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.

 

 

PROC Star
Posts: 7,484

Re: Calculate Days different

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

Ask a Question
Discussion stats
  • 10 replies
  • 196 views
  • 4 likes
  • 5 in conversation