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
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;
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;
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;
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;
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
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'?
Thank You so much guyz!! You are awesome.. It worked
I initially spent hours figuring out this..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.