Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SAS Date/Balance Sum

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 05:45 AM

Hi,

I have a problem where SAS and Excel totals differ

Weighted_avg_term_date in EXCEL

Term_date * Total_Balance = X

Term_date * Total_Balance = X

02/04/2028 * 7582557.23 = 355206924974.97

Weighted_avg_term_date in SAS

Term_date * Total_Balance = Y

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 05:50 AM - edited 05-31-2017 05:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 05:54 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 05:59 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 06:13 AM

You are multiplying a DATE with an AMOUNT? You're joking, right?

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 06:15 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 07:04 AM

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)

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 10:57 AM

KurtBremser 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 11:11 AM - edited 05-31-2017 11:14 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-31-2017 01:30 PM

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