BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Visual Analytics

SAS VA Data Builder: Add a New Data Item

Submitted by on 2016-02-05 – 9:25 AM

The SAS Visual Analytics Data Builder allows you to prepare and load data to the SAS LASR Server so you can complete analysis or build reports and dashboards. It’s a little more tricky to use than some of the other parts of SAS Visual Analytics but can usually be mastered within a day. If you need to add a data item to your data table, just click the plus icon to get started.

Using the Data Builder

The Data Builder is not meant to be used by everyone generally it’s limited to a few users who are familiar with the data sources. Most customers use it to load data from a data source such as a database or Hadoop. The Data Builder can join tables and load data to the HDFS and LASR Server. It can also be used to schedule jobs  You can also code in the Data Builder if you want to use IMSTAT procedure or some other select procedures.

va_db_05

Adding a Calculated Data Item to the Output Table

You can use the Data Builder to build the output data source or what you want loaded in to the LASR Server to use in your reports. In this case, I used the HEART data from the SASHELP library. The data contains statistics about heart patients from the e Framingham Heart Study.

After adding the HEART data table, I added a few columns of interest.  I don’t want all of the data for this example. To add a new data item, click the Plus icon, type the name, select the type (Number, Character, Date, etc) and type your expression. In some cases your expression may be simple enough that you can type it in the Expression field, but you can use the Expression window if it’s a bit more tricky.

va_db_01

In this instance, I want to add the Body Mass Index calculation to the other factors in the HEART table. When I click the Expression icon, the following window appeared.  It allows me to type the formula and pull the data items that I want to use. You can see I have the formula based on the Height and Weight items.

va_db_02

When I save the data query and preview the data, you can see my new BMI column.

va_db_03

What is it Doing?

Behind the scenes the Data Builder is writing SQL code. If you click on the Code pane, you’ll see all the code needed to complete the query. You may have to scroll down to see your specific SQL procedure. If you are handy with the SAS code then you can unlock the code and work on here. Just remember, once you unlock the code there is no going back!

va_db_04

 

Adding a Data Item with a Format

You can also apply a format to a data item to create a new data item. In this instance, I have the Candy tables from the SAS Enterprise Guide samples. In this case the month name is stored as a number and we want to see it as month name.  When I added the Month_Num data item, I changed it to Date type so the monname. format would be available.

va_db_06

To see the formats, click on the Format column to see the Select a Format window. Scroll to the MONNAMEw. format and select how you want the month name to appear. For instance, when Width is set to 9 the entire name appears, but when you select 3 only the first 3 letters of the month name are shown.

va_db_07

Here’s what it looks like when I view the result.  I added two data items so you could see the difference in the 9 vs 3 width.

va_db_08

And here you can see how the code applied the format. Also remember the label is what the user’s see when building reports or explorations.

va_db_09

 

That Was Painless …

Adding data items is a painless task – you just need a few pointers. What you may have noticed is that all of your data needs to be in a SAS readable format, such as a SAS dataset or SASHDAT table if coming from the HDFS.

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.

 

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , ,