Import Error on Excel

Reply
New Contributor
Posts: 3

Import Error on Excel


Hi,

I using PROC IMPORT to import excel file to SAS but facing issue.

-Unable to capture exact figure to dataset

Example

In the excel figure 123456789.59 after import to SAS will became 123456789.6

How can I capture exact figure same with in excel. Below was my code using to import

PROC IMPORT OUT= ABCDE_XXXX

            DATAFILE= "D:\XXXX.xls"

            DBMS=xls REPLACE;

     RANGE="Sheet1$.A35:C39";  /**only import few row data**/

     GETNAMES=YES;

RUN;

Thanks

Trusted Advisor
Posts: 3,215

Re: Import Error on Excel

Your figures are not exact by their floating nature.

Thus is hardware related and every tool has to deal with that Excel SAS and many others.

The formats in SAS are the presenting layer, check those have been set according your wishes. What could have happened is that the first 30 rows did not show the way you are desiring the data the guessing types char numeric and other defaults do not look further.

---->-- ja karman --<-----
Super User
Super User
Posts: 7,997

Re: Import Error on Excel

I would also consider not using Excel/proc import as there is a lot of guessing which goes on behind the scenes.  Save the data to CSV, then write a datastep to read in the data exactly as you want it.

data thedata;

     length var1 var2 var3...;

     infile "...\data.csv" dlm="," missover;

     input var1 $ var2 ...;

run;    

Super User
Posts: 11,343

Re: Import Error on Excel

And the difference may just be the format. Use either proc contents or the SAS explorer to look at variable properties. If you see a format like best10. for the variable that may be the difference. Change the format to something like 16.4.

And don't panic if you see additional decimal values as the original Excel sheet may have had format that defaults to 2 decimals.

Ask a Question
Discussion stats
  • 3 replies
  • 221 views
  • 2 likes
  • 4 in conversation