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.
How did you read the csv into SAS? Please post that code, so we can faithfully recreate your dataset.
The data posted is a subset of a larger dataset in SAS. I export this subset to csv so I can provide an example
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?
@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.
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_.
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
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?
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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: