topic Excel import: forcing column to be numeric in absence of MIXED in SAS Procedures

Excel import: forcing column to be numeric in absence of MIXED

js5 — Mon, 13 Aug 2018 12:54:16 GMT

Dear community,

I am having issues importing some excel files using EXCELCS or XLSX engines (only ones available due to bitness differences): as neither XLSX engine nor EXCELCS support MIXED option, if there are any (XLSX) resp. sufficient text values in the first rows (EXCELCS), the entire column gets imported as text. With XLSX engine this additionally leads to dates being saved as internal excel timestamps - number of days since 1 January 1900.

I tried using

dbdsopts="dbsastype=(var_mixed=NUMERIC)"

with EXCELCS engine but this does not work - I am getting the following error:

ERROR: During read: [Microsoft][ODBC-Treiber für Excel]Ungültiger Zeichenwert für Konvertierungsangabe. bei Spaltennummer 3 (var_mixed)

It appears that dbsastype option can force the numeric values to be imported as text but not the other way round. Is this a known limitation? Are there any other options? Unfortunately getting the file as .csv is not possible. Thank you!

Re: Excel import: forcing column to be numeric in absence of MIXED

RW9 — Mon, 13 Aug 2018 13:26:59 GMT

Excel is a really bad format for data. Proc import is a really bad way to read data into SAS (as its a guessing procedure). Add the two together and what you have is GIGO (Garbage In, Garbage Out).

Get the data in a usable format like CSV which is open, plain text, cross platform/bitness.

Write a datastep program to correctly read in the CSV file, setting lengths, informats, etc. as you know the data should be (presumably from your data import agreement).

Re: Excel import: forcing column to be numeric in absence of MIXED

Kurt_Bremser — Mon, 13 Aug 2018 14:22:03 GMT

It's totally easy to get a csv from the Excel crap. Open in a spreadsheet program of your choice (I recommend OpenOffice or LibreOffice) and save as csv. With proper software, this action can even be scripted from the commandline.