What should you do to calculate anythung for you in bar chart of any data?

 

Now I again want to know the profit value, but I don't have a column that contains that information. I need to calculate that myself. So to create a new column, I'm going to click on this Calculator icon on the right-hand side, which says, add a new computed column. This is going to start the new computed column wizard. And it's just four quick steps to define a new column. The first step is to choose what type of column I want to create. This is going to involve an arithmetic expression. So I'm going to choose Advance Expression and click Next. In step 2, I'm going to specify the expression for this new column.

Now, I can directly type in the expression in this expression box. Or I can choose columns, functions, and operators to include in that expression. I'm going to go with the latter method. So to calculate profit, it's going to be the difference between MSRP and invoice. So I'm going to expand where it says Selected Columns. These are all the columns that I brought over onto the Select Data tab. And to include, let's say, MSRP, for example, in the expression box, I can just double-click MSRP. That gets added to my expression. Then I need to subtract. So I'll click on the subtraction operator. And then double-click on the invoice. That's it. Now, this might be a little bit small, so if you want to hold down on your Control key and scroll up on your mouse, you can easily zoom in on that expression.

Now you'll notice that in front of the name of the column, there is a t1 dot. t1 is a table alias or nickname. So it's a nickname for the car's table. It doesn't hurt to have in this scenario, but it's also not necessary. So if you wanted to delete that, that's totally fine. But that is it for this expression. So I'm going to click Next, move on to step 3. Step 3 is where I'm going to specify some properties for this new column. So for example, the column name. Instead of just leaving it as a calculation, I'm going to call it profit. And because I like to display these values as currency values, I'm going to apply a format. So in the Format field, I'm going to click Change. Again, it's going to be a currency value. So I'll select the Currency category. Let's go with the dollar w.d format. The dollar format is going to add dollar signs and commas to our values.

I'm going to go ahead and increase the overall width to 10 just to make sure there's enough room to include the dollar sign, the commas, and all the values that I want to display. That looks good. I'll go ahead and click OK. And that's all I need to do in step 3. I'll click Next. Step 4 is just a quick summary. Everything looks good, so I'll click Finish. You'll notice that the new profit column is automatically added to my Select Data tab and is included as a column in that list of columns. It's under Computed Columns now. Because it's included in this list of columns, I can now use the profit to filter my data or even sort my data as well. Before I move on to the other Filter Data and Sort Data tabs, one thing I'd like to point out about the Select Data tab is that the order that the columns are listed in the order in the columns that will appear in the new output table. So you can go ahead and drag the columns around in the Select Data tab to change the order. Or you can take advantage of the arrows on the right-hand side. So to show you, I'm going to select Profit. And I'm going to click on this Move Up arrow twice. So I can have profit immediately after invoice.

You can move these around freely within the Select Data tab. All right, I have all the columns I want. Now let's filter our rows. I'm going to click on the Filter Data tab. And remember, I'm only interested in sedans. So to create a filter, I'm going to go ahead and filter on the type column. So I will drag the type onto the Filter Data tab. The new filter wizard opens up. Two very quick steps here. I want the type to be equal to-- and I want that value to be a sedan. Now I could type that in here. Or, an easier way, is to click this dropdown arrow. And on the Values tab, click Get Values. And what this does is SAS is going to look at the input cars table and provide us with a list of values for the type columns, so the unique values that are in the type column. There's a sedan.

I'll go ahead and click sedan. And my filter is good to go. So I'll only see cars where the type is equal to the sedan. I'll click Next. Step 2 is, again, just another property verification page. Everything looks good. So I'll click Finish. One last step is to go ahead and sort our tables. So I'll click on the Sort Data tab. And let's go ahead and start on that new column that we created profit. So this time, I'll just double-click on profit. That gets added to the Sort Data tab. The default sort direction is ascending. I'm going to go ahead and use a dropdown arrow and change that to descending. So I'll see the cars with the highest profit listed first, and then it'll go down from there. That is everything that I wanted to do in this query builder. So let's go ahead and click Run. And let's take a look at our new output table. All right, so here's our new output table. I only see the columns that I chose. You'll see the profit column is the difference between the MSRP and invoice columns. And this was sorted by the profit column. So I see those rows or those cars that had the higher profit values listed first. And then it goes down from there. And of course the filter I applied where is equal to the sedan.

It looks pretty good. Now since this is a new output table, I could take this output table and use it as an input to other analytical tasks to further analyze, let's say, the profit of these sedan cars. One last thing I'd like to show you is to go ahead and click on the Code tab. These point-and-click tasks or querying tools that we use in Enterprise Guide generate SAS code behind the scenes. And here we can actually see that SAS code. If you want to make further modifications, you can always click on Modify Tasks to go back into the task itself. Or you can make a copy of the code that you see here and add in some of your own code if you'd like as well. So that's a little bit about the query builder to help us manipulate our data. In this next demonstration, I'd like to show you a different point-and-click task. This time, we'll see how to create graphs, specifically bar charts using the bar chart task. We'll see how we can really customize the look of the bar chart, how to add code into the task-generated code. And also how to modify the output format.

Now for this demonstration, I'm going to start off where we left off in the previous demonstration, which is where we used the query builder. In the query builder, we created the car's profit query, and we subsetted the cars table to create cars_profit. Now in cars_profit, I'm only looking at sedans. And I also calculated the profit value of each car. What I'd like to do in this demonstration is to create a bar chart based off of this table. I would like the bar chart to show the average profit value of the carmaker. And I'd also like to have separate bar charts for each origin value. So one for Asia, one for Europe, and then one for the USA. Now to get started on this bar chart, I'm first going to make sure that I have the car's profit table opened up.

If you closed out of it, in the project pane you can double-click on the cars_profit query. And that will open up that cars_profit table for you. With the table open, I'm going to go to the Task pane, expand the Graph category, and then double-click on Bar Chart. This will start the bar chart task with the cars_profit table as input. Now the first thing to do in the bar chart task is to specify what type of bar chart I'd like to create. I would like a vertical bar chart with different colored bars.

I'll select Vertical Colored Bar. Next, I'm going to go to Data in the Selection pane. In Data, I can verify the input table, so that's cars_profit. And if I click on Edit, I can actually apply a simple filter to my input table. So I already applied a filter to my table from the query builder, so I won't need to do that here. But just know that you have that option available.