SAS Add-In for Microsoft Office

12月 122018
 

In her role as Product Manager for SAS Platform Technologies (including the SAS Add-In for Microsoft Office), my colleague Amy Peters hears this question often. With many organizations adopting Microsoft Office 365 -- the "cloud" version of Office -- what does this mean for other processes that integrate with Microsoft Office applications?

Microsoft has used different names for these similar offerings: Office 2016, Office 365, Microsoft 365, Office Online. The bottom line is that most users of a "365" package in the cloud, also have access to the Microsoft Office tools on their Windows desktop. They can use the full version of Excel, PowerPoint, Word, etc., and they also have access to these same tools via a web browser. At SAS, we recently experienced this transition ourselves. Have the Office applications on our desktops vanished? No, they have not. While more of our data is now on the cloud (looking at you, OneDrive), it's not really changing how we work, especially when creating/maintaining content. (Like many organizations, we already had one foot in this world by using Microsoft SharePoint for collaboration.)

Collaboration on the web. Full control on the desktop

Let's look at an example of how I use SAS with Microsoft Office. First, I create a report in SAS Visual Analytics. Then I open Excel on my desktop and use the SAS Add-In for Microsoft Office to embed the shared report into my spreadsheet. Want to see what that looks like in action? Check out this video Tech Talk with SAS developer Tim Beese.

Now suppose that I share this content in Microsoft OneDrive, and my colleague views it in Excel in a web browser. Yep, the content is still there. The difference is that the content is not dynamic like it is on my desktop. So what do you do when you want to edit that spreadsheet displaying in the browser? You select Open in Excel and the document opens on your desktop. Voila! The content is dynamic and you have all the functionality the SAS Add-In for Microsoft Office provides.

How is Microsoft Office 365 changing your workflow?

Today, the expectation of most users working with "Office Online" applications in their browsers is that it's primarily for viewing and basic editing. Will this change? Probably. We're researching how to provide more of the SAS Add-In for Microsoft Office function in a browser app. If you or your colleagues need this browser-based function – you want to do something specific in Excel with your SAS content -- we want to hear from you. And do you have a plan to move completely to browser-based Office apps? Currently you can't create SAS content from a browser-based Office app. If that's a pressing need, we would like to know. For now, we're not hearing of use cases where some form of the desktop app isn't still in the picture.

SAS integration with these everyday productivity tools, like Microsoft Office, is important to us. Don't forget about these SAS programming methods to create and read your Microsoft Office content:

How are you using Microsoft Office 365 with SAS? How do you think this workflow will change for you in the next year or two? Leave a comment -- we would love to hear from you.

The post Does SAS support Microsoft Office 365? appeared first on The SAS Dummy.

5月 312017
 

SAS programs in Excel, finally!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:

SAS programming in AMO

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.

The post Create and run SAS code in SAS Add-In for Microsoft Office appeared first on The SAS Dummy.

5月 312017
 

SAS programs in Excel, finally!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:

SAS programming in AMO

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.

The post Create and run SAS code in SAS Add-In for Microsoft Office appeared first on The SAS Dummy.

10月 142014
 

Hello, 1992 called. They want their DDE Excel automation back.

DDE broken?Perhaps the title of this article is too pessimistic. Of course your SAS programs that use DDE (dynamic data exchange) can still work perfectly, as long as you situate your SAS software and its DDE "partner" (usually Microsoft Excel) to run on the same Windows PC. DDE is still documented and supported in SAS 9.4.

But for the growing number of users who modernize to a centralized SAS environment, the legacy of DDE is a big challenge to bring forward. Your new environment might include the benefits of SAS Grid Computing, SAS Visual Analytics, stored processes, and more manageable security. You might run your SAS programs using SAS Enterprise Guide or even a web browser. But with all of that shiny new tech and its distributed architecture, the simple local arrangement that allows DDE to function...well, it falls apart.

Let's look at how DDE works. This data exchange relies on two consenting Windows processes, both running on a single machine, to communicate with each other using special Windows messages. In SAS programs, this usually takes the form of SAS spawning Microsoft Excel, sending a command to reference a particular cell or range of cells in a sheet, and poking in some values that were computed in SAS. If your SAS session is now running on a remote machine (often a non-Windows machine), then SAS cannot use DDE to talk to your local Microsoft Excel application. As the man says, "what we've got here...is failure to communicate."

For those customers who have a lot invested in DDE (some have hundreds of programs!) and no time/budget to adjust processes away from it, I usually recommend a simple stop-gap approach: maintain a designated machine with SAS for Windows to do your DDE grunt work, even as you move other processes to an enterprise SAS environment.

But going forward, I encourage customers to look at the alternatives supported by new features in SAS that are more in line with today's topology (SAS on a remote server, SAS Enterprise Guide or other local client on the PC). These features can replace many (but not all) of the legacy DDE uses that are out there.

DDE is still supported in Microsoft Excel. We cannot say for how long, as Microsoft has put more emphasis on VBA, .NET, or PowerShell for Excel automation instead. But since DDE works only between two Windows processes (SAS and Excel in this case) on the same machine, it's an outmoded approach that's incompatible with many IT setups.

Some DDE alternatives

The SAS Add-In for Microsoft Office offers the most flexible method for complete control over the format and structure of your spreadsheet. It turns the problem on its head: instead of relying on a batch SAS program to push content into Excel, you simply use Excel to pull your SAS content into the spreadsheet, exactly where and how you want it. You can use SAS stored processes to encapsulate whatever SAS operation you need.

Within the SAS Add-In, you can also use specific cells and ranges as input into stored processes; it's just as flexible to inject your Excel content into SAS.

You can use Office scripting to automate the process (like a batch job), so the process can happen unattended. The consumers of your Excel documents do not need the SAS Add-In for Microsoft Office in order to view the results.

I know that not everyone is lucky enough to have the SAS Add-In. For batch SAS users, ODS tagsets.ExcelXP -- or its SAS 9.4 descendant ODS EXCEL -- can be used to place formatted report content into an Excel spreadsheet. Here's an example use for ODS EXCEL, which works as a "one-way" push from SAS into Excel.

Also, PROC EXPORT now supports adding sheets to existing files, or replacing entire sheets in place. That's flexibility that didn't exist before, when some users turned to DDE to fill the gap.

Aside from these SAS-centric approaches, creative programmer types can use script (VBS, PowerShell, and other) to plug their SAS data results into a spreadsheet as a post-process (which many customers spawn within their SAS programs).

Thinking of DDE as technical debt

Weaning yourself off of DDE is easier said than done, I'll admit. Twenty years ago DDE was a boon for SAS developers who needed to deliver Excel content to picky constituents. I played my part: I wrote the SAS Companion for Microsoft Windows (Release 6.11!) and crafted some of the examples that still exist in the documentation.

However, I now consider DDE programs to be a form of "technical debt" that organizations will have to pay off sooner or later.

If your "DDE bill" hasn't come due yet, you're fortunate. But if you're planning to write another SAS program that relies on DDE, consider the future generations. Today's kids are writing SAS programs in iPads and web browsers, and DDE is about as hip as a rotary-dial phone. (Yes, it still works, but it does make your fingers tired.)

tags: DDE, dynamic data exchange, excel, SAS Add-In for Microsoft Office, SAS Enterprise Guide
1月 082013
 

I've bragged about how easy it is to install custom tasks for SAS Enterprise Guide. It's simple: you copy the .NET assembly (usually a single DLL file) into one of the designated folders in your SAS Enterprise Guide installation, and your task just shows up in the menu. Since I have made several custom tasks available for download from this blog, it's possible for users to spend just a few minutes to acquire capabilities that they didn't have before. (By the way, this also applies to the SAS Add-In for Microsoft Office.)

This can trigger anxiety for some SAS administrators. A custom task, despite it's usefulness, might work against the policies of those who maintain a SAS environment. For example, the Copy Files task makes it very easy to move files between your SAS server and your local PC. Some organizations don't want server-based files to leave their controlled server environment, so this type of custom task is a thorn in their side.

Fortunately, SAS administrators can control access to these capabilities by defining roles in SAS metadata, and then associating those roles with users or groups of users. Each role has a list of capabilities that are permitted. If a user's role doesn't permit a certain capability, then that feature simply doesn't appear in the SAS Enterprise Guide user interface.

Custom tasks are a special case for administration. A custom task is a new capability, and so by definition it's not known to SAS metadata. If it's not known, how can you control it? You accomplish it by following these general steps:

  1. First, examine the capability named Access Unregistered Custom Tasks. Do you want your users to be able to add their own tasks? Even if you allow this, you can still "disallow" certain custom tasks if you register them in metadata.
  2. Next, register the custom tasks that you want to control into SAS metadata. SAS Enterprise Guide provides a tool to do this. (It's not in SAS Management Console.)
  3. With the task now listed in SAS metadata, use SAS Management Console to control which roles (and therefore, which users) have the permission to run the task.

    In the remainder of this post, we'll explore these steps...in pictures.

    In SAS Enterprise Guide, select Tools->Add-In Manager to register the task on one machine

    It's important to use Add-In Manager (not the easier "drop-in" deployment) for this step, as it updates a version of the local SAS Enterprise Guide task list so that it contains information about the task.

    In SAS Enterprise Guide, select Tools->SAS Enterprise Guide Explorer and use the Task Import Wizard

    SAS Enterprise Guide Explorer provides this special tool that can examine the local task list (which now contains your custom task) and identify the "new" tasks that are not yet part of the SAS Metadata Server. This tool allows you to add those new tasks into the metadata-controlled list.

    In SAS Management Console, edit role to disallow unregistered custom tasks

    This step is optional -- if you don't want your end users to add any custom tasks on their own. You can restrict access to any custom tasks that you add to SAS metadata.

    In SAS Management Console, edit role to allow desired custom tasks for appropriate groups

    The new tasks show up under an "Imported Tasks" group. For any given role you can decide whether the task is allowed.

    Result: End user sees "restricted" functions

    Once the settings are in effect, the end user will see that his/her functions are restricted -- as indicated in the SAS Enterprise Guide status area on the bottom of the window.


    The user can click the "Functions" link to see the full list of features that are available in the product, along with an indication as to which are disallowed for the user. The user can use this information to "petition" the SAS administrator for access to additional capabilities, if desired. (Ah, the power of a SAS administrator!)

    tags: SAS Add-In for Microsoft Office, sas administration, SAS custom tasks, SAS Enterprise Guide
    4月 042012
     

    In September 2010, I questioned whether you should care about native 64-bit client applications (or the lack thereof). At the time, SAS did not have a 64-bit version of SAS Enterprise Guide or SAS Add-In for Microsoft Office. A skeptical reader might assume that I was just trying to make excuses for a gap in our offering.

    With the 5.1 releases, SAS Enterprise Guide and the SAS Add-In for Microsoft Office now offer native 64-bit versions (in addition to the traditional 32-bit versions). Recently a customer wrote to me and asked: now that we have these versions to offer, have I revised my thinking on this topic?

    My answer: No, I have not. Except in a few unusual circumstances, the 64-bit versions do not offer much advantage. Moreover, these versions can be the cause of some compatibility headaches.

    I know that "64-bit version" is a checkbox item for end users and IT departments as they deploy applications on new machines with 64-bit versions of Windows. In some cases it's automatic: if there is a 64-bit version, they'll deploy it. But I would argue that the decision requires more forethought. For affirmation of this opinion, I look to Microsoft's own guidance about the 64-bit version of Microsoft Office 2010. (It's not yet the recommended option for most people.)

    Considerations for SAS Enterprise Guide

    Remember, the 32-bit version of SAS Enterprise Guide works great on 64-bit Windows. You can use it to access a 64-bit version of SAS, and it's the SAS process that does the heavy lifting. Using the 32-bit version of SAS Enterprise Guide does not limit your ability to access large data or to use SAS for any memory-intensive processing.

    The only benefit that a 64-bit version of SAS Enterprise Guide might offer is the ability to run really big projects and process flows. That's because project files and ODS results are loaded into the SAS Enterprise Guide process space. (That's SEGUIDE.EXE if you're keeping track in Windows Task Manager.) But those projects would have to be really big, with big results, to consume an amount of memory beyond what you can address with 32-bits. Big projects tend to be more difficult to maintain, so you might consider reorganizing them to make your life easier.

    On the other hand, because the 64-bit version of SAS Enterprise Guide cannot interoperate with 32-bit components in-process, you might find a few features missing. In practice, these are a few of the limits:

    • You cannot directly open data using 32-bit data providers (often used from File->Open->OLE DB or ODBC).
    • You cannot use SAS Enterprise Guide to import data using 32-bit providers (includes Microsoft Exchange, Microsoft Access, plus legacy formats such as dBase). (Note that this is different than PROC IMPORT, which depends on the bitness of SAS, not of SAS Enterprise Guide.)
    • You cannot view certain results "embedded" directly within the application, such as PDF or RTF, as these use 32-bit viewers.

    Finally, if you simply cannot decide, you can install both versions of SAS Enterprise Guide 5.1: 32- and 64-bit. To do so, you must run through the installation process twice, once for each version.

    Considerations for SAS Add-In for Microsoft Office

    Because it's an add-in to Microsoft Office, this decision is easier. It all depends on which way you decided to go after considering this guidance from Microsoft.

    If you have the 64-bit version of Office, you need a 64-bit SAS add-in. And if you have the 32-bit version of Office, you need the 32-bit SAS add-in. And even though there is some overlap with SAS Enterprise Guide, your decision for one application doesn't affect the other. For example, on a single machine you can have the 32-bit version of SAS Enterprise Guide and a 64-bit version of MS Office with the 64-bit SAS Add-In for Microsoft Office.

    More than doubled: implications of 32-bit versus 64-bit

    The architecture differences of x86 versus x64, and their respective operating systems and applications, are the source of much confusion among SAS customers and among consumers in general.

    There isn't a clear-cut, right-or-wrong answer. Which architecture you choose for a particular application depends on many factors. And your decision will have implications, most likely related to how your application deals with legacy processes that use a different architecture. At SAS Global Forum this year, I'll be presenting a short "Super Demo" talk about some of these issues. As I assemble my notes on this topic, I'll be posting more of them here on the blog.

    I'm also interested in hearing from you. What have your struggles been like around this topic? Have you found a 64-bit version of an application (SAS or not) that helped you to get beyond a limitation? Let me know by leaving a comment.

    tags: 64-bit, SAS Add-In for Microsoft Office, SAS Enterprise Guide
    2月 232012
     

    Do you want your report to look good on the web, or to look good when you print it? Pick one.

    Before the SAS Report file format, that was the choice that you faced.

    HTML is perfect for the web browser. It's easy to scroll through tables, to apply an attractive color scheme, and to interact with graphical output. But if you try to print it, it's a mess. Tables don't always fit on a page, page numbering is nonsensical, and your colors get lost.

    If you want printed output, PDF is the way to go. Your output is optimized for the printed page. Tables, if they don't fit on a single page, have repeating column headers on subsequent pages. You can control whether it's landscape or portrait. But to view it on the web, you need a special browser plugin. If you try to share sections of content with copy/paste operations, you can end up with some funky results.

    SAS Report combines the best of these two mainstay formats into a single destination. In my Chunky-soup-style slogan,  "SAS Report views like HTML and prints like PDF."

    If you use SAS Enterprise Guide, SAS Web Report Studio, or SAS Add-In for Microsoft Office, then you already use SAS Report format. (You're soaking in it, metaphorically...though it might not result in softer hands.)

    Here's a partial list of where SAS Report makes an appearance:

    • It's the default output format for results in SAS Enterprise Guide (version 4.2 and later).
    • It's the format for report definitions in SAS Web Report Studio, which are stored as SRX files (SAS Report XML).
    • It's how the SAS Add-In for Microsoft Office pulls SAS output into your Microsoft Office client (such as Excel), and keeps the substance of your result while allowing you to apply custom formatting using Office features.  How does that work?  The SAS Report format actually keeps the data (substance) separate from the appearance (layout and style), so it's easier for the Add-In to keep them straight.
    • It's an ODS destination (ODS tagsets.sasreport12) that can feed content to all of these client applications from any SAS program.

    SAS Report is the lingua franca of SAS business intelligence applications.  You can share your SAS Enterprise Guide report with SAS Web Report Studio.  In the SAS Add-In for Office, you can open reports that were created in SAS Web Report Studio or SAS Enterprise Guide.

    SAS Report output can also be pulled apart and combined to create entire new reports.  The Report Builder in SAS Enterprise Guide (File->New->Report) allows you to take output from different tasks and programs within your project, and combine these into a report definition that shows just the pieces you want to share.  You can even design the report with side-by-side layouts, add text and graphics, and control how the report fits on a page.

    SAS Report may be a proprietary SAS file format, but that doesn't tie your content to SAS client applications. You can use SAS Enterprise Guide to save the report to PDF or HTML when you need to share with colleagues who are less fortunate (who don't have access to SAS).  And of course, you can "print" SAS Web Report Studio reports to PDF.

    And here's another little trick: SAS Report doesn't just print nicely -- it also copies nicely. In SAS Enterprise Guide, you can right-click on a table or graph in the SAS Report viewer and select Copy, then Paste into a Microsoft Office document (such as a spreadsheet or Word doc). SAS Enterprise Guide puts well-formed HTML "on the clipboard", and that's readily accepted by Office applications. (Hat tip to Tricia for reminding me how cool this feature can be.)

    tags: SAS Add-In for Microsoft Office, SAS Enterprise Guide, sas report, SAS Web Report Studio