Help using Base SAS procedures

Struggling with splitting up data and time

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Struggling with splitting up data and time

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


Accepted Solutions
Solution
‎08-18-2011 05:33 PM
Contributor
Posts: 72

Struggling with splitting up data and time

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


All Replies
Solution
‎08-18-2011 05:33 PM
Contributor
Posts: 72

Struggling with splitting up data and time

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;

PROC Star
Posts: 7,360

Struggling with splitting up data and time

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;

Regular Contributor
Posts: 184

Struggling with splitting up data and time

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;

Frequent Contributor
Frequent Contributor
Posts: 94

Re: Struggling with splitting up data and time

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

PROC Star
Posts: 7,360

Re: Struggling with splitting up data and time

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'?

Contributor
Posts: 33

Re: Struggling with splitting up data and time

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

I initially spent hours figuring out this..

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 181 views
  • 7 likes
  • 5 in conversation