Different means between PROC MEANS and EXCEL

[somebody]

I am not sure what I did wrong but I have different values between using PROC MEANS and excel. Although the difference is not large, it could be troublesome.

proc means data = example noprint; by date portfolio;
    output out = test(drop=_TYPE_ _FREQ_ form_Date) mean()=;
run;
I attached the data and the comparison between the means.

[Kurt_Bremser]

How did you read the csv into SAS? Please post that code, so we can faithfully recreate your dataset.

[somebody]

The data posted is a subset of a larger dataset in SAS. I export this subset to csv so I can provide an example

[Kurt_Bremser]

I opened the csv in Excel and LibreOffice Calc, and used the mean() function in both to calculate the means of the 6 observations for date 200001, portfolio 1. The result matches exactly with the result in SAS. I guess that the other groups will be the same.

How did you calculate the means in Excel?

[PaigeMiller]

---

@somebody wrote:

I am not sure what I did wrong but I have different values between using PROC MEANS and excel. Although the difference is not large, it could be troublesome.

---

Please tell us what mean was calculated by SAS and what mean was calculated by Excel. We need to have some idea of the magnitude of the difference; some differences are round-off error and not worth pursuing.

[somebody]

In the "example_results.xlsx" file, column "excel_MEAN" contains means computed by Excel by using the formula AVERAGE().

Column SAS_MEAN contains means computed by SAS. 

The 2 variables of interest are : r and tvol which are contained in column _NAME_.

[PaigeMiller]

In the "example_results.xlsx" file, column "excel_MEAN" contains means computed by Excel by using the formula AVERAGE().

Some of use refuse to download or open Excel or other Microsoft Office files as they are a security threat.

So please post as text the two answers

[VDD]

Excel does funny stuff to your numbers.  are you sure those results are correct that Excel provides.  Did you use a calculator, to confirm both results?

[Cynthia_sas]

Hi:

  Another consideration is that you are not controlling for MAXDEC in the PROC MEANS code, so there could be rounding differences. Finally, and more to the point, I think is that you have 2 BY variables in your PROC MEANS code. PROC MEANS will treat each BY group as being one isolated group, based on your 2 BY variables. I do not know whether you can replicate that kind of BY group processing with Excel.

  Without data to test, no one can do more than make general suggestions.

Cynthia

[Doc_Duke]

Those difference look like round-off errors.  although both SAS and Excel use double precision FLOATING POINT arithmetic, you can get round-off errors when the numerator N is large.