BookmarkSubscribeRSS Feed
somebody
Lapis Lazuli | Level 10

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
somebody
Lapis Lazuli | Level 10

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
Super User

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
Diamond | Level 26

@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
somebody
Lapis Lazuli | Level 10

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
Diamond | Level 26

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

--
Paige Miller
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
SAS Super FREQ

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
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 1608 views
  • 1 like
  • 6 in conversation