IMPORT EXCEL FILE; numbers do not appear as numbers

[Shoref_Rahman]

Hi,

I am trying to import an excel file into SAS EG, every time I do this however the numbers appear different to what they are in the spread sheet. I am also struggling with date formats

here is an exmaple of the excel file I am changing, the first column is a DOB, middle column is a Idenfier number and the last column is date registered;

3091991	1400000000033	15012018
17031977	1400000000044	15012018
3101982	1400000000055	16012018

After importing to SAS the columns and rows appear as below, the date columns do not register as a dates and the identifier number becomes what can be seen below.

3091991	1.4E+12	15012018
17031977	1.4E+12	15012018
3101982	1.4E+12	16012018

What I basically want to do is import the excel file with the correct formats for each column. dates as dates and the indentifier number as full number.

Can you help this please?

S

[RW9 · Accepted solution]

This is because Excel is a bad data format, and I assume you are using proc import which is a guessing procedure.  Combine the two and what you get out will be garbage half the time.

From the Excel data create a CSV file - plain text, delimited, cross platform, easy to use = much better than Excel.

Write a datastep import program (you can take the shell from the log after a proc import has run to save time), and apply in the datastep formats, informats, lengths etc.  Eg:

data want;
  infile ".../myfile.csv";
  length ...;
  format ...;
  informat ...;
  input ...;
run;

[Tom]

Your example looks fine to me.  It is just that the numbers in your second column are too large to display using the default BEST12. format.  Just use a different format to display them.

But they also look like they might really be identifiers instead of numbers that you are going to use in calculations. In that case if you convert them to strings in Excel then SAS will import them as character strings instead.

Your third column is NOT a date in Excel, why would you expect it to appears as a date in SAS?