Importing XLS with a datastep so I can define informats and formats

[sallyjo]

How do I import an xls or xlsx with a datasetep?  PROC IMPORT defined my variables incorrectly and is not importing all values for all variables.  I used the GUI to import the xls and created the datastep correctly.  I need to automate this procedure and don't remember or can I find the syntax for creating a dataset from excel using the Data step, I don't remember the syntax in the INFILE statement.  I know it can be done because othere places I use to work, we did it.

Thanks!

[Tom]

You don't.  Excel is not a good system to use for data.  A dataset needs to be in a rectangular format with each column using a consistent data type. Excel is a spreadsheet program where each cell is totally independent of the other cells.

If your excel file is such a mess then export it to a CSV file and read that text file using SAS. 

[ballardw]

Proc import guesses as to variable type and informats. In the case of Excel files the procedure is limited to examining something like the first 20 rows. So if a column has things that look like numbers for the first 20 rows it will assume the column should be numeric and the cells with other characters become missing.

Save as CSV. Use proc import on that file which will allow using large value of guessing rows to examine before assigning variable types, lengths and informats.

The log will contain the data step used to read the csv. You can copy that to the editor and save. Examine the informats as the procedure still is guessing. Things like identification numbers maybe should be character, especially if you have leading 0 to preserve. Change the Informat to match. Look at the lengths of character variables and see if they look long enough to hold the expected text. Since you are "automating" a process you should know what lengths, types, formats and informats should occur in your data.

You would then be able to direct the output of reading any given CSV to a new file or appending to previous as needed.

Any files that people manually edit before you get them may have problems caused by poor data entry habits though.