topic Re: SAS/ACCESS via ODBC Engine - Excel in Microsoft Integration with SAS

SAS/ACCESS via ODBC Engine - Excel

deleted_user — Thu, 28 Jun 2007 18:58:54 GMT

Can someone help me with this issue? I'm using the SAS/ACCESS via the ODBC engine. Whenever my data in Excel has combined letters and numbers, SAS outputs missing. The paper written by Edward Heaton, Westat, Paper-119-2007 "Reading Excel Workbooks" doesn't work for the ODBC engine.

Here's a sample of my data in Excel that is being read by SAS:
Excel
1019-053617B
1019-053614A
1019B-053588
1019-053529B
1019-053533B
1019-053548B
1019-053564
1019B-053820B

The output from SAS for each record is "."

Program runs fine, no errors and no warnings. Output of data in Excel is bad data. I'm hoping someone can help with this dilemma.

Thank you in advance!

Re: SAS/ACCESS via ODBC Engine - Excel

deleted_user — Fri, 29 Jun 2007 09:44:15 GMT

I suggest your issue isn't with SAS but with the loose typing of Excel. I suspect these are not the first rows of data in your spreadsheet either. Through a SAS option, SAS reads a given number of rows of data in Excel to try to determine the variable type. If you have missing values, then clearly Excel has convinced SAS that the column contains numeric data.

When SAS then encounters numbers with embedded hyphens or character values, it cannot create numeric values, so it reports the values are missing numeric content.

This is a very common problem and is often circumvented by adding a dummy row that contains character data in the first row, to force the data to be read as character. Then the first row is dropped from the table.

Kind regards

David

Re: SAS/ACCESS via ODBC Engine - Excel

Bill — Fri, 29 Jun 2007 13:31:01 GMT

I ran into a problem today trying to import an Excel 97 spreadsheet into a SAS dataset using PROC IMPORT. SAS insisted one column contained mismatched data types. In fact, it did, but in Excel I had done the Format/Cells - choosing Text as the data type so I expected that SAS would import the column as a character variable.

Alas, 'twas not so simple. Here's what you have to do to -really- ensure an Excel column is classified as text:

To REALLY change the format of an Excel column:
1) hilight the entire column
2) Click Data in menu bar
3) Choose Text to Columns
4) Click Next
5) Click Next
6) Column data format will say General, click Text radio button
7) Click Finish

Now the column is REALLY text and SAS will interpret as such when PROC IMPORT runs.

Re: SAS/ACCESS via ODBC Engine - Excel

deleted_user — Fri, 29 Jun 2007 22:27:35 GMT

Thanks for your responses. I entered dummy lines of data in Excel with 'ABCDEF" in the Excel field then removed the record and it worked fine!