08-18-2011 05:19 PM
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
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.
08-18-2011 08:19 PM
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) ;
format start end date9.;
daysdiff=intck('day', start, end);
08-19-2011 07:52 AM
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
format date date9.;
format datetime datetime20.;
date = 1;
datetime = 1;
diff = '02JUN2011:00:00:00'dt - '01MAY2011:00:00:00'dt;
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
08-19-2011 06:06 PM
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
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'?
Need further help from the community? Please ask a new question.