Should I use SAS to replace/replicate existing VBA code in Excel or should I use SAS to trigger the

[LeonardoJose]

So we have a process that currently looks like this:

1. Excel file #1 gets dropped in a file folder on our Windows Shared S: Drive network.

2. User copies data from Excel File #1 and pastes it into the "INPUT" sheet in Excel File #2 (macro-enabled).

3. The user runs the macro which calculates various helper columns, then interpolates the data to arrive at a single calculated column.

4. The user copies the calculated outputted column from file #2 and pastes the calculated column into Excel file #3, which runs a final macro that creates a .CSV file and drops it into a specific file location for our SAP ERP system to ingest.

This occurs every day at end of day. My current thoughts are either to re-do the entire process in SAS (longer to develop, but ability to add enhancements in the future to the process if needed) OR alternatively just use SAS to trigger the VBA macros so that we can streamline and remove the manual work of copying and pasting data to various sheets (just get SAS to copy and paste the data to various spreadsheet files and then trigger the macro in the workbook).

What are your guys thoughts?

[ballardw]

Without see actual examples it is pretty hard to be specific. But if the goal is the CSV at the end I would suspect you could read Excel File #1 directly with SAS and then do the calculations and export a CSV with the results.

I basically did that for three files this morning.

The  likely main concern is the actual structure of Excel #1 as there are some very badly designed formats of data in the spreadsheet world and reading them can be problematic. With a data set of a specific format created from the starting data then the manipulations done with your steps 3 and 4 are very likely much more stable in SAS (no copy and/or paste errors). One of the pretty nice things of working with SAS code is such a process is that when the next update to Excel comes out (potentially breaking all sorts of macros ) then the SAS code is likely to run. You'll find processes written in SAS such as SAS 5 from the 80's that the code still runs.

Hopefully someone in your organization actually can describe / understand what the existing macros are doing. Note: literal translation of VBA macros is generally suboptimal as SAS provides lots of manipulation tools that work a bit differently. Some things that may be called "interpolation" may be amenable to regression models (that may only need to be developed once) and scoring new data at specific values for instance.