BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nupur20
Calcite | Level 5

I have two columns in my database, one is start date and other is end data. These two columns are formatted like:

Start date                                         End Date

01May2010:00:00:00                        02June2010:00:00:00

I want to subtract the dates to find out number of days in between. I have tried formatting date and substring function but nothing worked. Whatever function I try, my date gets changed to weird numbers like 15000786 etc.

I would really appreciate your time and help regarding this.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
robby_beum
Quartz | Level 8

Try this:

data P01.output_ds;
   format start end date9.;
   set P01.input_ds;

   start=datepart(startdate);
   end=datepart(enddate);

   daysdiff=intck('day', start, end);
run;

View solution in original post

6 REPLIES 6
robby_beum
Quartz | Level 8

Try this:

data P01.output_ds;
   format start end date9.;
   set P01.input_ds;

   start=datepart(startdate);
   end=datepart(enddate);

   daysdiff=intck('day', start, end);
run;

art297
Opal | Level 21

I think that Robby has already provided the answer you need.  However, as for the daysdiff part, you could also simply use:

daysdiff=end-start;

Howles
Quartz | Level 8

If the DATEPART extractions are needed for other purposes, go ahead with them. However, the day difference can be calculated directly from the given datetimes:

  

daysdiff=intck('dtday', startdate, enddate) ;

robby_beum wrote:

Try this:

data P01.output_ds;
   format start end date9.;
   set P01.input_ds;

   start=datepart(startdate);
   end=datepart(enddate);

   daysdiff=intck('day', start, end);
run;

DF
Fluorite | Level 6 DF
Fluorite | Level 6

It's worth noting the reason for your issue is that your values are datetimes, not dates.  Unlike other platforms SAS treats these differently and they cannot be used together (directly).

When you subtract the datetimes, the difference you see is the number of seconds between the two.  When you do the same with dates you get the number of days.

In its numerical form, SAS counts dates as the number of days since 1st Jan 1960, and datetime as the number of seconds since midnight of 1st Jan 1960.  You can see this using the following code

data test;

format date date9.;

format datetime datetime20.;

date = 1;

datetime = 1;

diff = '02JUN2011:00:00:00'dt - '01MAY2011:00:00:00'dt;

run;

The DatePart and TimePart functions, convert a datetime into its respective components.

Hope that helps.

Edit: I've added the difference calculation using your sample datetimes.  The result is 2,764,800 which is the equivalent of 32 days but expressed in seconds (32*24*60*60).

Message was edited by: DF

art297
Opal | Level 21

Not meaning to strech this thread to absurdity, but using Iho's example, if you do the math for SAS you can trim approximately 30% off of your processing time. i.e., the following code

  diff=(End_date-Start_date)/(86400);

will produce the same result and use up about 30% less cpu time.

I only responded, again, because I noticed one of your example dates: 02June2010:00:00:00

Is that REALLY the way your data looks or was 'June' really 'Jun'?

Nupur20
Calcite | Level 5

Thank You so much guyz!! You are awesome.. It worked Smiley Happy

I initially spent hours figuring out this..

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1092 views
  • 7 likes
  • 5 in conversation