Reliance on Excel for data interchange is problematic because there are no rules about some of these things.
I would suggest: (assuming these are all supposed to be of the same file structure and similar contents:
1) Save the file as CSV.
2) Use Proc Import or the import wizard to read that file. You can indicate which row as the first row of data values (firstobs=). If your variable names do not occur on line 1 in the data I would modify the file imported so that they are on the first line. Also set the GUESSING rows option to something large like 32000 so that SAS examines more rows to determine lengths of variables and types.
3) The step above will generate a data step with code appearing in the log. COPY that data step back into the Editor an modify as follows:
4) Examine your variable names. Any that you do not like do a search and replace in the editor to replace all occurrences. Examine the informat statements. Character variables will be indicated with something like $24. That says the length of the variable will be 24 characters long. You should know from your data description (you do have one don't you?) what the maximum expected length would be. Make sure that number matches that description. Or make a guess. I would replace 24 with something like 35 to allow for other longer values later.
5) Make sure that variables you expect to be numeric have a numeric informat, likely best8 best12 or best32 from proc import. If not change it.
6) If you should have currency values expect to see a COMMA informat. If the currency has $ it is character and you need to change it. Make the width including decimals at least one or two characters larger than expected values: $123,456,789.12 may be set to comma15.2 I would increase that to comma17.2.
7) SAVE the program from editor.
Rerun it with the changes made.
Check the data. You may have a couple rounds of adjusting things.
When this is working as desired then make sure 1) each of your excel files is saved as CSV, 2) replace the name of the file in the INFILE statement , optionally the name of the data set output. The INFILE should also have the firstobs=3 set.
This method will not care how many rows there are but if the columns change order or number then you will have a lot of work in the future.
Your statement " The report has over 100K records daily.." may indicate other issues if this file is actually a report because reports often have summary lines/columns that may have different layouts.
Since I suspect that these daily files are not manually prepared you might go up the data chain and see if a CSV file could be created to begin with that has a documented layout.
