topic Re: Different means between PROC MEANS and EXCEL in SAS Programming

Different means between PROC MEANS and EXCEL

somebody — Sat, 31 Aug 2019 06:55:47 GMT

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.

Re: Different means between PROC MEANS and EXCEL

Kurt_Bremser — Sat, 31 Aug 2019 07:22:30 GMT

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

Re: Different means between PROC MEANS and EXCEL

PaigeMiller — Sat, 31 Aug 2019 11:53:19 GMT

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

Re: Different means between PROC MEANS and EXCEL

VDD — Sat, 31 Aug 2019 14:02:16 GMT

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?

Re: Different means between PROC MEANS and EXCEL

Cynthia_sas — Sat, 31 Aug 2019 15:37:26 GMT

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

Re: Different means between PROC MEANS and EXCEL

somebody — Sun, 01 Sep 2019 06:55:42 GMT

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

Re: Different means between PROC MEANS and EXCEL

somebody — Sun, 01 Sep 2019 07:05:24 GMT

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

Re: Different means between PROC MEANS and EXCEL

PaigeMiller — Sun, 01 Sep 2019 11:41:42 GMT

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

Re: Different means between PROC MEANS and EXCEL

Doc_Duke — Sun, 01 Sep 2019 19:59:40 GMT

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.

Re: Different means between PROC MEANS and EXCEL

Kurt_Bremser — Tue, 03 Sep 2019 07:51:21 GMT

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?