Hi, everyone.
Welcome to the Analyzing Census Data in SAS Studio Tutorial series.
In the previous video, I used data.census.gov to
download data on median home values in each state.
I also prepared this data in Excel for import.
So, in this video, let me show you how easy it is to import this data
into SAS Studio using the Import Data utility.
If you want to follow along with me,
make sure you watch the introduction video to
set up your environment and tutorial data.
You can find a link to it below or on the main tutorial page.
Let's get started.
So I've opened up SAS Studio, and I am
using SAS Studio through On-Demand for Academics, which is free for learners.
But, again, if you're using your own installation of SAS Studio.
That is totally fine as well.
Just to introduce SAS Studio a little bit here,
SAS Studio is a browser-based, SAS programming interface.
And it can connect to a local or hosted SAS server.
You can write your own code if you would like to do that or you can use the interface for
these tasks that will generate SAS code for you automatically. To show you the SAS Studio
interface a little bit, on the left hand side, we have our navigation pane. This is how we'll access
all of our files. And on the right hand side, we have the work area. The work area is tab-based. So
as we open up files or we try out a task, you'll see new tabs open up in that work area.
So now for importing. Before I can actually import that Median Home Value Excel workbook,
I need to upload it to the SAS server where the processing will occur. So that's my first step.
In the Server Files and folder section in the Navigation pane, I'm going to select the
Census Data Analysis folder because that's where I want to upload my file.
I'll click on the Upload icon with that arrow pointing upwards. I'll select Choose Files.
And I'm going to grab that Median Home Value Excel file I created in the previous video.
Click Open, and then Upload.
So, now, if I go ahead and expand that Census Data Analysis folder, I should now see my Median
Home Value Excel file. Now, if you didn't follow along with me in the previous video
where we accessed data from data.census.gov and prepared it and Excel, no worries at all. In your
Census Data Analysis folder, you should see the Median Home Value underscore s dot xlsx file,
and you can use that file instead. I'll, again, work with Median Home Value.
To start the importing process, I will right-click the file name, and select Import Data.
This is going to open up the Import Data utility on a new tab in the work area.
Now, you'll notice that the default view is split where I see the settings for the
utility on the top and the code and the results that are generated on the bottom.
I'm going to click on the Settings view just so I can focus on the settings for now.
Now, I will mention that in this specific tutorial, I'm importing in an Excel file,
and in order to use the Import Data Utility to import an Excel file, I need to have a license
for SAS Access Interface to PC files. That is included with SAS On-Demand for Academics which,
again, is free. We just want to make sure that you have that available to you.
So let's jump in with our settings here. The first part I see, I can see that we are importing in the
Median Home Value Excel file, and I can type in a worksheet name. By default though, the import data
utility is going to import in the first worksheet. Our Excel file only has one worksheet in it,
so we don't need to type in the worksheet name here. I can skip over that.
Next, moving on to the output data section. Whenever we perform an import, essentially,
what's happening is we have this Excel file,
and the import is creating a copy of that Excel file as a SAS table. And I specify a name
and location for that new SAS table that I'm creating. So I'll click Change.
For the library, where I want to save this, I'm going to select the Census
library that we created. Remember this is a pointer to our Census Data Analysis folder.
And in the data set box, I will call this file Median Home Value, and click Save.
Moving down to Options, this File type dropdown list. You can actually leave this at the default
option because SAS can figure out what that file type is based on the extension of the file,
but I'm going to go ahead and select it to be explicit. So using the dropdown this,
I'll select XLSX because we have a Microsoft Excel workbook.
Finally, you'll notice this checkbox Generate SAS variable names is already selected by default.
With this option selected, SAS will generate SAS column names from the data values in the
very first row of the Excel file, which is why I went through the process in the previous video
to give them proper SAS column names. And that is it.
Before I run this utility, I am going to change my view to Code/Results.
And I'd like to show you that all the Code tab here, you can actually see the SAS code that was
generated behind the scenes. I didn't have to type this myself. Just by selecting the options on the
settings, SAS generated this for me. Let's give it a run. I'm going to click on the
running man icon, Run, or if you'd like, you can select F3 on your keyboard as an alternative.
Taking a look at the Results tab, which is open here by default,
this shows us the attributes of the new SAS table that was created. Take a look at the
Data Set Name field. Notice the name of this table is census.medianhomevalue.
This two-level naming convention is used whenever we create tables and libraries. So
we first have the library name census dot the name of the table, median home value.
There's other pieces of information here, like the number of observations, or rows, 52,
number of variables, or columns, for. But I'm going to move on to the Output Data tab.
So I can actually see what that data looks like. I see four columns here. I see the names of GEO_ID,
State, MedianHome, and the MedianHomeMOE. And you might remember that these were in the very first
row of that Excel file. And SAS automatically used those as the column names as we see here.
I can see right above the table that, again, there are 52 rows and 4 columns.
And now I have those median home values along with the margin of errors for all states
available to me as a SAS table in SAS Studio.
Now, what I like to do is explore this table a little bit more. But before I do that, I do want
to point out one other tab we have, which is the Log tab. The log term is where we view the SAS
log. And the log essentially displays messages that are returned back from SAS.
There were no errors or warnings, which is good. But we do have a couple of notes here.
If you expand the notes section, you can click on these notes to jump to where that note
occurred within the log itself. But there are no notes that are of concern in this example.
Let's go back to the Output Data tab and explore this table a little bit more by
modifying the view of this table.
So let's start with the columns area. You'll notice in the columns area, by default,
all columns are selected. I just want to focus on a couple, so I will clear the check boxes for
GEO_ID, and then also MedianHomeMOE. So now, I'm just viewing State and MedianHome.
If I want more information about a particular column, for example, its attributes,
I can select the column in the columns area. So I'll select MedianHome.
And on the bottom I see those attributes. For example, I can see that this is a numeric column.
It actually has a format applied to it. It's the NLMNY15 dot format. You can think of this as the
national language monetary format. What this means is that, as a numeric column,
behind the scenes, the stored value only contains the numbers and the decimal point,
for example. But, by having this NL Money 15 dot format applied,
we are actually viewing, or this value is being displayed with the local currency. Now, in our
example, that means there will be a leading dollar sign. There will be commas separating each set of
three digits. No decimal places, all within the total allotted width of 15 we see here.
And that's why we're able to see this value with the dollar signs just like a currency.
Reason why this was automatically used for us was, remember, whenever I was in Excel,
I applied a format in Excel. And whenever I did that and import the table in, an equivalent is
automatically applied in SAS Studio, which is, again, the NL Money 15 dot format.
Let's go ahead and further modify the view of this table. Maybe I'm interested in only
looking at the states with a median home value greater than $300,000. What I can do is right
click the column I want to apply a filter on, MedianHome, and then select Add Filter.
First, instead of equality, again, I'm interested in it being greater than $300,000. So I shall go
with greater than, or equal to, select that symbol. And then type in 300000.
You want to make sure that you type this value without any dollar signs or commas because
we want to match the unformatted stored value. Click filter. You see the view has been updated,
and now I see that there are 11 states that satisfy that criteria.
Last thing that I'll do here is I'd like to go ahead and sort by the Median Home Values as well.
So, like I did earlier with adding the filter, I'll right-click the Median Home Value column,
the column heading again, and then I'll go with sort descending. So this now makes it easy to
see that the state with the highest Median Home Value was Hawaii with roughly $631,000.
And, again, there were 11 states that had Median Home Values greater than $300,000.
Now, I would like to mention here that any of these customizations that we're
applying to this table in the table viewer, like sorting our rows or applying any sort of filter,
these are not saved with the table itself. However, what SAS is doing behind the scenes
as we're selecting these options is it's actually generating SAS code that we can use.
If you'd like to take a look at that code, you can click on this button right here that says
display the code that creates the current table. When I click on that, a new program tab opens up,
and it displays the code that was used to generate that specific view of the table.
So if you want a starting point, you're able to use this code right here.
I'll go ahead and close this Program tab. Now, let's go ahead and remove those customizations
that we applied and go back to the original view of the table. So, first thing I'll do,
is next to where it says filter, and I see Median Home Value greater than or equal to 300,000.
I'll click on the X, Clear Filter to remove the filter, and now I see all the rows.
Now, to sort it back in the original order, I will right click MedianHome,
the column heading again. This time select Sort by Data Order,
and if I want to bring back the columns, I can go back to the columns area and select those. But,
instead, I'm going to click on the Refresh icon, and now I have all of my columns again.
At this point, I am done. So I'm going to go ahead and close the Median Home Value tab.
And I am not going to save these changes. It's important to keep in mind that it is not necessary
to save the settings that we specified in that import data utility to save the imported data.
That imported data was actually saved just by running the utility and creating the
output table Census dot MedianHome. And I can actually take a look at that table.
I can take a look at the library section in my navigation pane, expand My Libraries, Census,
and there we have it. I can actually see the Median Home Value table is available to me.
Remember, again though, that census library is just a pointer, or shortcut, to the Census
Data Analysis folder. So if I go to Server Files and Folders, and look at my Census Data Analysis
folder, I should see that same Median Home Value SAS table available in here as well.
You've seen how to import census data into SAS Studio. Now, it's your turn to try this out
with the practices in the tutorial notes. If you want to take this process a step further,
the challenge practice will show you how you can transpose, or restructure,
the imported table with the Stack/Split Columns task. Thanks for watching.