Tag: Add-In for MS Office

SAS Add-In for MS Office: Abe Lincoln Sends Glee in a Snowstorm

lincoln loves sas

It’s been really snowy and cold in North Carolina for the past week.  About 5″ of snow fell in 2 hours, which unleashed pandemonium. Power was out for some, flights were cancelled because the airport shutdown, and many people even left their cars beside the road electing to walk home or stay in a hotel for the night. Since I work from home, it was …

Post SAS Add-In for MS Office: Abe Lincoln Sends Glee in a Snowstorm appeared first on BI Notes for SAS® Users. Go to BI Notes for SAS® Users to subscribe.

SAS Stored Processes: Querying a Stored Process from Excel without the Add-In

Stig Eide

What do I have in common with a guy in Norway?  Well, if you look at my last name Aanderud – you might think he is a family member.  Turns out Stig Eide and I both use SAS Stored Processes to solve issues!  Recently this SAS platform administrator asked if he …

The post SAS Stored Processes: Querying a Stored Process from Excel without the Add-In appeared first on Business Intelligence Notes for SAS® BI Users. Written by .

SAS OLAP Cubes: Viewing Member Properties in Excel and Enterprise Guide

As a follow-up to my earlier post on taking advantage of OLAP member properties, you can also display OLAP member properties through the Add-in for Microsoft Office as well as SAS Enterprise Guide.  I’m a huge fan of Enterprise Guide, so it’s nice to have that ability but even nicer when the more common information consumer can display member properties through a Pivot Table in Excel. One Click to More Information Simply right-click the level and select Show Properties in Report.  From there you can check the properties you have defined. I like the way the Pivot Table displays member properties as each column in Excel.  This information gives the user a little more insight into the data. Also Works in SAS Enterprise Guide To display member properties through SAS Enterprise Guide the approach is similar, right-click the level and select Show Member Property. From there you can check the member properties you want to display.   More about OLAP Member Properties Check out my earlier post to see how these same OLAP member properties are defined and displayed in SAS Web Report Studio. Related content: SAS OLAP Cubes: Taking Advantage of OLAP Member Properties SAS Business Intelligence Tools Overview SAS […]

SAS Enterprise Guide: Pick a Winner Randomly

To pick the winners of the 50 Keys to Learning SAS Stored Processes book, my first thought was to print the list and then put everyone’s name in a hat. Angela brilliantly suggested that we use one of the SAS Random functions.  However, since there was a list  of names I really needed a random number between two values.  This brought to mind the MS Excel RANDBETWEEN function – but how do you translate that function to SAS Enterprise Guide code? Getting a Random Number between Two Values I have to admit for a one-off process like picking a random number,  MS Excel would be my goto tool. MS Excel has a function called RANDBETWEEN() that allows you to provide two numbers and it then selects a random value between those two numbers.  I tried to use the SAS RANDOM functions – but I couldn’t figure out how to make it work in 5 minutes (my personal patience meter) so I needed an expert.  Consult with a SAS Expert Lucky for me SAS-L had the answer – it’s one of the places I search for SAS tips when I’m stuck.  In August 2000, Mark Terjeson wrote a post called “Just […]

Excel and OLAP Viewer: Not enough room for the Cube!

When you use the OLAP Viewer in Microsoft Excel you may have run into the window borders – literally.  Your cube and analysis needs more space!   Scroll, Scroll, and Scroll to See the Cube Data  When you open the OLAP cube in the MS Excel OLAP Viewer the default cube size dictates the area used. You may get 20 rows by 20 columns or you might get 2 rows by 2 columns. So when you want to do some analysis, you expand a dimension and scroll, scroll this way and then scroll, scroll that way to see the coveted data!  We need space to do proper analysis!! Oh have the data gods forsaken us?!? Answer to Your OLAP Prayers Obviously I would not bring up this point if I did not have an answer. So here’s how you work yourself out of this tight spot. Trick is to turn on the MS Excel Developer Ribbon. It may already be turned on – for instance, in the figure below you can see the Developer menu between the View and Excellent Analytics menus in my MS Excel menu.   If you have the Developer menu – then skip to the next step.  Otherwise, […]

What Can I Do With a Stored Process?

Often I refer to SAS Stored Processes as the workhorse of the SAS BI tools … you can use these little devils everywhere sometimes as Band-Aid, sometimes as the star, and sometimes as a background player. In a past post, I showed you how to use a stored process to add dashboard dials to Web Report Studio and Linking to a Stored Process from Web Report Studio. Add-In for MS Office – View Reports or Data Use the stored process to deliver a report or maybe just a dataset (using Proc Print) to the Add-In for Microsoft Office (AMO). In the past, I set up several stored processes that only contained a PROC PRINT to display a data set. Then I added a prompt for Product, which allowed the user to query product inquiries based on the product of interest. Here’s how you access the stored process from AMO 4.3. This works similarly in prior versions of AMO. Open MS Excel (or Word/PowerPoint). On the SAS ribbon, click the Reports icon. Navigate to the stored processes storage location and click the stored process name. < Optional> This stored process has two prompts: Visitor Type and ODS Style. Your stored process […]

SAS Add-In to MS Office: Refreshing the Data & Breaking the Link

If you use SAS Add-In to Microsoft Office – specifically Excel, then you already know how awesome it is to link to the SAS data on the server. First, if you are using data that needs to be refreshed – just a click of the button brings you the new rows and your report or charts are updated immediately.  Awesome! Let the analysis begin …  Refreshing Your MS Office Report with SAS Data Using the SAS Add-In to MS Excel (or Word or PowerPoint), you can build charts from the SAS data sets. You may have a report that you create each month based on an Oracle table that is extracted into SAS. On the first day of each month the data is made available, which is great because you report is due the next day and your manager gets upset when it’s not on his desk. [He’s a fan of your work – what can you say?] Let’s make your job a little easier so you can get back to answering your fan mail. Using the SAS Data button from the SAS ribbon, you can peruse the SAS Server to find the desired data.  With the found data, you […]