Coalesce the Missing Values

The coalesce function (alternatively the coalesceC function for character values) is very useful for selectively loading a field depending on the state of data.  The parameters are simple.  Just reference variables in your data or explicit hard-coded values and the coalesce function picks the first non-missing value for that observation.  It selects based on the order variables are entered, from left to right.

coalesce( [first variable], [second variable], .... , [Nth variable])

Sometimes I hard-code the following values at the end of the coalesce parameter list to ensure something gets entered (depending on requirements):

• !UNKNOWN
• !MISSING
• !HEY LOOK AT ME

Using these standardized values can help the business spot missing values very quickly, especially if you use a special character such as the exclamation point which sorts missing values at the top when viewing in ascending order.  

The following code fills missing values of ‘DeathCause’ in the SASHELP.HEART dataset:

data out;
  set SASHELP.HEART; 
  DeathCause = coalesceC(DeathCause, '!UNKNOWN');
run;

The missing values are converted to !UNKNOWN:

Identify Missing Data when Loading a Dimensional Model

Coalescing missing foreign key values can also be useful when loading a dimensional model.  In a star schema, categorical values are stored in dimension tables with corresponding foreign keys that references these values from fact tables.   The purpose of foreign keys is to describe the factual numeric values contained in the fact table by joining to the related dimension table.  A good best practice is to always load explicit non-NULL foreign key values to ensure numeric data is always identified and because your DBA may not like NULL values within integrity constraints.  If a numeric value truly has a missing dimension, you can use the coalesce function to stage a “zero” value for the foreign key in a fact table.  You could also use a value of “-1″ as the “missing” foreign key value.  This also acts as a “catch all” to make sure the ETL process completes with no errors due to attempting to insert a missing or NULL value in a fact table. 

This is an example DIMENSION table I’m using to reference address locations in a fact table.  

The fact table can reference the ‘address_key’ of 0 for anything that is missing or unknown.

These are two ways I’ve used the COALESCE() and COALESCEC() functions.  Do you have any other uses?

The post Coalesce Missing Data to Highlight the Unknown appeared first on Business Intelligence Notes for SAS® BI Users. Written by Steve Overton.

Ok I’m looking for SAS BI Rock Stars at SAS Global Forum 2013.  If you are a regular blog reader – you have to find me!  We have ribbons to add to your name tag. 

Even if you are not a SAS BI user – you can still have one and just say you’re in training. 

Check Out These Spots

If you cannot find me … then check Michelle Homes at the Metacoda booth in the demo hall as well!  Vince DelG0bbo also has some at his booth.

Steve Overton will have some as well. 

#SASGF13 #TweetUp Photos!

Check out the photos from last night’s event.  We all had a blast playing a Bingo game full of trivia like “Find a Trekkie”, “Find someone with a rack mounted server in home”, “Find some wearing blue shoes”, “Find some wearing a SGF tee-shirt”… and on and on.

See all the photos at the Metacoda SASGF13 TweetUp stream.

The post SASGF13 – Calling All SAS BI Rock Stars appeared first on Business Intelligence Notes for SAS® BI Users. Written by Tricia Aanderud.

Last December I had short chat with Rick Styll, a SAS product manager, who was really excited about the product and encouraged me to learn about it and write about it.  I believe he brought SAS Press editor Julie Platt with him as a hint.  When VA 6.1 was introduced, I grabbed the free online customer documentation  to start reading it.  [That's right ...when all else fails RTFM baby!]  Ok, would you believe I skimmed the first few chapters – heavily?

[BTW Rick is presenting a Visual Analytics paper on Tuesday afternoon so make sure you attend and sit on the front row.]

What is Visual Analytics?

Remember the idea is that this product can handle big data quickly and allow users to easily interact with the data!  Last year Dr. Goodnight said the application reduce a job that required ~18 hours to run to under a half hour. Impressive for sure – but that was a lot of hardware.  I like the live demo he gave on stage of the product [see here:Link to billion records in seconds video.]  It was certainly fast and the results were very pretty.

That’s an important point because some of the SAS output would not win any beauty contests -especially when Excel charts go walking down the runway.  The speed was due to the fancy server SAS LASR Analytics Server, but the beauty was the Visual Analytics product.

Quick Product Overview 

Visual Analytics is a web-based application that can be broken into several components:

• Visual Data Builder allows you to build queries, join tables, and create calculations from registered database tables. It’s how you prepare the data for display.
• Visual Analytics Explorer allows you to interact with the data.  You can build charts, tables, maps in the workspace area or just play with the data.  
• Visual Analytics Designer allows you to create reports or dashboards that can be viewed on a mobile device (using the SAS Mobile BI app) or in the viewer.  This product is most similar to the existing BI Dashboard product.
• Visual Analytics Viewer allows users to view the report on a Web browser.
• Visual Analytics Administrator allows you to manage the environment.

This configuration looks similar to another SAS application that we may be more familiar with!

I downloaded the SAS Mobile BI app for my iPad from the Apple store. [Also available on Google Play.]  SAS has some samples setup so you can interact with it to learn how to use the product. Just click on the Library button and you’ll see a list of items you can add.  For instance, I added Mashups and Explorations. 

Questions I Have

First let me re-iterate that I like what I am seeing so far in the demos and the documentation.  I’d say SAS is rockin’ it with this tool and it generates some fantastic results.  I also realize every product has it’s limitations – which is not good or bad – just reality.

After skimming the documentation, here’s the questions I hope to have answered at the conference.  Some questions I think I know the answer  - but I want to confirm before I start blabbing to everyone else.

• What I like about the information maps is that they apply a business layer to the data making it easier to understand.  Since the Visual Data Builder is inside the database, what features do I use to make the data more “user friendly”?

   

• Who is using the mobile features for this product?  Most of the jobs I have done, the data was extremely confidential.  I cannot imagine a bank allowing their data to be available from an iPad easily.  Maybe summarized data is okay – but is that really useful?  Maybe it’s meant for sales people?
• The product uses Flash.  I thought Steve Jobs used his iPad to squash Flash in favor of HTML5? 
• How should I think about this tool in conjunction with the SAS BI tool set   Is it just part of the tool set  is it replacing it?
  • Looks like there is not an upgrade path to this product from my existing BI install.  What suggestions does SAS have for re-using my existing work?
  • Can I link to my Web Report Studio reports?  Pass values through a parameter?  How about a SAS Stored Process?
  • Are my stored processes re-useable?
• Will Siri interact with my mobile BI app?  Can I make her build charts for me?
    
• Oh … how much does it cost?  How is it licensed?   Can I purchase it without the fancy-schmancy hardware?  Really I’m just looking for a price range, not a quote. 

Where Can I See It?

SAS is presenting some papers about the Visual Analytics product.  Check the #SASGF13 Agenda Builder for times and locations [or the mobile app].  Here’s a partial list that I hope to attend.

Monday

• SAS® Business Intelligence Development Roundtable: SAS Business Intelligence Solutions Portfolio and Future Focus
• Self-Service Data Management: Visual Data Builder 
• SAS® Workshop: SAS® Visual Analytics 6.1

Tuesday

• Whirlwind Tour Around SAS® Visual Analytics
• The Forest and the Trees: See It All with SAS® Visual Analytics Explorer
• Fast Dashboards Anywhere with SAS® Visual Analytics

Wednesday

• How Mobile Changes the BI Experience
• Emerging Best Practices in the Age of Democratized Analytics

Also, check the demo hall for specific product demonstrations.  

Tell Me What You Think

If there are any questions you have, put them in the comment below and I’ll look for the answer.

The post Are You a SAS BI Geek Seeking Visual Analytics Answers at #SASGF13? appeared first on Business Intelligence Notes for SAS® BI Users. Written by Tricia Aanderud.

Here’s the main points:

• LinkedIn is not Facebook, learn the difference.
• Don’t wear sunglasses in your LinkedIn profile picture.
• You need connections!

The post Tips for Creating a Great LinkedIn Profile appeared first on Business Intelligence Notes for SAS® BI Users. Written by Tricia Aanderud.