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!! 🙂
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:
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:
@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!! 🙂
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...
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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.