When SAS Add-In for Microsoft Office was first created over a decade ago, SAS programmers were told to check their skills at the door. This new product was for non-programmers only. SAS programmers were invited to contribute to the experience by packaging their code in SAS stored processes, which end users would then run using point-and-click menus. But there was no way to write and run your SAS programs directly in Microsoft Excel or Word or PowerPoint.
This was a comfort to many SAS administrators, who wanted to provide SAS analytics to their end users but didn't want them to have to learn to program. Or perhaps to even allow them to program. But, times have changed. Citizen data scientists have been practicing their coding skills, and now they want to mix it up in Microsoft Office. In response to this demand, SAS R&D has added the SAS programming environment -- the parts that make sense, anyway -- into SAS Add-In for Microsoft Office. You can write programs, run them, and drop the results into any part of your Microsoft Office document.
Here's a short screencast of how it works in Microsoft Excel:
If you want to learn more and see a narrated demo from the principal developer, watch this interview with Tim Beese from SAS R&D. Tim shows the coding feature along with several other cool enhancements to SAS Add-In for Microsoft Office. As Tim explains, SAS administrators still have the final say when it comes to allowing Excel users to let loose with SAS code -- they can enable the feature by role, and so grant this as a privilege at their discretion.
The first few minutes of this video shows some impressive integration with SAS Visual Analytics and Microsoft Excel. The SAS programming demo begins around the 5-minute mark.
11 Comments
Smart move, I like it already ! This is some kind of "official return" since there was back in 9.1 a custom task for the then current SAS Add-in which opened the Enhanced Editor inside Excel. It provides a nice workaround for enabling "advanced" data queries based on several tables with joins instead of basic single-table queries (the single table might be a view or Info Map statically joining tables, of course). Btw, thanks for the video, these "tech talk" series is really well made & easy to follow as take away. Pictures are more talkative than mere words on print.
Glad you like it, Ronan! I created that custom task you reference, and we used it internally for lots of testing. Sometimes SAS Tech Support would supply it for special needs. I'm glad that we now have official support for this important capability!
I see, another secret weapon from the Q branch going public ^^. Thanks and well done,this is definitively useful.
This has the potential to solve some platform issues and business conundrums we have with clients in the Agency/PR space. As Director of Product Management and Research there are specific business issues that can be solved by better integration between complicated SAS Code and the use of Microsoft Excel in smaller agencies. I will be reaching out to better understand what restrictions there will be and they can be overcome. I am actually local (Raleigh) , will there be any upcoming showcases on the SAS campus like there were when Visual Analytics was released a few years ago ?
Hi Eugene, I'm not aware of any upcoming events, but you can probably reach out via this page. If you visit the page you should be able to start a chat with one of our reps, and I'm sure a demo could be arranged.
How come I never heard this before? Definitely not something we would used in regulated environment. At least, none of the companies I worked for have this add-on feature available.
Hi Chris,
Really interesting!!
I want to know if its posible every kind of sentence...for example a libname sentence. I want to execute a libname to see a table that I have created in a library that I can't see when I open Excel and go to Add-in.
Another question: from the Excel Add-in I can execute SAS E.G. tasks like graph or analysis tasks, but I want to know if I can execute data manipulation tasks.
Last question: I am using a SAS dataset from Excel if I change the data in Excel, is it possible to send this changes to the original SAS dataset.
Thanks in advance!!
To answer you questions:
- Yes, if you assign a libname by code then it should be available immediately in the SAS interface to Excel.
- Data manipulation tasks? Yes, many of those tasks are available under the SAS tasks menus. The SAS Add-In does not have the full-blown Query Builder like EG, but many of the other tasks (like Rank, Standardize, Transpose, etc.) are available.
- You can update SAS data from Excel. When viewing a SAS table, you'll see a "Begin Edit" button on the top of the screen (well, it will look different in Spanish). Click it to enter "edit mode" -- then click again to commit the changes and write them back to SAS.
Thanks for your help Chris!!
What is the earliest version of the SAS Add-In for Microsoft Office (SASMO) that has the feature for SAS programming? I would like to use SASMO in Excel to access a SAS dataset that is refreshed daily. I would like to automatically refresh the Excel pivot table and pivot chart associated with the dataset (including making sure all the data gets included as the number of records varies with each refresh) when the Excel is opened. I would need to use the SAS programming feature in SASMO to specify the libname of the SAS dataset. When I do all this, would it be possible for other people to open the document and be able to view the data the same as me i.e. have the libname specified and have the automatic refresh of the pivot table and chart to include all data? The alternative is an automatic refresh of the Excel data worksheet each day on SAS Grid and send out the file as an email attachment reminding people to refresh the pivot table and chart and ensure they include all data in the pivot table and pivot chart. Thanks for any guidance and pointers on the best way to do this.
This feature was added in v7.13 (Nov 2016). And your idea for the auto-refresh works, as long as other users also have the SAS Add-In for Microsoft Office installed. If that's not the case, then you would need to automate the refresh using AMO scripting or using the other scheme you suggested.