Why are some of my SAS fields blank or missing '.' when imported from an Excel spreadsheet?

If an Excel column is formatted as numeric and there is a character in one of its cells that is formatted as text, the character field will be imported into SAS as '.'.

This is because the API that SAS uses to read the Excel file determines the column type by scanning the first eight rows. It will use the most common data type it encounters in these 8 rows.

Since SAS does not allow mixed data, the Excel columns that contain the mixed data will need to be re-defined as a text field.

To do this, open Excel and highlight the column to change. NOTE: Excel left aligns text fields and right aligns numeric fields, so you must highlight the entire column to redefine the Excel column header to text and you can only highlight one column at a time. Then select, DATA | TEXT TO COLUMNS | select NEXT until you STEP 3 of 3 | under COLUMN DATA FORMAT select TEXT | select FINISH. Now the entire Excel column should be left aligned and the Excel header modified for this column to text.

Back