topic Re: PROC IMPORT EXCEL FILE in New SAS User

PROC IMPORT EXCEL FILE

BalajiBollu — Wed, 13 Mar 2019 10:33:45 GMT

Hi,

I have an excel file in which one of the columns value starts at row 8 (Row 1 to 7 is blank). It has a number (0.345) there. But when I use PROC IMPORT to import that excel file it is reading that column as a Character. How can i ask PROC IMPORT to read it as Numeric?

Re: PROC IMPORT EXCEL FILE

RW9 — Wed, 13 Mar 2019 10:42:20 GMT

Excel is a bad data source (no structure).

Proc import is a guessing procedure, it guesses what your data should be.

Combine the above and you will get garbage 80% of the time.

The best suggestion, as always, is to convert your data to a better format plain text (i.e. open, cross platform) such as CSV (which Excel can save to), XML, Json. Then with that nice datasource, write a datastep import program which reads the plain text file using formats, lengths, informats, which you - the person who knows the data best (with your data import agreement) - supplies so the data is read correctly and repeatable each time.

I suspect however that none of the above is what will happen, so you can either fix it after proc import, try to force it by adding a row into Excel, or some other bad trick to get round the situation without fixing it. But you will find many many topics on here about this very thing and its due to Excel+Proc Import = bad.

Re: PROC IMPORT EXCEL FILE

ballardw — Wed, 13 Mar 2019 15:46:52 GMT

You can use Proc Import with the CSV file. Set a large value for the GUESSINGROWS value (NOT available with Excel files) and you can specify the start of the data using the DATAROW statement (also not available for Excel).

The proc will generate data step code that you can copy from the log, save and modify if any of the results need to set to match your needs. Likely things like account numbers, phone numbers, Zip codes or other identifiers may try to be numeric but likely should be character. That is easy to fix with the data step by changing the informat or setting attributes prior to the Input statement.

I usually take to time to add variable Labels and initial data quality check code to the data step.

Re: PROC IMPORT EXCEL FILE

BalajiBollu — Thu, 14 Mar 2019 06:58:29 GMT

Thank you for your suggestions.