JMP Essentials. Curt Hinrichs
the Data Preview panel. Once you are satisfied with the adjustments you have made, select Import.
Importing a Text File
If you are importing a text file, another handy wizard is included in the Data with Preview file option. Like the Excel Import Wizard, this wizard enables you to view your data and specify how you want it to appear before importing it into a JMP data table. It also provides options to convert your text file if it is delimited by commas, tabs, or spaces:
1. Select File Open.
The Big Class_L.txt file, illustrated here, can be found by selecting C: Program Files SAS JMP 15 Samples Import Data Big Class_L.txt.
2. Select Text Files (*.txt, *.csv, *.dat, *.tsv) in the Files of Type drop-down menu. Select the file.
3. Select Data (Using Preview) (Figure 2.8).
Figure 2.8 Data Using Preview
4. Select Open.
5. Choose the settings that you may need to structure your data for import. (See Figure 2.9.) Click Next and then Import.
Figure 2.9 Text Data Preview
Importing a Database File
Options to import data extracted from a database are available through ODBC within JMP. To access this data, first connect to the database (the data source should already be defined) and then specify the table of interest. You can also create custom queries of your data using Query Builder (explained in the next section) or the Advanced button. If you need more help defining your data source, select Help JMP Documentation Library Using JMP Ch. 3 Import Data from a Database.
1. Select File Database Open Table (Figure 2.10).
Figure 2.10 Database Open Menu
2. The Database Open Table window appears. (See Figure 2.11.) It prompts you to connect to your database and either open a data table or specify a query. Clicking the Connect button launches the Select Data Source window to locate and connect to your database.
Figure 2.11 Database Open Dialog Box
3. Locate the table of interest, highlight it, and click Open Table to import the data.
| Note |
| Note: You have the option of directly importing database files by selecting File Open as previously discussed (assuming that these programs are installed). Using this more direct option enables you to import only a single table. |
Creating a Database Query Using Query Builder
While importing a database file is a quick way to bring in a single table, there are times when you want to import from more than one file at a time. A database query enables you to combine data tables, aggregate data, sort data, and filter data. Once the query is created, it can be saved and reused for future queries.
In other software environments, you would need to know SQL (Structured Query Language) to create such queries. However, the Query Builder in JMP enables you to create a query in an easy, point-and-click environment that creates the SQL code for you. As with importing a single database file, you must first connect to the database. The data source should already be defined. Once the connection is established, you can choose the data tables of interest and specify the parts of the data tables that you want to extract into your resulting JMP table.
1. Select File Database Query Builder.
2. Choose New Connection and connect to your database (this example is for illustration purposes only since you will be unable to connect to this database).
For this example, you want to create a data table with information about historical SAT scores for states in the South and Southwestern regions. You would like to include the following information in your data table:
● From the data table SATByYear: the average SAT Math and SAT Verbal scores for each state.
● From the data table SATStateInfo: the Student/Faculty ratio for each state.
● From the data table StateToRegion: the Region each state is assigned to.
1. Select the SATByYear data table and assign it as the Primary table for the query.
2. Select SATStateInfo and StateToRegion data tables and assign them as the Secondary tables for the query. (See Figure 2.12.) Note that the column State appears in each data table and is designated as a “key” variable. Key variables are automatically used as the matching column to join data table in the query.
Figure 2.12 Database Query
3. Select Build Query.
4. In the Tables panel, select the SATByYear data table.
5. Choose the State, SAT Verbal, and SAT Math from the Available Columns panel and click the Add button. (See Figure 2.13.) (Because these are small data tables, you might want to turn on the Update preview automatically option. This option can slow performance down if the data tables are very large.)
Figure 2.13 Database Query Building
6. Since you are interested in the average SAT scores for each state, change the Aggregation for the SAT Math and SAT Verbal columns to Average. Note that Group By was automatically turned on for State. Examine the sample shown in the lower panel. You have one row for each state. (See Figure 2.14.)
Figure 2.14 Aggregating Data
7. Select the SATStateInfo data table in the Tables panel. Add the Student/Faculty Ratio column to the included columns.
8. Select the StateToRegion data table in the Tables panel. Add the Region column to the included columns. (See Figure 2.15.)
Figure 2.15 Defining Included Columns
9. To include only the states in the South and Southwestern regions, select Region in the Included Columns list and drag it to the Filter pane on the right side of the window.
10. Control-click to select both South and Southwest in the Filters list.
11. Click Save to save the query as a .jmpquery file for use later.
12. Click the Run Query button to create the new data table. (See Figure 2.16.)
Figure 2.16 Resulting Data Table
Creating a JMP Data Table from Scratch
1. Select File New Data Table to create a new data table (Figure 2.17):
Figure 2.17 Creating a New Data Table
2. Double-click on the first column’s heading and type the column name (the variable name).
3.