SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Excel GUI

Reply
N/A
Posts: 0

Excel GUI

Does anyone have any information about building an Excel GUI to extract customer data from SAS? Basically what I'm looking to do is have a GUI with selects to choose from and export customer data. We want to eliminate some of the requests we have and thought by building a GUI some of our associates can pull data/counts themselves. I have a white paper on SAS/AF but that doesnt help me too much. I've done some pretty extensive searching but keep coming up empty handed. Any help will be greatly appreciated!!! Thanks!
SAS Super FREQ
Posts: 8,721

Re: Excel GUI

Hi:
The SAS Add-in for Microsoft Office, part of the SAS Platform for Business Analytics (also called the BI Platform), allows Microsoft Office users (Word, Excel and PowerPoint) to directly access SAS data sources and to perform tasks against the data. So, for example, an Excel user with the SAS Add-in for Microsoft Office could open a SAS data source, filter the data source and place the data into a new workbook or onto a sheet in an existing workbook. Or, they could run a graph task or an analytical task against the data source. Or, they could run a "stored process" and return the resulting report from the stored process program directly to Word, Excel or PowerPoint.

Otherwise, isn't building a GUI with Excel something you would do with Microsoft technology and programming techniques??? SAS would merely be serving data.

cynthia
N/A
Posts: 0

Re: Excel GUI

Hi Cynthia. Our end users are account managers serving our customers and know nothing about the actual fields in our table. The table is a national consumer file consisting of about 200 million records. So really what I need is some type of GUI that I can create select boxes or drop down boxes with masked field names that make sense to them so they can plug in what the customer is looking for. Example: A customer calls in and is looking for prospects to mail to that live in zip code 35201, 45+ years of age, with children in the home. I would need a box for each of these on a GUI for them to plug the criteria into so they could run the record count or actually export the list. Does that make sense? It doesnt have to be Excel or any Microsoft product. I just need some sort of interface for them to use. Thanks for your reply and any advice is greatly appreciated.
SAS Super FREQ
Posts: 8,721

Re: Excel GUI

Hi:
Well, that (drop down boxes that allow the users to select choices that become selection parameters) sounds almost exactly like a stored process program that has had parameters defined for how data should be selected by the user.

If you built a SAS program -- and turned it into a stored process, the automatic prompting interface would present the user with a dialog box that had a selection choice (usually a drop down box, but you can choose other types of selection prompts --like a calendar for dates). This parameter prompting interface is automatically built into the SAS Platform for Business Analytics, when you use stored processes.

Look at the screen shot on page 1 of this paper, at the prompts for STATE and COUNTRY:
http://support.sas.com/resources/papers/proceedings09/330-2009.pdf
or on page 4 of this paper, at the prompt for REGION:
http://www.nesug.org/proceedings/nesug07/ap/ap22.pdf (figure 5 shows how the REGION parameter was defined in the metadata).

The prompting "GUI" was built automatically in these examples -- from the defined metadata that will "run" the stored process based on the user's selections. And, although you could build your own prompting interface (as shown in the first paper above), many people find that the prompting interface that is automatically generated is sufficient for end users who do not know the technical details of the data or the tables they need to access. With a SAS Stored Process, the results can be returned directly to Word or Excel using the SAS Add-in for Microsoft Office. So, it sounds to me like stored processes would be what you're looking for.

cynthia
Esteemed Advisor
Posts: 7,301

Re: Excel GUI

I'm not going to disagree with Cynthia on this, but just add that SAS/AF can also do it and, in my opinion, give you the capability of expanding it in whatever direction you want.

If the product that Cynthia mentioned does what you want .. perfect!

But, if you want to roll your own, I'd look deeper into SAS/AF. It's an awfully powerfull language that has always been far too far underutilized.

My 2 cents,
Art
SAS Super FREQ
Posts: 8,721

Re: Excel GUI

I agree...SAS/AF and SCL are very powerful. However, an AF application in Base SAS is "fat client" and does require (or used to) a SAS license for every desktop where the AF application would run.

SAS Web AF, which is part of the BI platform, allows the use of Java beans to code thin client applications that utilize the metadata and other features of the BI Platform. (A Web AF application would be used typically to create a custom interface to the BI platform -- perhaps as an alternative to the SAS Add-in for Microsoft Office.)

Another alternative would be to write a script (REXX, CLIST, VB) to collect the parameters and pass the parameters to a batch .SAS program. (This is what we used to do in the "old" days when we needed to collect user input -- but this would not necessarily fulfill the request for an Excel-based GUI interface to SAS)

A third alternative would be to use SAS/IntrNet to run HTTP requests (via an HTML form to collect user input) to a SAS Application Dispatcher, which would then kick off a SAS program and could return results to the client machine -- usually a browser, but could be Excel.

But since the original request was for an Excel GUI -- because of the end-user's comfort level with Excel -- to me that is an argument for the SAS Add-in for Microsoft Office.

cynthia
Esteemed Advisor
Posts: 7,301

Re: Excel GUI

Cynthia,

First of all, my best wishes to you and yours for the happiest of new years.

SAS/AF, to my knowledge, only requires a license on one machine in order to develop and compile a routine. The compiled code can then be used on any machine that has the SAS components that the routine calls, but those machines do NOT have to have SAS/AF licensed.

Art
SAS Super FREQ
Posts: 8,721

Re: Excel GUI

Hi:
Happy New Year to you, too!

I did not say that the app needed a SAS/AF license to run, or at least, I did not mean to imply that. For example, if your AF app uses SAS/GRAPH, then SAS/GRAPH must be licensed. If you use PROC TABULATE, PROC PRINT, PROC FORMAT, Base PROC WHATEVER or submit DATA step code in the app, then BASE SAS must be licensed. If you use PROC GLM (or any STAT procedure), then SAS/STAT must be licensed, etc, etc.

But, even with those requirements met, the AF app would still not be callable from within Excel as an "Excel GUI".

cynthia
Frequent Contributor
Posts: 118

Re: Excel GUI

There is also the option that you can program some Excel-VBA code in your Excel Spreadsheet using SAS Integration Technologies (Especially the OleDB Data Provider or the Integrated Object Model (IOM)) to select your SAS data, filter or transform it and import it into your Excel-Spreadsheet.

The only downside i see here is that there is no out of the box solution for this scenario and you have to write your own custom VBA code.
Grand Advisor
Posts: 10,251

Re: Excel GUI

The solution may be to use SAS/AF in a server mode.

I haven't used AF for awhile but one of the internet options was to have a SAS server application do all of the heavy work and the results were presented as HTML or similar output. The users didn't need to have any SAS install, just a web browser.

An example of a site run by the US Census is: http://www.census.gov/hhes/www/cpstc/cps_table_creator.html

This site lets you build requests for categories of data from the Current Population Survey and even set a few customizations.

The parts I vaguely remember on this topic had several different ways to approach the Web/SAS interface.
SAS Super FREQ
Posts: 8,721

Re: Excel GUI

Hi:
That Census application looks a standard HTML form that is calling the SAS/IntrNet broker app. This can be confirmed by looking at the <FORM ACTION...> on the source code for that page and you will find:
[pre]
<form action="/cgi-bin/broker" method="POST">
<input type="HIDDEN" name="_PROGRAM" value="tcprogs.cpstablecreator.sas">
[/pre]

A call to cgi-bin/broker and the 3 level .SAS name are both indicators of SAS/IntrNet (and not SAS/AF) to me. Although, a SAS/IntrNet application could call a .SCL program, this looks like a straightforward Application Dispatcher library program that is taking input from the HTML form fields.

If they had used webAF to build the front-end, the interface would probably have been a Java application or applet and not HTML (when using SAS/IntrNet).

cynthia
Ask a Question
Discussion stats
  • 10 replies
  • 1537 views
  • 0 likes
  • 5 in conversation