Opening an Excel file in JMP will activate the Excel Import Wizard (Figure 4.1 Excel Import Wizard Showing Cost of Smoking Data), which allows you to control which worksheets are imported and to make adjustments such as selecting the number of rows that contain the column headings. Examine the Data Preview to make sure the columns are delimited correctly.

If there is more than one worksheet in the Excel file, select the sheets to import into JMP by highlighting them in the Worksheets portion of the Excel Import Wizard dialog. Once the data has been imported into JMP make sure that the data and modeling types are set appropriately for your analysis. For example, the “Data Value” column contains the smoking costs, which should be treated as a number. Right click in the “Data Value” column heading and select Column Info to view a dialog that shows the data and modeling types (See Figure 4.2 Column Info Dialog).

As imported from the Excel file, “Data Value” was formatted as character. Use the drop-down menus to change Data Type to Numeric and the Modeling Type to Continuous.

The STATE System exports the data in a format that is amenable to database operations. Some modifications will be required to facilitate JMP analysis.

For each state there are six rows corresponding to the six different types of health care expenses for 2009. This is a database normalized form that facilitates summing the different types of expenses and adding new categories of expenses. This is a best practice in database design as it allows flexibility in the number different types of expenses that are recorded.

JMP data sheets are designed to have variables in columns and observations in rows. For this analysis each type of expense should be in a separate column. This can be accomplished by selecting Tables >Split. Enter “Variable” into the Split By field, “Data Value” into the Split Columns field, and select the Keep All radio button. The completed dialog is shown in Figure 4.3 Tables Split Dialog.

The JMP data table will now have a separate column for each type of expense.

The STATE System outputs several fields that specify the units, data source, and other metadata about the health care expenses. This method of documenting data values is a database management best practice. For example, merging the numeric value of expense and its units into one field would not allow mathematical operations to be performed.

Some of this metadata should be transferred to the JMP column headers, recorded as a JMP Column Property (Note) or included in the analysis narrative. The health care costs are given in millions of dollars and it is important that these units appear in the headings and legends of tables and graphs created from this data. This can be achieved by adding this information to the JMP column headings. Once incorporated into the JMP column headings, some of these metadata columns can be deleted.

The STATE system gives footnotes for the Other and Total expenditures. A JMP Note is a good place to retain this information. A Note can be attached to each column as a Column Property (right click on column name "Other Expense ($ mil) " > Column info> Column property> Notes), as shown in Figure 4.4 Adding Notes in the Column Properties Dialog for the Other expenditures.

The edited file is saved in JMP format as SmokingHealthcareExpenditures.jmp. The new JMP column names are given in the Data Definition section of this case.