topic Struggling with splitting up data and time in SAS Procedures

Struggling with splitting up data and time

Nupur20 — Thu, 18 Aug 2011 21:19:43 GMT

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

Struggling with splitting up data and time

robby_beum — Thu, 18 Aug 2011 21:33:16 GMT

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;

Struggling with splitting up data and time

art297 — Thu, 18 Aug 2011 22:10:00 GMT

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;

Struggling with splitting up data and time

Howles — Fri, 19 Aug 2011 00:19:58 GMT

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;

Re: Struggling with splitting up data and time

DF — Fri, 19 Aug 2011 11:52:50 GMT

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

Re: Struggling with splitting up data and time

art297 — Fri, 19 Aug 2011 22:06:24 GMT

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

Re: Struggling with splitting up data and time

Nupur20 — Sat, 20 Aug 2011 01:00:06 GMT

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

I initially spent hours figuring out this..