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.
... View more
