topic Re: How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive? in SAS Enterprise Guide

How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive?

atran2 — Thu, 12 Dec 2019 18:53:21 GMT

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.

Re: How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive?

SASKiwi — Thu, 12 Dec 2019 19:06:16 GMT

A better option would be to get your data team to provide a delimited file (CSV), read that into SAS and do all of your calculations in SAS not Excel then get SAS to update SAP via SAS/ACCESS to SAP (don't know if you have this). That way you can fully automate the process with no manual intervention.

I would avoid Excel if at all possible plus manual interventions for data importation and processing as these are just recipes for things to go wrong. With Excel you cannot fully control how data is read.

Re: How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive?

Kurt_Bremser — Thu, 12 Dec 2019 20:25:59 GMT

have the orginal data sent as csv
do all calculations/transformations in SAS
create the csv for SAP in SAS

Re: How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive?

atran2 — Thu, 12 Dec 2019 21:37:19 GMT

Hey, I might be able to get the data as .csv but part of the challenge is there is some components of the process which rely on Bloomberg add-in for Excel, which we can't get that type of functionality elsewhere. As a result, the most "lean" we can get is a single Excel sheet with a bunch of cells pulling Bloomberg data and using that as an input for further calculation.

Re: How can I dump a SAS dataset in EG into an existing .xlsx workbook, located on Windows S: Drive?

SASKiwi — Thu, 12 Dec 2019 22:11:42 GMT

OK, then read the Bloomberg data into SAS also and do your calculations there.

It may also be possible to pull the Bloomberg data directly with SAS if there is a public interface.