A Gentle Introduction to Statistics Using SAS Studio. Ron Cody
alt="Figure 1.1 Some JMP Help Options"/>
The first row of the worksheet contains column names (also known as variable names). The remaining rows contain data on three students (yes, it was a very small class). The worksheet name was not changed so that it has the default name Sheet1.
The first step to import this data into a SAS data set is to double-click the Import Data task.
Figure 5.5: Double-Clicking the Import Data Task
You have two ways to select which file you want to import. One is to click the Select File button on the right side of the screen—the other method is to click the Server Files and Folders tab in the Navigation pane (on the left), find the file, and drag it to the Drag and Drop area. Depending on how you set up your SAS Studio session, you might find your files under Folder shortcuts then myfolder.
Using the first method and clicking Select File, brings up a window where you can select a file to import. Here it is:
Figure 5.6: Clicking on the Select File Button
Select the file that you want to import and click Open. This brings up the Import Window:
Figure 5.7: The Import Window
Use the mouse to enlarge the top half of the Import window or use the scroll bar on the right to reveal the entire window. The figure below shows the expanded view of the Import window:
Figure 5.8: Expanded View of the Import Window
The top part of the window shows information about the file that you want to import. You can enter a Worksheet Name (if there are multiple worksheets). But because you only have one worksheet, you do not have to enter a worksheet name.
The OPTIONS pulldown menu enables you to select a file type. However, if your file has the appropriate extension (for example, XLSX, XLS, or CSV), you can leave the default actions (based on the file extension) to decide how to import the data.
Because the first row of the spreadsheet contains column names, leave the check on the “Generate SAS variable names” option. This tells the import utility to use the first row of the worksheet to generate variable names.
You probably want to change the name of the output SAS data set. Clicking the Change button in the Output Data area of the screen brings up a list of SAS libraries (below).
Figure 5.9: Changing the Name of the SAS Data Set
The WORK library is used to create a temporary SAS data set (that disappears when you close your SAS Session). For now, let’s select the WORK library and name the data set Grades. Click the Save button at the bottom of the screen to complete the file selection process.
When all is ready, click the Run icon (Figure 5.10).
Figure 5.10: Click the Run Icon
You are done! Here is a section of the results.
Figure 5.11: Variable List for the Work.Grades SAS Data Set
Here you see a list of the variable names (note: you may have to scroll down through several pages to see this), whether they are stored as numeric or character, along with some other information that we don’t need at this time. Notice that the import utility correctly reads Name as character and the other variables as numeric.
Listing the SAS Data Set
A quick way to see a listing of the Grades data set is to select the Libraries tab in the navigation pane, open the WORK library, and double-click Grades. It looks like this:
Figure 5.12: Data Set Grades in the Work Library
You can use your mouse to scroll to the right to see the rest of the table. To create a better-looking report, click the Tasks and Utilities tab of the navigation pane and select Tasks, then Data, followed by List Data. (See Figure 5.13.)
Figure 5.13: The List Data Task
Double-click List Data and select the Grades data set in the WORK library in the Data selection box. Then click the Run icon. You will be presented with a nice-looking list of the Grades data set. (See Figure 5.14 below.)
Figure 5.14: List of the Grades Data Set
Importing an Excel Workbook with Invalid SAS Variable Names
What if your Excel worksheet has column headings that are not valid SAS variable names?
Valid SAS variable names are up to 32 characters long. The first character must be a letter or underscore—the remaining characters can be letters, digits, or underscores. You are free to use upper- or lowercase letters.
As an example of a worksheet with invalid variable names, look at the worksheet Grades2 shown in Figure 5.15.
Figure 5.15: List of Excel Workbook Grades2
Most of the column headings in this spreadsheet are not valid SAS variable names. Six of them contain a blank in the middle of the name, and the last column (2015Final) starts with a digit. What happens when you import this worksheet? Because you now know how to use the Import Data task, it is not necessary to describe the import task again. All you really need to see is the final list of variables in the data set. Here they are:
Figure 5.16: Variable Names in the Grades2 SAS Data Set
SAS replaced all the blanks with underscores and added an underscore as the first character in the 2015Final name to create valid SAS variable names. Note: the option to use column labels as column headings was deselected so that you could see the actual variable names.
Importing an Excel Workbook That Does Not Have Column Headings
What if the first row of your worksheet does not contain column headings (variable names)? You have two choices: First, you can edit the worksheet and insert a row with column headings (probably the best option). The other option is to deselect “Create Variable Names” in the OPTIONS section in the Import Window (see Figure 5.17) and let SAS create variable names for you.
Figure 5.17: Uncheck the Create Variable Names Option
Here is the result:
Figure 5.18: Variable Names Generated by SAS