Lapis Lazuli | Level 10

Different means between PROC MEANS and EXCEL

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

9 REPLIES 9
Super User

Re: Different means between PROC MEANS and EXCEL

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

Lapis Lazuli | Level 10

Re: Different means between PROC MEANS and EXCEL

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

Super User

Re: Different means between PROC MEANS and EXCEL

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?

Diamond | Level 26

Re: Different means between PROC MEANS and EXCEL

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

--
Paige Miller
Lapis Lazuli | Level 10

Re: Different means between PROC MEANS and EXCEL

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

Diamond | Level 26

Re: Different means between PROC MEANS and EXCEL

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.

--
Paige Miller
Ammonite | Level 13

Re: Different means between PROC MEANS and EXCEL

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?

SAS Super FREQ

Re: Different means between PROC MEANS and EXCEL

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

Rhodochrosite | Level 12

Re: Different means between PROC MEANS and EXCEL

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.

Discussion stats
• 9 replies
• 1608 views
• 1 like
• 6 in conversation