topic Re: Excel automation in SAS Enterprise Guide

Excel automation

new_sas_user_4 — Fri, 10 Aug 2018 16:16:24 GMT

Hi Everyone,

I need some help to automate my excel work:

I will explain what I do currently..

SAS CODE(run as a trigger run)

SAS code which generates a table which I export in Excel(lets call it Data Sheet).

This has numbers for Prior month and current month to date(by date) for revenue.

EXCEL WORK:

Excel workbook Report has sheets Sheet1 and Dsiplay1

I copy and paste data from Data Sheet and paste it in Sheet1.

Excel sheet Display1 has formulas which take data from Sheet1. I convert Display1 excel sheet as pdf and send it via email.

Once the data comes in for a day’s revenue, I have to freeze it which means I manually copy and paste the data for that day as a value in my excel(Display1 Sheet)…Numbers for Prior days will change when pulled on future date but we want to see the number as it first appeared on that day…

Eg, when I do a pull on 8th july, I get the below numbers:

01Jul2018	90
02Jul2018	123
03Jul2018	256
04Jul2018	123
05Jul2018	256
06Jul2018	548
07Jul2018	567
08Jul2018	400

Now when I do a pull on 9th July I might get :

01Jul2018	100
02Jul2018	150
03Jul2018	200
04Jul2018	250
05Jul2018	300
06Jul2018	350
07Jul2018	400
08Jul2018	450

But in my report, I do want to see 7th July as it was pulled on 8th (567) not 400 and so forth for the coming days…

My current code runs through a trigger run everyday at 5am , so when I come to office I have the data ready and do the Excel work..What I want to do is to automate the excel work as well so I do not have to work on it everyday..the mail automatically gets out!

If this isn't the right spot for such a question, please direct me to the appropriate forum.

Thanks a lot!! 🙂

Re: Excel automation

Reeza — Fri, 10 Aug 2018 16:44:55 GMT

This is the right place but your question is a bit too vague to offer a full solution. I can say that all of this can be done with SAS since I've done it multiple times. Out of curiousity, is there any reason you couldn't generate the full report in SAS outputted to a PDF report and then email that PDF? That's likely the easiest solution depending on the complexity of your data process/report.

As an interim:

Create an Excel template with the formulas you want. In template, create a named range large enough to hold all of your data. It can be bigger but if it's smaller you'll get errors. Make the template also have an excel macro that will export to a specific location with a fixed or dynamic file name as needed. Sometimes I create a new named range that will contain the file name and location.
Export data to named range. Use the libname method here.
Look into either DDE or VBS, I recommend VBS to run the macro in the workbook to create the PDF
Use SAS to call the VBS and to email as necessary.

I still think redesigning your process in SAS fully is likely to be easier.

Don't know if this has enough details but this is a walkthrough of how I did it:

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhurshed-CoordinateComplexReports-Spring2014.pdf

@new_sas_user_4 wrote:

Hi Everyone,

I need some help to automate my excel work:

I will explain what I do currently..

SAS CODE(run as a trigger run)

SAS code which generates a table which I export in Excel(lets call it Data Sheet).

This has numbers for Prior month and current month to date(by date) for revenue.

EXCEL WORK:

Excel workbook Report has sheets Sheet1 and Dsiplay1

I copy and paste data from Data Sheet and paste it in Sheet1.

Excel sheet Display1 has formulas which take data from Sheet1. I convert Display1 excel sheet as pdf and send it via email.

Once the data comes in for a day’s revenue, I have to freeze it which means I manually copy and paste the data for that day as a value in my excel(Display1 Sheet)…Numbers for Prior days will change when pulled on future date but we want to see the number as it first appeared on that day…

Eg, when I do a pull on 8th july, I get the below numbers:

01Jul2018 90

02Jul2018 123

03Jul2018 256

04Jul2018 123

05Jul2018 256

06Jul2018 548

07Jul2018 567

08Jul2018 400

Now when I do a pull on 9th July I might get :

01Jul2018 100

02Jul2018 150

03Jul2018 200

04Jul2018 250

05Jul2018 300

06Jul2018 350

07Jul2018 400

08Jul2018 450

But in my report, I do want to see 7th July as it was pulled on 8th (567) not 400 and so forth for the coming days…

My current code runs through a trigger run everyday at 5am , so when I come to office I have the data ready and do the Excel work..What I want to do is to automate the excel work as well so I do not have to work on it everyday..the mail automatically gets out!

If this isn't the right spot for such a question, please direct me to the appropriate forum.

Thanks a lot!! 🙂

Re: Excel automation

new_sas_user_4 — Fri, 10 Aug 2018 17:27:04 GMT

Thanks Reeza!!

One of the reasons I couldn't do this is :

There is data (such as revenue target for each day fixed one time at start of the month)

which needs to be a column in the final display ...also a column which has variance(difference) between actuals (what I pull each day) and the target....and a few other columns some of which are fixed data and some dynamic or manually calculated..

and the other reason: I do not know the process to generate the full report in SAS outputted to a PDF report and then email that PDF..

Could you please elaborate on he interim solution you suggested..I didnt understand steps 2,3,4...using libname and thereafter..I do not use excel macros as of now..

also, if there is any link or site to learn how to do it, please direct me there...

I am new to using SAS or excel macros and automation...

Re: Excel automation

Reeza — Fri, 10 Aug 2018 17:41:26 GMT

See page 15 on the PDF linked above for an example of exporting data using the libname method.

The process for any of this is relatively simple though - break it down into small enough steps and then tackle each step independently.

You'll move faster with this type of approach.

The calculations don't sound cumbersome so it may be worth pushing it all to SAS. It seems this is beyond your skill set at the moment so you have 3 options:

1. Learn how to do it - takes time

2. Pay someone else - takes money

3. Find an alternative/mixed approach - suggestions above are that.

You can search any topic on lexjansen.com. For example:

How to email a report:

http://support.sas.com/resources/papers/proceedings17/1300-2017.pdf

The VBS and Excel VB macro code are also in the original PDF linked in my previous response. Maybe I'll write it up someday fully. The slides were for a presentation but without me talking they're less useful 🙂