turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to import excel values correctly into SAS data...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

William

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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 ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

Maxims of Maximally Efficient SAS Programmers

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-08-2016 09:30 AM

/* 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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-08-2016 10:29 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-08-2016 10:45 AM

sorry the code :

informat

description $100

quarter_per TPERC

;

should be read

input

description $100

quarter_per TPERC

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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

informat

description $100**quarter_per TPERC best32.**

;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-08-2016 03:38 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-08-2016 04:08 PM - edited 04-08-2016 04:08 PM

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