topic Re: How to import excel values correctly into SAS dataset ? in SAS Programming

How to import excel values correctly into SAS dataset ?

wtien196838 — Thu, 07 Apr 2016 22:55:19 GMT

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.

William

Re: How to import excel values correctly into SAS dataset ?

ballardw — Thu, 07 Apr 2016 23:08:48 GMT

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.

Re: How to import excel values correctly into SAS dataset ?

wtien196838 — Fri, 08 Apr 2016 02:30:05 GMT

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 ?

Re: How to import excel values correctly into SAS dataset ?

Reeza — Fri, 08 Apr 2016 03:41:28 GMT

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.

Re: How to import excel values correctly into SAS dataset ?

Kurt_Bremser — Fri, 08 Apr 2016 06:10:39 GMT

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.

Re: How to import excel values correctly into SAS dataset ?

wtien196838 — Fri, 08 Apr 2016 13:30:42 GMT

/* Open Excel file */

PROC FORMAT;
INVALUE TPERC
'N/A' = .
'-' = 999
'#VALUE!' = 999
'#DIV/0!' = 999
other =[17.10]
;

Filename inputf DDE "excel|tabname r10c5:r20c6 notab lrecl=32000; /* sample 2 columns */

Data test ;
infile inputf dlm='09'x DSD Missover PAD;
informat
description $100
quarter_per TPERC
;
run;
---------------------------------------
Excel report:
Location Qtr_percent
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.

Re: How to import excel values correctly into SAS dataset ?

Reeza — Fri, 08 Apr 2016 14:29:24 GMT

Is that your full code? Where's the input statement?

Re: How to import excel values correctly into SAS dataset ?

wtien196838 — Fri, 08 Apr 2016 14:45:59 GMT

sorry the code :

informat
description $100
quarter_per TPERC
;

should be read

input
description $100
quarter_per TPERC
;

Re: How to import excel values correctly into SAS dataset ?

Reeza — Fri, 08 Apr 2016 14:56:38 GMT

So you don't have an informat specified for your numeric variable. Try using best32. and seeing your results. As I indicated earlier...

informat
description $100
quarter_per TPERC best32.
;

Re: How to import excel values correctly into SAS dataset ?

wtien196838 — Fri, 08 Apr 2016 15:16:10 GMT

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.

Re: How to import excel values correctly into SAS dataset ?

FreelanceReinh — Fri, 08 Apr 2016 19:38:16 GMT

Hello William,

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.

Re: How to import excel values correctly into SAS dataset ?

Reeza — Fri, 08 Apr 2016 20:08:57 GMT

@wtien196838 Just to clarify, when you ran with Best32 informat it did not work?