topic Re: Calculate Days different in SAS Programming

Calculate Days different

JHE — Thu, 22 Jun 2017 13:31:03 GMT

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

Re: Calculate Days different

art297 — Thu, 22 Jun 2017 13:34:09 GMT

Those are datetime variables. Try:

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

Art, CEO, AnalystFinder.com

Re: Calculate Days different

PeterClemmensen — Thu, 22 Jun 2017 13:35:56 GMT

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;

Re: Calculate Days different

JHE — Thu, 22 Jun 2017 13:41:14 GMT

Thank you it works.

Re: Calculate Days different

JHE — Thu, 22 Jun 2017 13:42:12 GMT

Thank you , it works.

Re: Calculate Days different

RW9 — Thu, 22 Jun 2017 13:50:10 GMT

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.

Re: Calculate Days different

ballardw — Thu, 22 Jun 2017 14:21:31 GMT

And yet another:

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

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

Re: Calculate Days different

art297 — Thu, 22 Jun 2017 14:42:42 GMT

@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

Re: Calculate Days different

JHE — Thu, 22 Jun 2017 14:53:03 GMT

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 ?

Re: Calculate Days different

ballardw — Thu, 22 Jun 2017 15:00:08 GMT

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.

Re: Calculate Days different

art297 — Thu, 22 Jun 2017 15:14:11 GMT

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