04-07-2016 06:55 PM
Dear SAS user community,
My user passed me an Excel report which has columns of data.
For example, one cell of a column has 82.5% (real value is 82.4678901235%).
I extract from database to get the value of that cell which is exactly 82.4678901235.
When I use sas proc compare, the result is mismatched due to 82.5 vs 82.4678901235.
Would you show me a solution to this issue ?
PS: if I changed all values of excel report format to real values, the proc compare created a matched result report.
Thanks in advance.
04-07-2016 07:08 PM
I'm not sure I quite follow. Did you import the data into SAS from Excel or some other database, or both?
Are you saying the value of 82.4678901235 did or did not appear in the SAS data set? And from which source(see previous question).
If you imported data into SAS from Excel describe what method you used. Ditto if reading from another database.
If is possible that the displayed value from Excel is what SAS received depending upon the method used.
04-07-2016 10:30 PM
I read a range of cells in each excel worksheet. I use DDE to read the value of each cell. Use informat to code each variable.
I also extract data from same user oracle database to compare.
In short, user data and my data are the same. Only user present data in excel format is differ to the real value (rounding issue).
Is what you see (values in the excel cell) if what you get (rounding number) in reading excel cells into sas datasset ?
Example: real value is 82.4678901235, excel format 82.5 and sas read in 82.5000000000 (informat 13.10).
My question is how can I read in from excel the real value so that the proc compare user data (read from excel) and my data (read from oracle database) MATCHING ?
04-07-2016 11:41 PM
Please post the DDE code you used to read in the data.
Did you correctly specify your informat? In this case you may want to try BEST32. as an informat to read the file correctly.
04-08-2016 02:10 AM
This is because Excel is not a sensible transfer medium and the internal Excel file format is not a suitable format for data transfer.
If someone wants to send you test data from a database query, they should unload that into a csv or fixed column file and send you that.
As long as you use Excel as the medium, you will have a constant PITA.
04-08-2016 09:30 AM
/* Open Excel file */
'N/A' = .
'-' = 999
'#VALUE!' = 999
'#DIV/0!' = 999
Filename inputf DDE "excel|tabname r10c5:r20c6 notab lrecl=32000; /* sample 2 columns */
Data test ;
infile inputf dlm='09'x DSD Missover PAD;
East region 82.5 % (excel format decimal = 1) or 82.4678901235% (format decimal=10)
I need to verify the correctness of this report. SAS pull from database the real value
82.4678901235 but sas read in the excel value 82.5000000000.
AS a result, proc compare showed mismatch on the cell.
I CAN NOT modify the user report, change all numbers format to decimal=10. If this is the case,
proc compare showed MATCHING.
04-08-2016 10:56 AM
So you don't have an informat specified for your numeric variable. Try using best32. and seeing your results. As I indicated earlier...
quarter_per TPERC best32.
04-08-2016 11:15 AM - edited 04-08-2016 11:16 AM
Proc format assigned the value 17.10 (in TPERC) when sas read in the excel cell value.
As a result, the value sas read in is 82.5000000000 which is corresponding to the display cell value 82.5.
While the actual value is 82.4678901235 if we change the format on excel (decimal=10).
The above code is a part of the full program. The program contains sql to connect database to extract data.
Proc compare to compare two sas datasets which are read in the data from excel report and from database.
04-08-2016 03:38 PM
I think, the problem is that generally formatted values are sent through the DDE connection (SAS-formatted values to Excel and Excel-formatted values to SAS). So, the solution should be to let SAS instruct Excel (via DDE) to show all decimals in the relevant cells and only then read the numbers. Please refer to section 5 ("Changing Cell Formats") on page 4 of this paper for a short data _null_ step to accomplish this.