Ok, I would then suggest splitting your process into two segments.
1) Loading of data - this would be the extract from Excel of the data and the loading to a data/(warehouse, storage, sets, etc.). You will likely find it a whole lot easier to use a command line converter (Apache Tika maybe) to convert all the Excel files to plain CSV text. Then datasteps can be developed to read in the CSV data. This avoids one of the big drawbacks of Excel in its bad data storage, and avoiding using guessing procedures like proc import to generate garbage (GIGO - Garbage in garbage out). Also avoids any unnecessary post-processing.
2) Processing of the data from your data store.
Other tips:
Avoid using SQL. SQL is known to become resource heavy at times, and because it creates its own internal plan you can't guarantee that two runs with different data will run the same (sorted/unsorted is one which jumps to mind).
Avoid creating lots of datasets (often seen when macro is used), as each read/write if there is even a tiny lag on the network can be blown up to the power of number of loops.
Examine the log closely at each section to see resource intensive tasks and focus re-factoring efforts there as @SASKiwi has posted.
You can shrink your data using a variety of methods - RDBMs style, using informats etc. If you have lots of categories across large data, then create an informat of the categories, then read in the data using the informats. You can then encode the data to be as small as possible - with large text data this can be a large saving.
There is plenty of other things you can look at as well, but the biggest two are going to be importing the data and removing any loops (macro ones).
... View more
