DATA Step, Macro, Functions and more

Using SAS to run excel Macros?

Reply
Occasional Contributor
Posts: 6

Using SAS to run excel Macros?

I am new-ish to the programming side of SAS but am currently on a mission to try and automate my reporting.


One thing I really want to know is it possible to use SAS to run my VBA macro that is living in the excel document?

Currently am on enterprise guide 4.2 but hoping it might be a coding solution that doesnt matter which version we are on.

(I think in the pipeline is for us to get the newer one).

Thanks for your help...

Super User
Posts: 19,772

Re: Using SAS to run excel Macros?

Posted in reply to popples123

Yes.  There are a few ways, though using EG may limit your options.

One is to write a VBScript that opens the excel file and runs the macro, see the System Command add in for EG:

http://blogs.sas.com/content/sasdummy/2013/11/25/11-custom-tasks/

Another is using Base SAS and DDE but many people consider that incredibly old school.

Depending on what your VBA code is doing there may be ways to have SAS do it directly as well.

Occasional Contributor
Posts: 6

Re: Using SAS to run excel Macros?

Great thanks I will have a read through that information and learn about VBScript!! Smiley Happy

There are a few different things I will need to do, such as multiple coping and pasting from an "engine" which is just a bunch of formulas that loops through different groups.

However the one I am working on currently is simpler where I just need to copy a range of data from one worksheet in an excel file and paste values to a different excel file, so this is something I could just code in SAS?

Super User
Posts: 19,772

Re: Using SAS to run excel Macros?

Posted in reply to popples123

Yes, you could just import the data from one excel file and export to the second excel file in the specific range.

It might be worth fully describing your problem and then someone can post the most efficient/best method using SAS and/or a combination of DDE/VBScript.

Super User
Super User
Posts: 7,942

Re: Using SAS to run excel Macros?

Posted in reply to popples123

Hi,

I agree with Reeza, post an example of what, and where you are trying to do this items.  It sounds to me, from your post, that you have lots of things to do within Excel, so my first question would be why not do it all there, avoid the whole doing it via SAS.

Trusted Advisor
Posts: 3,212

Re: Using SAS to run excel Macros?

Posted in reply to popples123

At this moment I am looking at  (A Poor/Rich SAS Users Proc Export - sasCommunity) that Arthur Tabachneck has started in https://communities.sas.com/message/230493#230493.

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,212

Re: Using SAS to run excel Macros?

Posted in reply to popples123

This time  I do not agree with RW9. Excel usage is lacking the traceability auditability and verifying code testing aspects. You can solve those in a SAS environment.

When you have something serious as job to do than avoid Excel as much as possible. For presenting figures it cannot be ignored as too many managers are asking "give me a spreadsheet".

When it is not serious what you are doing, why are you doing that at all.   

---->-- ja karman --<-----
Super User
Super User
Posts: 7,942

Re: Using SAS to run excel Macros?

Whilst I agree with your point there about traceability/verifying code, that's more of the way something is used, not really the tool itself.  I wouldn't personally recommend using Excel for anything, but the undeniable fact is that it is used a lot (banking for instance).  If you want to go the route of SAS then save the data from Excel to delimited then import and work on it in SAS - remove the need to macros.  Otherwise it just adds another layer on.  I suppose it also depends on what this "engine" is, and what is being done also.

Trusted Advisor
Posts: 3,212

Re: Using SAS to run excel Macros?

Ok we are evaluating Excel usage different European Spreadsheet Risks Interest Group - spreadsheet risk management and solutions conference .

For Excel the data and code is not segregated that is fundamental tool issue.  As is the complete freedom on cell-usage spread over different sheets. This cannot be corrected afterwards needing a lot of additional actions for that. With an applicable toolset you can still make a tremendous mess of it. No problem for creating rubbish. At that point we agree.  

---->-- ja karman --<-----
Occasional Contributor
Posts: 6

Re: Using SAS to run excel Macros?

thanks for the comments

interesting discussion around excel, i know a number of people in my team think that providing excel just with the report already created is an advantage because then it is less likely to not work or something to break and yes just provides the data that someone might want for a specific presentation etc. However others in the team think that too much of the process can be hidden in SAS or similar and its harder for someone to step through the process and error check or whatever.

With my specific example I have one spreadsheet where someone makes updates/calculations/decisions about targets, there is a lot of data and calculations in here and I just really need the output of this, I then copy it into another report where weekly data is measured against those targets. This one is pretty simple I think if its possible to just take information from a cell range in a specific sheet within an excel file (i hope) and then push it back out again?!

The other one regarding an engine is to do with forecasting so making calculations based on basically a modelpoint etc which the macro cycles through and then copy and pastes the results to another sheet, its easy to step through the macro if something isn't working correctly or something needs to be changed, so that was the concern about having it in SAS, however I am open to advice/suggestions as I am following what has generally be done in our team and that doesnt necessarily mean its the best way to do it!!

It would be great however if I could run the macro from SAS (while also creating the datasets i need) rather than having to open the excel file and run the marco separately.

Super User
Super User
Posts: 7,942

Re: Using SAS to run excel Macros?

Posted in reply to popples123

Yes, common approach that.  In my field QC/Validation is very important.  Every script we write is either double programmed i.e. two individuals separately programming from a spec, or thorough output review.  Not just once, but each time this is run.  Version control keeps any changes and an audit history, documentation is in place to aid in proving this etc.  So often I see Excel files being sent around where no-one has checked the logic/code/formulas, things changing, etc.  Its really quite shocking.  Excel itself is notorious for "helping" the user by hiding things, numbers stored as text, special characters etc.

TBH you can do most things in most languages, they have their specialites - SAS is great for statsy things, javascript designed for interactive web pages, basic designed to be simple etc.  Its more about documentation, process and validation.  What do you want to do - probably the hardest part to understand, how do I make sure it does what its supposed to - second hardest, make it do what I want - pretty easy really.

Some suggestions have been posted above on how to run a VBA macro - DDE for instance.  You could also put a call to the macro in the OnOpen function of the workbook so it runs whenever it is opened.  I don't use EG so not sure what limitations you will have there.

Trusted Advisor
Posts: 3,212

Re: Using SAS to run excel Macros?

Posted in reply to popples123

RW9 you proved I am wrong (release management Excel) as you described that, that is very nice way to take care for that at you site.
Eguide is not having an easy way back to Excel (no DDE/VBA) you could build something yourself as EGuide custom tasks. When the sas server is run at Windows you do possible some things.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 11 replies
  • 4387 views
  • 1 like
  • 4 in conversation