NOTE: Prompts, Beyond the Basics With Enterprise Guide

There's an enormous number of capabilities in Enterprise Guide (EG). I'm not just referring to the wide range of tasks for data manipulation and data analysis, nor the fact that you can type your SAS code or you can drag and drop tasks to make a flow diagram, I'm thinking of capabilities like Prompts. If you want to make your EG projects more flexible and more easily used by those with less SAS skills than yourself then Prompts should interest you.

In brief, Prompts allow you to make your EG project parameter-driven, and the parameter values are requested via a nice, neat dialog box. The dialog box is automatically customised to include the parameters you have included in your Project; it allows the user to input simple text, but can also offer drop-down lists, date pickers and other widgets that make data entry easier for the user.

Prompts are easy to use within your project. They're stored and referred to as macro variables, i.e. you need to remember to prefix the Prompt's name with an ampersand (&) when you use it.

So, let's take things in order: how do we create prompts, where can we use them, and how do we use them?

To create a prompt, use EG's View menu and select Prompt Manager. This will show the Prompt Manager pane. From there, you can click Add; and from the Add New Prompt dialog, you can specify your first Prompt. The dialog is fairly self-explanatory. The key field is "Name", this is the name that you will use in your tasks and code in your Project. The other key field is "Method for populating prompt", this tells EG whether you simply want to allow the user to enter values, whether you want the user to select from a list of values that you have provided, or whether the user shall select from a list of values taken from a data set (this latter option is known as a Dynamic Prompt).

The Prompt Manager is the main focus for creating and editing Prompts, but there is also a button in the Query Builder task dialog.

There's a wide range of places you can use a Prompt. You can use Prompts in queries, tasks, programs and stored processes.

To use a Prompt in a query or task, the opportunity is usually made clear. For example, when offered the opportunity to type a value in Query builder, you're shown a dialog with three tabs: Values, Columns, Prompts. If you use a Prompt within a task or query by typing its name (prefixed with an ampersand) then you'll need to tell EG your intention. You can do this from the Properties of the task or query - you'll see Prompts listed in the left-hand pane of the Properties dialog. Use the same approach for programs, i.e. select the Properties of the program and edit the Prompts pane.

If you would like to know more about Prompts, I recommend you read Angela Hall's excellent SAS Global Forum 2011 paper Creating Reusable Programs by Using SAS Enterprise Guide Prompt Manager.

Prompts are very simple to put into use and to maintain. They provide tremendous flexibility with their choice of differing types, defaults values, etc. And finally, they make your EG Project much more accessible to those with less SAS knowledge than yourself. If you're not already using them, I suggest you take the first opportunity to do so.

Enthusiasm and Vision #RaspberryJam

Well, that was an interesting evening! Lots of enthusiasm, lots of  geekiness, lots of vision, and lots of printed circuit boards. Inspiring.

I've just been to the third Raspberry Jam in London, an event designed to facilitate the sharing of knowledge and experience of the exciting new Raspberry Pi microcomputer. Its British creators call it "an ARM GNU/Linux box for $25", but to me it's an attempt to recreate the fun and learnings I experienced as a teenager in the 1970's with an Atari 400 (or BBC micro, TRS 80, or Sinclair Spectrum). We called them microcomputers in those days.

This wasn't just a trip down memory lane. The British approach to teaching Information and Communications Technology (ICT) has consisted largely of teaching kids how to use the primary components of Microsoft Office (Word, Excel, PowerPoint and Publisher), producing posters, leaflets and the like. That's not what got me excited about computers as a teenager. I got hooked on computing by coding stuff - being creative and building something that didn't exist until I created it from my own design. Being able to drive Microsoft Office applications is a good office skill, but it won't produce a new generation of computer scientists in the UK; that's computer scientists who will contribute to the music, video, movie and games industries amongst others.

I secretly harbour ambitions to be an IT teacher at a senior school (age 11-18). When I say IT teacher, I'm thinking of teaching kids how to write code, how to think about design, and how to create new technology. There was an enthusiastic group of teachers at tonight's event with just the same ambitions. Their schools and their pupils are lucky to have such an enthusiastic and visionary bunch of people. I was glad to meet like-minded people.

I was expecting the main part of this evening to be one or two presentations from people who've done interesting stuff with their Pi's. Instead, our hosts (Mozilla) had kindly provided several monitors and display stands so that anybody with a Pi could hook it up and show it off. This left the rest of us to circulate and (big intake of breath) talk to each other. As a child I was taught not to speak to strangers; as a British adult, I continue to follow the same teachings!

The Mozilla office is located in St Martin's Lane, near Covent Garden, in London's theatre district. The character and ambience inside the building were artistic and creative. The free beer, nuts, Pringles and Doritos helped too. I had no trouble talking to strangers!

All-in-all, it wasn't the evening I expected, but it was an enjoyable evening nonetheless and I hope to return to the next event (in October). Moreover, I hope that the Raspberry Pi fans can continue to influence ideas about what we should be teaching our kids with regard to technology: how to use it, or how to create it. I say "how to create it".

My thanks to Ben and colleagues for their organisation of the event, and to Mozilla for kindly hosting it.

Whatever You Call It, It's All About People First

Achieve Intelligence (AI) just published its latest monthly news article. AI's monthly publications are all themed on building your Business Intelligence (BI) strategy. This month's publication is entitled "Capability Improvement – The Three P’s" and it describes the importance of people, process and plumbing in your strategy.

AI's monthly publications are long-overdue a mention in NOTE: because they are written by a team of people who have "got the T-shirt" in addition to going "there" and seeing "it"; plus, the publications are written in bite-sized chunks so you can take an actionable nugget of information from each monthly publication. Similar to NOTE:, you can visit the web site monthly, or you can request a convenient monthly email. I've not yet found a means to subscribe through RSS (many NOTE: readers use this approach).

Past publications from AI have included:

• Why do I need a Business Intelligence Strategy?
• How to Create a Business Intelligence Strategy
• Reasons for chaos: before a Business Intelligence Strategy
• Five Areas of Business Intelligence Strategy
• Stakeholder Management

AI's topic this month is one that I visited myself a couple of years ago. In "Keys to Success: People, Process, Technology", I wrote about how people are the most important factor in the success of any endeavour, closely followed by the business processes that the people use to achieve their goals. I refer to the final element as "technology", AI prefers "plumbing", but our message is the same... no project or strategy can focus on technology alone.

If you'd like to receive some advice and challenges about BI strategy each month, subscribe to AI. Better still, invite them to visit you and discuss your BI strategy. You do have a clearly-expressed BI strategy, don't you?...

NOTE: Let's Go to San Francisco #sasgf13

Every time I think of next year's SAS Global Forum I get the sound of The Flower Pot Men floating around my head - Let's Go To San Francisco. I may only have turned five years old during the 1967 "summer of love", but some of those wonderfully happy and carefree songs (from the likes of The Beach Boys, Beatles, Monkees, Small Faces, Mamas and Papas, Kinks, and Scott Mackenzie) have infiltrated my memories ever since and remain with me. I must make sure I pack some appropriate tracks on my music player before travelling next April.

I'm always full of grand plans for SAS Global Forum papers at this time of year. Sometimes I find the time and put the work in, other times I have to trim my ambitions. Right now I'm keen to rise to conference chair Rick Mitchell's challenge of accommodating 100 posters, so I'm thinking of submitting one full paper, one coders' corner paper, and a poster. Too much? Maybe, but I'm wondering if I can link them and set them around one theme. And then I need to weave some flower power references into them.

Overly ambitious? Maybe, but the deadline for contributed submission abstracts and working drafts of papers and posters is November 19, so I have a few weeks and months to be creative.

I always find the first word of the first paragraph to be the most difficult. And I also find, no matter how familiar the topic may already be to me, that I learn more about the topic whilst I'm writing. That's not to say that writing a paper is necessarily hard, it's just the simple fact that writing makes one think more comprehensively about the topic, and one becomes aware of small gaps in one's knowledge.

So, writing a paper helps me to solidify my own knowledge, and presenting my paper is of benefit to SAS Global Forum attendees (I hope!). It's mutually beneficial. Combined with the benefits of visiting San Francisco (cue the song in my head again!), I'm determined to attend next year's SAS Global Forum and get my papers and poster into the programme.

My comparative lack of hair means I won't have to worry about whether to follow the advice of the late Scott McKenzie and wear flowers in my hair. Phew, that's a relief!

NOTE: What Am I Reading?

We all try to write data-driven code, or code that provides some degree of flexibility regarding its input data. We see these approaches as reducing maintenance and making the code more robust. There are a number of features in the SAS language that allow us to know more about the data that we're using. Have you come across the DATA step's CALL VNEXT routine? No? Then read on...

The CALL VNEXT routine returns the name, type and length of a variable in use in a DATA step. In this case, "in use" means "in the Program Data Vector (PDV)". The PDV is the area of memory that the DATA step sets aside to hold all variables that are read in or created during execution of the DATA step. Understanding the PDV, how it is constructed, and how it is populated is very important if you are to truly master DATA step programming. CALL VNEXT provides a good insight.

The following log output shows how successive calls to VNEXT reveal details about each successive variable in the PDV. See how VNEXT returns information about first. and last. variables if a BY statement is present, plus _ERROR_ and _N_.

18         proc sql;

19           create view classv as

20             select *

21             from sashelp.class

22             order by sex;

NOTE: SQL view WORK.CLASSV has been defined.

23         quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.01 seconds

      cpu time            0.00 seconds

     

24         data _null_;

25           set classv;

26           by sex;

27           length vname $32 vtype $1 vlength 8;

28           vname='xx';

29           do while(lengthn(vname) ne 0);

30             call vnext(vname,vtype,vlength);

31             put vname= vtype= vlength=;

32           end;

33           STOP;

34         run;

vname=Name vtype=C vlength=8

vname=Sex vtype=C vlength=1

vname=Age vtype=N vlength=8

vname=Height vtype=N vlength=8

vname=Weight vtype=N vlength=8

vname=FIRST.Sex vtype=N vlength=8

vname=LAST.Sex vtype=N vlength=8

vname=vname vtype=C vlength=32

vname=vtype vtype=C vlength=1

vname=vlength vtype=N vlength=8

vname=_ERROR_ vtype=N vlength=8

vname=_N_ vtype=N vlength=8

vname=  vtype=  vlength=0

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: There were 2 observations read from the data set WORK.CLASSV.

NOTE: DATA statement used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

Ian Whitlock's SAS User Group International (SUGI) paper titled "How to Think Through the SAS DATA Step" for SUGI 31 provides a lengthy and detailed exposé of the PDV and other key elements of DATA step programming. Jim Johnson's "The Use and Abuse of the Program Data Vector" from this year's SAS Global Forum (SGF) focuses neatly on the PDV. If you want to know more about the PDV, these papers are excellent sources of information.

Making your programs robust (meaning they don't fail, for example, every time there's the slightest mistaken variation in input data) and making them future-proof (meaning they don't need to be amended and re-tested when, for example, field lengths get longer due to a corporate merger and the introduction of unforeseen data sources) won't keep you busy with maintenance work but will give you time to spend on more productive, innovative activities. CALL VNEXT might just help you in achieving this goal.

NOTE: New Ways in Means

There aren't many SAS programmers who haven't used PROC MEANS (or its alter ego PROC SUMMARY) to aggregate (summarise) their data. PROC MEANS is a familiar tool for most SAS programmers, and the process of subsequently selecting specific values of _TYPE_is an equally familiar if slightly more painful activity. If you've ever struggled to select the correct _TYPE_ value(s) then I wonder if you know of the CHARTYPE parameter.

Traditionally, SAS software created _TYPE_ as a numeric variable, and we had to select values based upon a calculation or by use of bit strings. However, version 8 introduced the CHARTYPE parameter to PROC MEANS. CHARTYPE tells PROC MEANS to create _TYPE_ as a character variable. This makes the select process much easier. Compare the following snippets of code.

/* Old Style */
proc means ...
  class continent country state town;
  output out=old ...
[snip]
data olddemo;
  set old;
  select (_type_);
    when (5) /* country & town */
    [snip]
    when ('1010'b) /* continent & state */
[snip]

/* New Style */

proc means chartype ...
  class continent country state town;
  output out=old ...
[snip]
data olddemo;
  set old;
  select (_type_);
    when ('0101') /* country & town */
    [snip]
    when ('1010') /* continent & state */
[snip]

As you can see, you can select rows which contain your desired class column combination by simply specifying 1's for the columns you want and 0's for those you don't. The length of the _TYPE_ variable is determined by the number of CLASS variables.

If you'd like to learn more about PROC MEANS, I recommend Andrew Karp's excellent SAS User Group International (SUGI) paper titled "Advanced Tips and Techniques with PROC MEANS" from SUGI 27. Andrew covers CHARTYPE and much, much more - all with excellent examples.

NOTE: True Self-Service BI With Futrix

Over the last couple of weeks I've featured a couple of 3rd-party SAS-related products (Metacoda security plug-ins and Phil Holland's Ebook/apps). I've said how much I support 3rd-party products. So, I couldn't let this unintentional mini-series end without including the Daddy of them all: Futrix.

Futrix's ability to let its users drill up, down, and across without being constrained by cube definitions or data hierarchies is one of the key attractions of this SAS-based Business Intelligence (BI) tool that has been around since 1997. The ease with which novice users can pick up and use Futrix belies other vendors' claims to ease-of-use. Futrix is ease-of-use personified.

But don't think that ease-of-use equals limited-in-ability. Aside from the ability to drill anywhere (including jumping from one table or cube to another whilst preserving your drill-level), Futrix allows dynamic grouping (for creating categorisations on the fly) and user-defined formulas. The information can be presented in a mixture of tables, charts and dashboards.

Beneath the user interface there's a sophisticated set of administrative tools that include a full security model and the ability to allow Futrix to dynamically tune cubes and its own aggregations.

Product development is rapid, and influenced by customer feedback.

Take a look at the web site, take-up the offer of a free trial demonstration, and take your SAS-based data to a whole new audience!

NOTE: Celebrates Its Third Birthday

It seems a lot longer, yet today is just NOTE:'s 3rd birthday*. In that time we've published over 300 posts and received over 80,000 page views.

Around 400 people subscribe to our RSS feed, and 200 subscribe to our email feed. We have nearly 150 Twitter followers.

Thank you for your continued interest in NOTE:. I write about what interests me; that's a mixture of SAS technical hints and tips combined with best practice for the software development life-cycle (SDLC), with an occasional mention of gadgetry.

I enjoy writing the blog articles, and I'm glad there's an audience for them. I'm always keen to increase the size of that audience, so if you have a colleague, manager, or team member who might also be interested in NOTE:, please do encourage them to subscribe.

I'm a freelance SAS consultant, and I work through Ratcliffe Technical Services Ltd from the UK. I individually provide a variety of services (covering all points of the SDLC, plus project management and team management), and I also provide additional individuals and teams for clients too. My posts come from a mixture of personal experience plus my avid readership of SDLC and team leadership books and articles.

Here's to the next 300 articles!

-------
* Yes, the very first three posts that you can see on NOTE: are from July 2009, but they were experimental and I didn't commit to the blog until September.

NOTE: A Book That Grows? That's Got To Be A New Concept!

Following my feature of Metacoda and their security plug-ins last week, I have to feature Phil Holland. As I said last week, I'm a keen supporter of the third-party market for SAS-related products. In addition to his two published books (Saving Time and Money Using SAS, and Power User's Guide to SAS Programming), Phil has a collection of apps that act as books but deliver the flexibility of apps. Let me explain.

Phil's apps are available for WebOS, Android, and Chrome. I sat down with Phil and a whole bunch of different bits of his hardware a couple of weeks ago. Phil explained his (unique?) concept to me over dinner and a few beers. The apps are a series of eBooks about SAS software programming. They focus on frequently asked questions and answers, and they are illustrated by code samples. The code samples can be sent by email directly from the app (copy-and-paste would be of no use because the apps are on mobile devices). So far, they sound like relatively conventional eBooks. So, why has Phil developed them as apps?

Phil has designed the eBook/apps to encourage users to contribute additional "how do I do this" questions. Questions (and answers) that are of interest to the wider audience are then added to upgrades of the apps by Phil. The upgrades are delivered free, over-the-air to the mobile devices. So Phil's eBook/app readers get the benefit of additional content, and their books just grow and grow.

The current titles in the series are:

• Data Steps
• Platforms
• SQL
• Enterprise Guide

The cost of each is minimal, and there's also a free sample book. With the content coming from Phil, you can be assured of its quality and accuracy.

I think Phil's concept is imaginative and could be applied in many areas, not just SAS. The apps are a relatively new venture for Phil, and I wish him well. Go take a look!