Import data task not working on excel mixed field (numeric/char)

[tommymene86]

Hi,

I'm having some troubles importing a simple excel interval with SAS EG (7.12).

In the excel (see attachment) there is a column with both numeric (with lots of decimals) and character values.

I want just the numeric values but, no matter what I set in the field attributes, there is no informat or format that imports correctly the values (the result is an all missing values column).

I found out that if you manually cancel all the character values in the excel, SAS imports them correctly.

Is there any way to set the import task properly and avoid the manual cancellation?

Thanks for your help

T

[ballardw]

---

@tommymene86 wrote:

Hi,

I'm having some troubles importing a simple excel interval with SAS EG (7.12).

In the excel (see attachment) there is a column with both numeric (with lots of decimals) and character values.

I want just the numeric values but, no matter what I set in the field attributes, there is no informat or format that imports correctly the values (the result is an all missing values column).

I found out that if you manually cancel all the character values in the excel, SAS imports them correctly.

Is there any way to set the import task properly and avoid the manual cancellation?

Thanks for your help

T

---

Not with simple tasks or wizards. Those tools assume the data is clean such as all values numeric or character. When they encounter mixed data with ANY character value in the rows used to set the properties means that the data must be imported as character because the character values are not numeric.

If you are going to be doing this repeatedly then you have some choices. One would be to tell the data source to stop putting junk in your data file. Another would be to use a different file format such as CSV with agreed upon layout and write a standard DATA step to read that file format. You can write a step that can work around known values such as your n.s. depending on what you want done.

Or possibly use the PC files server and write a data step using that intermediary to avoid creating CSV.

If you scan through the posts on this forum you will find that poor content coupled with Excel files is one of the most common questions. That happens because Excel places NO constraints on the garbage that people can place into the data. So wizards cannot be written to solve all of the possible garbage scenarios. GIGO.

[Tom]

If the column in Excel has mixed numeric cells and character cells then you should import it into SAS as a character variable.

You can then add another step in SAS to convert the character values that look like numbers back into numbers.