Import excel data cell with green triangle (error indicator) on the top left corner

[UserAL]

Hi All,

I tried to import an excel dataset to SAS 9.4, and got some blank data if the cell has a green triangle on the top left in excel spreadsheet.  I understand that was a error indicator telling me a mix format of the column.  For a small excel dataset, I can find and fix it in excel before importing to SAS using either IMPORT Wizard or PROC IMPORT.  If I have large dataset and many columns it will be time consuming.  Can anyone help what SAS statement I should use to take care of those problem green triangle?  So far, I used code and data as shown in attached file.  Rows 810-813 of column C (Acuity level) were ok, but row 814 and after were blank after import.  Thanks.

[ballardw]

If you must use Proc Import you are generally better off to save the file as CSV and import that.

One reason, you can use the option GUESSINGROWS with CSV, not any of the xls or xlsx files, to examine more of the data before assigning variable properties like variable type and length. I typically use GUESSINGROWS=MAX.

One thing that happens when using File-Save AS to CSV is that the resulting file will generally apply the cell formats where present so mixes of values my appear as needed an not reason to "fix" the files.

Another is that Proc Import will create data step code to read the file. It will be in the LOG, where it can be copied, edited and saved.

You may want to do that if you have things like account numbers that start with zeroes. Import will typically assign a value like 00312345 as numeric and lose the leading the zeroes. You can modify the code to read the value as character.

Another advantage is if you have multiple XLSX files of the same structure then you can 1) to the Save as to CSV, 2) modify the name of the input file and the output data set and read the data with the same properties.

[UserAL]

Hi Ballardw,

Thanks.  It works.

---AL

[TomKari]

Personally, I've had a number of issues importing Excel worksheets, for the reasons you describe.

My favourite solution is to take any decision-making out of the process; I add a line at the top of my worksheet, and put the string CHAR in every active column. That forces SAS to import the data as character, and I can then manage the conversion to numeric when necessary, with any required editing.

Tom

[UserAL]

Hi Tom,

Your suggestion also works well.  Thank you.

---AL