Hi folks, I'm newer to SAS and basically I'm looking to automate some Excel tasks. The current process looks like this: Data team sends us a data extract via .xlsx (they auto-drop it into the same Google Drive shared folder everyday at a specific time) We copy and paste the data from that .xlsx workbook to an "intermediary workbook", which contain various Excel formulae that perform further calculations on the data and structures it in the right way for SAP to easily ingest (next step). We take the output of the intermediary workbook and paste it into the final macro-enabled workbook. This workbook has macros which create a .csv copy of the data we pasted in initially and drop it to a specific filepath location on our Shared Network Drive, which SAP automatically ingests as an end of day process, which updates the respective data in SAP. What I would like to do is: Use SAS to ingest the initial .xlsx data file Replace the "intermediary" workbook by replicating the Excel formulaic calculations in SAS Replace the final macro-enabled workbook by pushing the .csv output file to a specific Windows S: Drive file location. My only challenge right now is that I'm essentially using SAS Enterprise Guide for a task that isn't a super common use-case. The intention however is to reduce human error that comes with copy/paste activities, as well as streamline the process into one comprehensive SAS program with an audit trail of what the program is doing each day rather than leave it up to a spreadsheet tool to do it. What are your guys thoughts as to how feasible this is and how would I get started at a high level to accomplish this? What I figured would be a good first step is to simply grab the .xlsx file from Google Drive, then paste it into the existing "Intermediary Workbook" and then pick up the output and paste it into the "final macro workbook", basically replacing only the human steps. The next step after that would be to try to tackle the actual Excel workbooks themselves and see if we can replicate the functionality of that intermediary workbook.
... View more
