Edit 16 Jan 2018: I cannot now reproduce the problem described, so the original question is invalid. Sorry. I'll leave a description of my mistake as it might be helpful. Proc import with DBMS=XLSX is importing mixed character and number data in variables correctly. It is reading all the decimal places of the numbers, so the number -0.0621009999999962 is being imported as "-6.2100999999996187E-2". I think the problem arose when I dumped the character rows and converted the numbers to numeric. With the statement new(i)=input(old(i),8.) it was not picking up the final "E-2" in the example above, so converting it to the number -6.21009 The statement new(i)=input(old(i),21.) gives the correct output. Thanks to those who replied to the original question. The data is being imported into SAS to avoid the human errors that can occur in creating CSV from XLSX, so the question meant to ask for solutions apart from altering the input. The only relevant solution in the support page http://support.sas.com/kb/46/472.html is to use DBMS=XLSX. So in summary, as far as I can see, Proc Import with DBMS=XLSX works correctly, and gets numbers right, even when the variables are treated as character, and the numbers are not displayed to all decimal places. Care is required when converting to numeric format. -end of edit- ---------------------------------------- ORIGINAL QUESTION: Proc import XLSX NOT reading numbers correctly Using this code running SAS 9.4 I am importing a file with the variable names in the first row, then 8 rows of character data not needed, then 100 rows of numeric data shown to 2 decimal places. PROC IMPORT DATAFILE=&file DBMS=xlsx REPLACE OUT=SAMPLE ; RANGE="S1MR"; GETNAMES=YES; run; I want to start at row 1 to use the variable names (there are hundreds of columns so I don't want to specify each one). I am then discarding the 8 unneeded rows and converting character to numeric to get numbers I can manipulate. I only need the numeric data rounded to the nearest integer. This generally works fine but the value -0.06 is really -0.0621009999999962 and is being imported as "-6.021", and 0.00 is really 0.00320000 is imported as "3.2" From Ballardw's answer here https://communities.sas.com/t5/SAS-Procedures/Incorrect-values-is-datasets-imported-from-CSV/td-p/173447 I'm guessing it's because the full 16 digit number is either not being accurately stored or read. SAS is determining the columns to be length 15. From this note on character strings being truncated http://support.sas.com/kb/46/472.html it looks there is no way in SAS of controlling length with proc import of XLSX. If I change the format in Excel to display to 16 decimal places I can get the right output as SAS makes the columns length longer. Apart from this workaround, or otherwise altering the input, is there any way of getting SAS to read these small numbers correctly?
... View more
