BookmarkSubscribeRSS Feed
Hi,
 
I have a problem where SAS and Excel totals differ
 
Weighted_avg_term_date in EXCEL
Term_date * Total_Balance = X
02/04/2028 * 7582557.23 =  355206924974.97

Weighted_avg_term_date in SAS
Term_date * Total_Balance = Y
   02/04/2028 * 7582557.23 =  189027600722.29
 
Why don't they match when it's the same calculation, now im not sure what's right, SAS or Excel?
 
Term_Date is a Date variable.
9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Dates are stored differently in SAS and Excel.  SAS dates are number of days since January 1, 1960.  Excel has a 1900 (I think from memory) start date, hence why you have different results.  The real question here is why you are multiplying a Date - i.e. not a time period or number of days etc. - but a numeric.  I think you have your formula wrong there, maybe you need number of days * value or something.

 

E.g. in SAS 02/04/2008 is mmddyy8., = 21949.

This was the calculation they felt was correct. All I have been tasked with is replicating the numbers within SAS.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

You will need to point out the incorrectness to "them" then.  

Assuming the date is 04Feb2028:

SAS:

21949 * 7582557.23 

 

Excel:

46787 * 7582557.23

 

This is why you get the different results, the base interpretation of what a date is differs between the two systems, and is also the prime reason why this calculation makes no sense whatsoever.  If you move it to another system, then the number will change again.  The calculation needs to be based on some fixed data, not an adhoc representation of how a system calculates dates.

Nope but that isn't for me to get invloved with, nothing to do with me! All I have been asked to do is replicate what they have calculated within excel whether or not I agree with it.

Kurt_Bremser
Super User

First of all, this is so utterly, incredibly stupid that my head hurts just thinking about it.

 

I just tested this "calculation" with Excel 2010, and - lo and behold! - I get a different result already:

04.02.2028 7582557,23 354765105120,01
02.04.2028 7582557,23 355204893439,35

Note that I tested your date as DDMMYY or MMDDYY

Are you sure that you are showing the complete Excel calculation in your initial post?

 

The same result is achieved with LibreOffice.

 

Now, if we take care of the date offset inherent in Excel:

02.04.2028 01.01.1900 7582557,23 355197310882,12

(subtracting column B from A and using the result in the multiplication)

and do the same in SAS:

data _null_;
val1 = ('02apr2028'd - '31dec1899'd) * 7582557.23;
put val1= 20.2;
run;

we get this:

24         data _null_;
25         val1 = ('02apr2028'd - '31dec1899'd) * 7582557.23;
26         put val1= 20.2;
27         run;

val1=355197310882.12

Tadaa!

(Note that one has to use 31dec1899 because of a serious date calculation bug in Excel, which thinks that 1900 was a leap year)

ballardw
Super User

@Kurt_Bremser wrote:

 

(Note that one has to use 31dec1899 because of a serious date calculation bug in Excel, which thinks that 1900 was a leap year)


AND Excel thinks 0 Jan 1900 is a valid date.

snoopy369
Barite | Level 11

I hope you at least bring up the issue here (that this doesn't really make sense). If you worked for me and didn't, I'd consider it a major negative in your review. Of course it may well be for someone else to say that it does make sense, but that's up to them once you point it out.  Simply hiding behind 'it's not my job' is not a sign of a good employee.

Of course if they're evaluating the change in (whatever) since 1/1/1900, then this is fine. But that's an important detail, don't you think?  And if that's what they're doing, then Kurt's answer is the correct one.  In fact you might ask whether they want the actual change since 1/1/1900, or the Excel-incorrect value, which will be slightly different - again as Kurt points out, Excel incorrectly considers 2/29/1900 a valid date, so it counts one more day in the period 1/1/1900->today than it should.  

 

It may well be that replicating the bug is desired - that replicability is the key here and not accuracy - but I'd definitely want that documented as a specific requirement (the 2/29/1900 day being included) so that in 5 years when my successor asks why we're subtracting 12/31/1899 the documentation explains why - and who made the decision.

Vince_SAS
Rhodochrosite | Level 12

For a humorous but informative view of Excel dates:

 

My First BillG Review

https://www.joelonsoftware.com/2006/06/16/my-first-billg-review

 

Vince DelGobbo

SAS R&D

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1338 views
  • 4 likes
  • 6 in conversation