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.
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.
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.
When I save the data query and preview the data, you can see my new BMI column.
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!
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.
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.
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.
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.
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.