Contents
About Importing Data to JMP
You can import many file formats into JMP and save them as data tables. JMP opens many files by default. The file formats which JMP does not support by default require specific Open Database Connectivity (ODBC) drivers.
The Following File Formats Are Supported by Default:
• Comma-separated (.csv)
• .dat files that consist of text
• ESRI shapefiles (.shp)
• Flow Cytometry versions 2.0 and 3.0 (.fcs)
• HTML (.htm, .html)
• Microsoft Excel 1997–2003 (.xls)
• Microsoft Excel 2007 (*.xlsx, *.xlsm) (Windows without an ODBC driver installed)
• Minitab (.mtw, .mtp, but not .mpj)
• Plain text (.txt)
• SAS transport (.xpt, .stx)
• SAS versions 6–8 on Macintosh (.sas7bdat, .ssd, .ssd01, .saseb$data)
• SAS versions 6–9 on Windows (.sd2, .sd5, .sd7, .sas7bdat)
• SPSS files (.sav)
• Tab-separated (.tsv)
The Following Files Require ODBC Drivers:
• Database (dBASE) (.dbf, .ndx, .mdx) is supported with a V3+ compliant ODBC driver.
• Microsoft Access Database (.mdb) is supported with a V3+ compliant ODBC driver.
• On Macintosh, Microsoft Excel 2007+ (.xlsm, .xlsx, .xlsb) is supported with at least a version 3 compliant ODBC driver installed on the machine. 64-bit JMP requires a 64-bit ODBC driver.
See Import Data from a Database for details for working with databases.
Your computer’s available memory affects data import. Very large files might load slowly or not at all. Consider splitting up large files before importing them. In JMP, you can then join or concatenate the tables. For more information, see Concatenate Data Tables in Reshape Data and Join Data Tables in Reshape Data.
Note: You can open R code (.R) and SAS program files (.sas) in JMP, but the text opens in a Script window, not in a data table.
Import Text Files
You can open text files with the extensions .txt, .csv, and .tsv, and the text is converted to a data table. Files with the .dat extension that consist of text are also supported. Text files can be delimited using almost any character, or they can be fixed-width files.
To adjust import settings, choose from one of the following options:
• Select File > Preferences > Text Data Files to change the import settings so that JMP determines the best way to structure and format the data table.
• Manually select the import settings as you open the file (described in this section).
• Open the file in the Script Editor, edit the content, and then import the content. This option is helpful when you need to add text delimiters or modify the text.
To import a text file:
1. Select File > Open.
2. On Windows, you can set the file type to Text Files.
3. Select the text file that you want to open.
For information about the options, see Table 3.1.
|
|
Automatically Determining Data Arrangement
|
Manually Specifying Data Arrangement
|
|
Windows
|
1. Select File > Open.
2. From the list next to File name (or the Files of type list on Windows XP), select Text Files.
3. To use the import rules from the preferences, select Data, using Text Import preferences. (See Text Data Files in JMP Preferences.)
To have text import use its best guess to arrange the data, select the Data, using best guess option.
(Optional) Select the Select this filter the next time this dialog is invoked option to apply the filter that you chose by default.
4. Select the file that you want to open.
5. Click Open.
Tip: The JMP Home window provides a shortcut to the above steps if you recently opened the file. Right-click the file in the Recent Files list and select Import (Preferences) or Import (Best Guess). (Your import preference is bolded in the right-click menu.)
|
1. Select File > Open.
2. From the list next to File name (or the Files of type list on Windows XP), select Text Files.
3. Select Data with Preview next to Open at the bottom of the window.
4. Select the file that you want to open.
5. Click Open.
6. Complete the Text Import window. See Text Import Preview Options, for details.
7. Click Import.
Tip: The JMP Home window provides a shortcut to the above steps if you recently opened the file. Right-click the file in the Recent Files list and select Import (Preview).
|
|
Macintosh
|
1. Select File > Open.
2. Select the file that you want to open.
3. From the Open As field, select Data (Best Guess) or Data (Using Preferences).
4. Click Open.
|
1. Select File > Open.
2. Select the file that you want to open. From the Open As field, select Data (Using Preview).
3. Click Open.
4. Complete the Text Import Preview window. See Text Import Preview Options, for details.
5. Click Import.
|
Note: On Windows, JMP can open text files in your computer’s default text editor. Select File > Open, and then select All Files (*.*) from the File name list (or the Files of type list on Windows XP). Select the text file, and then select Use default program to open. Uncheck to open as text.
For details about importing text from a Script window, see Import Text from the Script Window.
Text Import Preview Options
When you open a text file that JMP supports, JMP can show a preview of the text before opening the file as a data table. This option lets you manually arrange and format the data. For example, you can specify the end-of-line character or strip quotation marks.
JMP detects the file’s structure and shows options for importing text with either delimiters or fixed width fields. If JMP chooses the wrong file structure, click the Delimited fields or Fixed width fields radio button to import the data as the correct format. (For example, the fixed width window might appear when your file is actually delimited.)
The text import preview options are shown in Figure 3.2 and Figure 3.3.
Figure 3.2 Text Import Preview for Fixed Width Files
Figure 3.3 Text Import Preview for Delimited Files
Charset
Select the character set used in the imported file, or let JMP detect the character set. If incorrect characters are displayed in the imported file, open the file again and select another character set.
End of Field
(Available only in the Delimited Import window) Select the check boxes beside the character that marks the end of a field. Alternatively, select the check box beside Other and enter a character if the appropriate character is not listed.
End of Line
(Available only in the Delimited Import window) Select the check boxes beside the character that marks the end of a line (row). Alternatively, select the check box beside Other and enter a character if the appropriate character is not listed. Note that when JMP finds double quotation marks, the delimiter rules change to look for an end double quotation mark. Other text delimiters, including spaces embedded within the quotes, are ignored and treated as part of the text string.
File contains column names on line
Tell JMP where to find data to use as column names. For example, if the column names in your text file are on line (row) 3, select this option and type 3 in the check box. Otherwise, JMP uses the data in the first line of the imported file as the column name in the JMP data table or takes the first line as data.
Data starts on line
Specify the number of the first line that contains data.
Number of Lines
Specify the number of lines (rows) that you want to import.
Strip enclosing quotation marks
Available only on fixed-width imports. Select this check box when you want JMP to remove quotation marks that enclose data in the text file.
Two-digit year rule
Specify how year numbers are displayed. Select the 100-year range in which your dates fall. For example, if the earliest date is 1979, select 1970-2069. If the earliest date is 2012, select 2000-2099. If dates span centuries, you must recode the dates with four-digit years before importing the data.
Recognize apostrophe as quotation mark (not recommended)
(Available only in the Delimited Import window). Use this option only if your data comes from a nonstandard source that places apostrophes around data fields rather than quotation marks.
When you are finished selecting the settings, click Next. The next window shows each column’s modeling type. To change the default modeling types, do one of the following:
• Click on the data type icon to change the data type from numeric (
) to character (
). Clicking the icon cycles between the modeling type and exclude (
). Exclude means that the column is not imported.
) to character (). Clicking the icon cycles between the modeling type and exclude (). Exclude means that the column is not imported.• To change a numeric column’s data format, select the format from the red triangle menu.
• Click on the column heading to modify the text.
The top of the Text Import window shows a preview of the text file as it appears when imported into a JMP data file. Click the Import button to import the data.
Figure 3.4 Text Import Preview Window with Column Options
6. When you are finished, click Import to complete the text import.
Open a Text File in a Text Editing Window
You can open a text file in a Script window, where you edit the text. Then you can import the text as a data table. This feature is helpful when you want to reformat the text before importing it as a data table. For example, you might need to insert the correct delimiters or modify the text.
Another option is opening a JMP add-in definition (.def) file as text and then editing it in a Script window.
To open a text file in a text editing window (Windows):
Files that you recently opened are listed in the JMP Home window. For most files, right-click the text file and select Open as Plain Text to open the file in a text editing window. JMP add-in definition files cannot be opened as plain text from the JMP Home window.
When you are opening the file for the first time, follow these steps:
1. Select File > Open.
2. Do one of the following:
– (Windows Vista and Windows 7) To open a JMP add-in definition file as text, select All JMP Files or JMP Add-In Files from the list next to File name (or the Files of type list on Windows XP). Click the Open button arrow, and then select Open as Plain Text. The file opens in a Script window. Skip the remaining steps.
– To open other text files, select Text Files from the list next to File name (or the Files of type list on Windows XP).
Figure 3.5 Select Text Files
3. (Optional) To set the default option file type to Text Files, select the check box beside Select this filter the next time this dialog is invoked.
4. Select the file.
5. Select Plain text into Script window next to Open as.
6. Click Open.
The text appears in a Script window.
To open a text file in a text editing window (Macintosh):
1. Select File > Open.
Figure 3.6 Opening a Text Document on the Macintosh
2. Select the file.
3. Select Text from the Open As list.
4. Click Open.
The text appears in a Script window.
For details about converting the text to a data table, follow step 3 in Import Text from the Script Window.
Import Text from the Script Window
You can import text from the Script window as a data table. The text can be in a table format (for example, from a Microsoft Word document or Web page) or in plain text format. This feature is helpful when you want to reformat the text before importing it as a data table. For example, you might need to insert the correct delimiters or modify the text.
JMP uses the import settings in the preferences to determine how to structure and format the text. Some options include removing quotation marks around text and specifying the rows that contain column headings and data. See Text Data Files in JMP Preferences for details.
Note: You can also import an entire Web page as a data table. See Import Remote Files and Web Pages for details.
This section describes how to import text that you paste into the Script window. For details about opening a text file in the Script window, see Open a Text File in a Text Editing Window.
To import text from the Script window:
1. Open a new Script window in JMP by selecting File > New > Script (Windows) or File > New > New Script (Macintosh).
2. Copy and paste the text into the Script window.
3. Do one of the following:
– To import all text from the Script window, select File > Import as Data (Windows) or Edit > Import as Data (Macintosh).
– To import specific text, select the text, and then select File > Import as Data (Windows) or Edit > Import as Data (Macintosh).
The text is imported into a JMP data table.
Import Remote Files and Web Pages
You import data from Internet sites, intranet sites, FTP sites, or other computers by selecting File > Internet Open. The file paths begin with the Uniform Resource Locators (URLs) http, ftp, or file; a drive letter; or the path to a network drive (relative or absolute).
You also use this feature to import a Web page on a local or network drive as a data table. Once the data table is created, there is a script called Source that you can run to re-import and refresh the data. If you open a Web page by selecting File > Open, the page opens in a browser, not as a data table.
SAS stored process reports open in an HTML view. From that view, you can import the file as a data table.
JMP inserts the location of the original data as a note in the data table. For example, the path Z:example.html is included as a note when you import example.html from the Z drive.
To open a remote file:
1. Select File > Internet Open. The window shown in Figure 3.7 appears.
Figure 3.7 Internet Open Window
2. Enter the URL.
3. Click the list under Open As, and select the option that specifies how you would like JMP to display the imported data:
Data Imports the file as a data table.
Web page Opens the Web page in a browser. (Select this option to import data generated by Web page scripts and server-side requests.)
Text Opens the file in a JMP Script window. In an HTML file, the HTML tags of a .html file are displayed.
4. Click OK.
One of the following occurs:
– The file opens as you specified.
– If you imported a Web page as data, a window appears that lists all tables on the page. Select the table or tables that you want to open, and then click OK. Each table opens in a new data table.
– If you imported a Web page as a Web page, select File > Import Data as Data Table in the browser. Select the table or tables that you want to open, and then click OK. Each table opens in a new data table.
– If the file is on an FTP server, the window in Figure 3.8 appears.
Figure 3.8 FTP Login Window
For an anonymous account, click OK. For an authenticated login, enter your user ID and password. The file then opens as you specified.
Note: Some anonymous FTP servers require a user ID. If the data table does not open, try typing either ftp or anonymous in the User ID text box. Leave the Password text box empty and click OK.
By default, SAS stored processes open reports in an HTML view.
To open a SAS stored process report as a data table:
1. In the HTML view, select File > Import Table as Data Table.
A window appears that lists the tables found in the Web page.
2. Select the table or tables that you want to import.
3. Click OK.
Each table is opened as a new data table.
See Run Stored Processes for more information about stored process reports.
Import SPSS Files
JMP opens SPSS files as data tables and maintains several SPSS features:
• General numeric and character data with minimal formatting are supported.
• SPSS date, datetime, and time formats are supported.
• By default, labels are converted to column headings. When you select this option, and the data contains no labels, the columns are named Column 1, Column 2, and so on.
You also have the option of selecting the conversion method for column headings when opening an SPSS file. The method that you select then overrides the preferences.
To change the default conversion method, select File > Preferences (or JMP > Preferences on Macintosh). On the General page, deselect Use SPSS labels for column names during import. Variable names are then imported automatically as column headings.
• The value labels that you defined in the SPSS file are saved as Value Labels column properties. The value label then appears in each data table cell instead of the original value. For details about Value Label properties, see Value Labels in Set Column Properties.
SPSS can assign certain values in a variable to be treated as missing for analyses. For example, the value 64 could be regarded as missing for a Height variable. Then, the calculation of the distribution of height would ignore values of 64. When you import SPSS into JMP, these values are included in the Missing Value Codes column property for the appropriate variable.
At the time of publication, custom currency formats selected in an SPSS file are not maintained on import. In addition, JMP does not read SPSS data that contains double-byte characters, such as non-Unicode Japanese characters.
Note: As with importing other files, you might experience a delay when opening and saving large SPSS files.
To open an SPSS file (Windows):
1. Select File > Open.
2. From the list next to File name (or the Files of type list on Windows XP), select SPSS Data Files (*.sav).
3. Select the SPSS file.
4. (Optional) To specify the column headings, select one of the following Set JMP column names from options:
– SPSS Labels (or Use Label Names for Column Names on Windows XP) creates column headings from SPSS labels.
– SPSS Variable Names (or Use Variable Names for Column Names on Windows XP) creates column headings from variable names.
5. Click Open.
JMP opens the file as a data table.
To open an SPSS file (Macintosh):
1. Select File > Open.
2. Select the SPSS file.
3. (Optional) To specify the column headings, do one of the following
– Deselect Use SPSS Labels as Headings to convert variable names to column headings.
– Select Use SPSS Labels as Headings to convert labels to column headings.
4. Click Open.
JMP opens the file as a data table.
Import Excel Files
When you open an Excel file in JMP, the file is automatically converted to a data table. JMP can automatically convert the first row into column headings. If you do not select this option, the columns are named Column 1, Column 2, and so on.
JMP can also automatically open each worksheet in the spreadsheet as a separate data table. You select this option in the preferences or when you open a spreadsheet. See General in JMP Preferences for details about Excel preferences.
JMP also opens Excel files from Web sites that do not require you to log in. On Windows, follow the procedure in this section. On Macintosh, use the File > Internet Open command. See Import Remote Files and Web Pages for more information.
To open an Excel file (Windows):
1. Select File > Open.
2. Select the Excel file type (Excel Files (*.xls, *.xlsm, *.xlsx)).
3. Select the file or enter the URL.
4. (Optional) To convert text in the first row to column headings, select Always next to Always enforce Excel Row 1 as labels (or Should Row 1 be Labels? on Windows XP). If you do not want to import specific worksheets, click Open.
5. (Optional) To open specific worksheets, do one of the following:
– (Windows 7 and Vista) Click the Open button arrow, select Open Selected Worksheets, select one ore more worksheets, and then click OK. You can also click Select All if you change your mind and want to import all worksheets.
– (Windows XP) Click Allow individual worksheet selection, click Open, select the worksheets, and then click OK.
The spreadsheet is opened as you indicated.
To open an Excel file (Macintosh):
Note: If the filename is grayed out, the required ODBC driver is not installed, so the file type is not supported. 64-bit JMP requires a 64-bit ODBC driver.
1. Select File > Open.
2. Select the file.
3. (Optional) To convert text in the first row to column headings, select Use Excel Labels as Headings.
4. (Optional) To open specific worksheets, select Select Individual Excel Worksheets.
5. Click Open.
If you chose to open specific worksheets, select those worksheets from the list, and then click OK. You can also click Select All if you change your mind and want to import all worksheets.
The spreadsheet is opened as you indicated.
Import Data from SAS
You can connect to a SAS server and work directly with SAS data sets:
• Import whole SAS data sets or portions of data sets
• Make changes to imported SAS data in JMP and then export those changes as a SAS data set
• Run stored processes
• Submit SAS code from JMP
Notes:
• On Windows, the Java Run-time Environment (JRE) 1.6 or later must be installed on your computer to access SAS. However, JRE 1.6 does not need to be specified as the current version.
• On Macintosh, JRE 1.6 or later must be installed for SAS integration.
Access SAS options from the File > SAS menu:
Browse Data
Browse and import data residing on a SAS Server.
Export Data to SAS
Export JMP data tables to a SAS Server.
Browse SAS Folders
Browse and run SAS stored processes or open Metadata-defined data tables.
SAS Add-ins
Opens a window with links to additional JSL and SAS programs available.
New SAS Program
Opens a script window for writing and submitting SAS code.
Submit to SAS
Sends SAS code directly from JMP to the currently active SAS server.
Open SAS Log Window
Opens a SAS log window for the active SAS server.
Open SAS Output Window
Opens a SAS output window for the active SAS server. This window shows recent SAS output.
Server Connections
Administer connections to SAS servers.
You can also find shortcuts for SAS options on the SAS page of the JMP Starter, and there is a SAS toolbar. You can save certain settings pertaining to SAS Integration on the SAS Integration page of the Preferences window (File > Preferences). For more information about setting your SAS Integration preferences, see SAS Integration in JMP Preferences.
Import SAS Data Sets
SAS data sets are saved in one of many SAS formats:
• Windows formats are .sd2, .sd5, .sd7, .sas7bdat.
• Macintosh formats are .sas7bdat, .ssd, .ssd01, .saseb$data.
When you open a data set in JMP, the file opens as a data table. JMP uses SAS variable names as column names by default. To use variable labels in a specific file on Windows, select the option when you open the file (see step 4 below).
JMP detects the data set encoding, so opening a UTF-8 file on a non-UTF-8 server does not affect the encoding.
To open a SAS data set:
Note: On Macintosh, you select File > Open, select the data set, and then click OK. Options are available only for SAS Transport (.xpt) files.
1. Select File > Open.
2. (Windows only) Select SAS Data Sets or Data Files from the list next to File name (or the Files of type list on Windows XP), as shown in Figure 3.9.
Note: SAS variable names and formats are preserved and can be saved after changes are made to the SAS data set. See Save as a SAS Data Set in Save and Share Data.
3. Select the file.
Figure 3.9 Open SAS Data Set
4. (Optional on Windows) Select any of the following options:
– Select this filter the next time this dialog is invoked Sets the default file type choice to the option that you select next to the File name list (or in the Files of Type list on Windows XP). If selected, the default file type will be SAS Data Sets the next time you reach this window.
– SAS variable labels (Windows Vista and Windows 7 only) Uses the SAS variable labels (instead of variable names) as the column names in the JMP data table.
– SAS variable names (or Use SAS Variable Names for Column Names on Windows XP) Uses the SAS variable names (instead of the labels) as the column names in the JMP data table.
(Optional) Select any of the following for a SAS Transport (.xpt) file:
– Select member Lets you enter the name of a specific member, or table, for JMP to open. On Macintosh, select Member Tables > Specified and then enter the name.
– Open all members Opens all members, or tables, in the transport file. On Macintosh, select Member Tables > All.
– Save all members Saves the file as a JMP file as soon as you open it. The file is saved to the same directory where the SAS transport file was opened. On Macintosh, the option is Save all.
– Select Columns Tells JMP to open only certain columns from the transport file. Select the columns that you want to import from the list that appears. On Macintosh, the option is Select columns before opening.
5. Click Open.
Note: If you are importing date variables from a SAS file, JMP looks for a SAS date format and translates it to a JMP date column.
Create SAS Transport Files in SAS
JMP can open SAS transport files that were saved using the SAS XPORT engine. For example, below is sample SAS code that creates a transport file called test.
Note: misc and work are SAS libref names.
data test;
input name $ age weight;
cards;
Susan 12 72
Melanie 10 68
Jonathan 11 77
Sheila 13 67
;
libname misc xport 'C:/test.xpt';
proc copy in=work out=misc;
run;
Connect to SAS
You can either connect to a SAS Metadata Server or directly to a SAS Workspace Server. Once connected to a SAS Metadata Server, you can browse through SAS servers, libraries, and data sets.
Note: The earliest supported release of the SAS Metadata Server is version 9.1.3 SP4. Connections to earlier releases of the SAS Metadata Server are experimental and are not supported.
To connect to a SAS server, click File > SAS > Server Connections. The SAS Server Connections window in Figure 3.10 appears. All connections are made in this window.
Figure 3.10 SAS Server Connections
Connect to a SAS Metadata Server
Note: You can be connected to only one Metadata Server at a time. If you make a second connection, your first one is disconnected.
To connect to a Metadata Server:
1. Select the version for the SAS Server. Your SAS Metadata Server administrator should have this information.
2. Select the profile that you want to use.
If you do not have a profile set up, see To create or modify a SAS Metadata Server profile:.
3. Click Connect.
If JMP is unable to establish a connection, an error message appears. Common reasons are invalid user names or passwords. If you need to update the information for the profile, see To create or modify a SAS Metadata Server profile:.
4. Click Close.
Once you are connected to a SAS Metadata Server, you can connect to any SAS Workspace Servers that the Metadata Server offers.
To connect to a SAS Workspace Server:
1. In the SAS Server Connections window, select the Workspace Server to connect to. See Figure 3.11. Note that connecting to SAS on your computer is available only on Windows.
Figure 3.11 Open a Connection to a Workspace Server
2. Click Connect.
Under Open Workspace Server Connections, the Workspace Server is shown as the current active connection. See Figure 3.12.
Figure 3.12 Current Active Connection
3. Click Close.
To change the active connection:
Note: The active connection is what is used to submit SAS code or handle SAS script commands.
To change the active connection, you first need to be connected to more than one server. Follow the instructions in To connect to a SAS Workspace Server: to add two or more server connections.
1. In the Open Workspace Server Connections section, click the drop-down menu and select the desired server.
2. Click Set as Active.
3. Click Close.
Tip: You can change the active server at any time.
To disconnect from a SAS Workspace Server:
1. In the SAS Server Connections window, select the Workspace Server to disconnect under Open Workspace Server Connections.
2. Click Disconnect.
To disconnect from a SAS Metadata Server:
1. In the SAS Server Connections window, select the Metadata Server to disconnect.
2. Click Disconnect.
To create or modify a SAS Metadata Server profile:
1. In the SAS Server Connections window, select the SAS Server Version.
2. Click Manage Profiles.
3. Click Add to add a new profile, or click Modify to change a profile’s settings.
The Create Profile or Modify Profile window appears. If you are adding a new profile, all fields are empty except the Authentication domain field, which contains DefaultAuth, and the Port field. If you are modifying a profile, the fields contain the current information.
Figure 3.13 Create or Modify a Metadata Server Profile
4. Fill in the information needed to connect to a SAS Metadata Server. Your SAS Metadata Server administrator should have this information.
Profile name
Select a name for this profile. This name is shown in the list of profiles.
Description
(Optional) You can enter a short description of this profile.
Machine
The name of the machine that hosts the Metadata Server. (Example: myserver.mycompany.com)
Port
The port through which you should connect to the machine. (Example: 8561)
User name
Your user name for the Metadata Server.
Password
Your password. This is always displayed as asterisks.
Authentication domain
The domain you, as a user, belong to.
5. Click Save.
Connect to a SAS Workspace Server on a Remote Machine
You can also connect directly to a SAS Workspace Server, instead of going through a Metadata Server.
To connect to a SAS Workspace Server:
1. Select File > SAS > Server Connections to open the SAS Server Connections window.
2. Under Establish New Workspace Server Connection, select Connect to remote SAS server on. See Figure 3.14.
Figure 3.14 Open a Connection to a Remote SAS Server
3. Enter the machine name and the port number. Your SAS server administrator has this information.
4. Click Connect.
5. Enter your user name and password in the window that appears.
6. Click OK.
7. Click Close in the SAS Server Connections window.
To disconnect from a SAS Workspace Server:
1. In the SAS Server Connections window, select the server to disconnect under Open Workspace Server Connections.
2. Click Disconnect.
Connect to a SAS Environment (Windows Only)
On Windows, JMP lets you connect to a SAS mid-tier (or SAS environment) if SAS Server version 9.3 is selected in JMP’s preferences and your computer or JMP has been configured correctly.
The SAS installer should have set up your computer to find the SAS environment definition file. If not, you can enter the path to the file in the JMP preferences.
To configure your JMP preferences:
1. Select File > Preferences > SAS Integration.
2. Select I want to connect to a SAS Environment and then click Configure.
3. To connect to an environment that JMP has already detected, click Automatic discovery, and then select the URL from the list if necessary.
4. To enter the path to the SAS environment definition file, click Manual configuration and enter the URL.
5. Click OK.
To connect to a SAS Environment:
1. Select File > SAS > Server Connections to open the SAS Server Connections window.
2. In the Metadata Server Connection area, select Connect to a SAS Environment.
If this option is not available, either your computer or JMP is not configured to find the environment. See To configure your JMP preferences: for details.
3. Select the name of the environment from the Environment list if necessary.
4. Click Connect.
5. Enter your user name and password if prompted.
Connect to SAS on Your Local Machine (Windows Only)
You can also connect directly to SAS on your local machine.
To connect to SAS on your computer:
1. Select File > SAS > Server Connections to open the SAS Server Connections window.
2. Under Establish New Connection, select Connect to SAS on this machine.
3. Click Connect.
4. Click Close in the SAS Server Connections window.
To disconnect from SAS on your computer:
1. In the SAS Server Connections window, select Local under Open Connections.
2. Click Disconnect.
Open SAS Data Sets through a SAS Server
Once you connect to a SAS Workspace Server, you can browse through the SAS libraries on that server and import data into JMP.
To browse the data sets on the SAS server, select File > SAS > Browse Data. The Browse SAS Data window appears. See Figure 3.15.
Figure 3.15 Browse SAS Data
The window is initially populated with a list of servers the SAS Metadata Server provides (if connected). Any physical and local connections are also shown (as listed in Open a Connection to a Remote SAS Server).
• Select a server to see a list of libraries that server contains.
• Select a library to see a list of data sets within that library.
• Select a data set to see a list of columns within that data set.
When you close and reopen the Browse SAS Data window, the previously viewed library and data set appear in the window. However, at any time, you can select a different server from the SAS Server list and then select a library and data set.
Tip: If a server is unavailable, or if the connections failed, the server’s name is shown in light, italic text. Click it to try to re-establish the connection.
Browse SAS Data Information
You can select a SAS data set and see information about its contents before opening it using the Get Details, Column Details, and Data Preview options.
Data Preview
When you select a data set, the Data Preview outline shows you the first ten rows and columns in the data set. See Figure 3.16.
Figure 3.16 Data Preview
Data Set Details
Click Get Details in the Browse SAS Data window to see the size and last modification date for each data set in the library. This option helps you estimate whether your computer can process the entire data set.
Column Details
To see information about a particular column in the data set, select it. The Column Details outline shows you some basic information about the data column. See Figure 3.17.
Figure 3.17 Column Details
Name
Column name from the SAS data set.
Label
Descriptive column label. The label can be longer than the name, and is often helpful to determine what the column name means.
Type
Specifies whether the column has a character or numeric data type.
Length
The length in bytes of data in the column.
Sort Order
How the column is sorted in SAS.
Format
The format for the SAS column, such as DOLLAR. This format field also contains information about the width of formatted values and the number of decimal places.
Open a SAS Data Set in JMP
You can import SAS data sets directly into JMP.
1. From the Browse SAS Data window, select a data set.
2. Click Import.
The SAS data set is imported into a JMP data table. When SAS data is imported, JMP attempts to make the best match to the SAS format.
If you want to import only a portion of a data set, you can do any of the following:
• Select a subset of the columns. See To select a subset of columns:.
• Construct a WHERE clause to filter the data. See To import using a WHERE clause:.
• Take a random sample of the data. See Importing a Random Sample of the Data.
To select a subset of columns:
1. Open the Import Options outline. See Figure 3.18.
Figure 3.18 Import Options
2. Click Select Columns.
The Select Columns window appears. See Figure 3.19.
Figure 3.19 Select Columns
3. Select the columns that you want to import.
To select more than one column at a time, press CTRL and click each column.
4. Click Add.
5. When you have added all the columns that you want, click OK.
6. In the Browse SAS Data window, click Import.
Only the columns that you selected from the SAS data set are imported into a JMP data table.
To import using a WHERE clause:
1. Click Where.
2. Use the WHERE clause editor to construct your WHERE clause.
3. Click OK to return to the Browse SAS Data window.
4. Click Import.
Only the data that matches your WHERE clause are imported into a JMP data table.
For information about constructing WHERE clauses and using the WHERE clause editor, see Use the WHERE Clause Editor.
Note: If you import data using both a WHERE clause and sampling, the WHERE clause is applied first, and then a sample of the filtered data is taken.
You can also write your own SQL statements.
To import using a custom SQL statement:
You can also open a SAS data set using a custom SQL statement.
1. Open the Custom SQL outline under the Import Options outline. See Figure 3.18.
Figure 3.20 Custom SQL
2. Enter your SQL statement in the window.
3. Click Execute Custom SQL.
Note: Your SQL is run on the selected server but is not restricted to any selected library or data set.
Importing a Random Sample of the Data
You can also import a random sample of the rows of the SAS data set.
Note: The sampling feature requires that the SAS server has the SAS/STAT product licensed and installed. If SAS/STAT is not present, sampling is disabled.
In the Sample Imported Data area of the Import Options outline, select the Import a random sample of the data set check box. By default, 5% of the rows are imported. To change the random sample import settings, click the Settings button.
Figure 3.21 Sampling Settings
In this window, you specify any of the following:
Sample Size
You can set the sample size be percentage or by number of rows. To ensure that each row is sampled only once, de-select the With replacement option. To ensure that any row can be sampled and appear more than once in the imported data, select the option.
Selecting by Column
You can select strata by moving columns into the Strata list.
Handling Multiple Row Sampling
If With replacement is selected, you can specify to either add each duplicated row as a separate row or combine all duplicated rows into one row. If the second option is selected, a column is added to the table that contains a count of how many times each row was sampled.
Setting minimum and maximum numbers of items selected
Select the option and enter a number.
Setting the random number seed
Select the option and enter a seed. Specifying the seed lets you reproduce the exact same sample multiple times.
Note: If you import data using both a WHERE clause and sampling, the WHERE clause is applied first, and then a sample of the filtered data is taken.
Import Options
There are additional options that you can use to specify how SAS data is imported into JMP.
Use labels for imported column names
When selected, this option switches the column name, which has a limited length and might be difficult to decipher, with the column label. This option is turned off by default. To use the SAS data column names as column names in JMP, uncheck this box.
Add SQL table variable to imported table
When selected, this option adds SQL queries to the data table panel as a variable. This option is turned on by default. If you turn off this option, only two variables are added when you import the data table: the SAS server and the data set.
Tip: If your data is password-protected, you might want to turn this option off, because your password might be shown in the SQL.
Table Variables
After you import the JMP data table, table variables appear in the upper left panel of the data table. These variables show the SAS server, data set, and the SQL query and sampling settings if applicable. There is also a source script added that lets you re-do the import at any time.
Open Password-Protected Data Sets
JMP can open SAS version 7 or higher data sets that are password protected. The passwords are not case sensitive.
To open password-protected data sets:
1. Select File > Open.
2. Select SAS Data Sets from the Files of type list.
3. Select the file.
4. Click Open.
5. Enter the password and then click OK.
When the password is incorrect, you are prompted to enter it again until you get it right.
Run Stored Processes
Stored processes are SAS DATA step code saved on the SAS server that you are connected to. You can run them from JMP and see the results of the script in JMP.
Note: Depending on the preferences that you have set for SAS, error messages are sent either to the JMP log or to a separate SAS log window.
You must be connected to a Metadata Server to view and run stored processes. If you select File > SAS > Browse SAS Folders without such a connection, you are prompted to either make a connection or cancel your action.
To select and run a stored process:
1. Select File > SAS > Browse SAS Folders.
The Browse SAS Folders window appears.
2. Browse through the stored processes to find the one that you want to run.
3. Select it and click Run.
The data opens as a JMP data table.
On Windows, you can also right-click a stored process and select Copy Metadata Path. This option copies the path to the clipboard. You can then paste it into a script window to include it as a parameter for the JSL operator Meta Get Stored Process(). For more information, see the Scripting Guide.
Note: Static graphs might not appear in the results returned from a SAS stored process when streaming output is selected.
Stored processes send reports to HTML by default, but you can select RTF or PDF instead on the SAS Integration page of the JMP preferences. Select File > Preferences (Windows) or JMP > Preferences (Macintosh) to view the JMP preferences.
Submit SAS Code
You can submit SAS code directly from JMP to the currently active SAS server. If the submitted SAS code generates SAS Listing output, that output is automatically retrieved from the SAS server and displayed in JMP. Also, the generated SAS Log is retrieved, and, if there are any errors in the submitted code, the SAS Log is automatically displayed in the SAS Log window.
Figure 3.22 SAS Code Submission Example
To run SAS code directly from JMP:
1. Either open an existing SAS program using File > Open, or create a new SAS program. (Create a new SAS program by selecting File > SAS > New SAS Program and typing in the SAS code.)
2. Click the Submit to SAS icon 
.
.You can also right-click in the Program Editor window and select Submit to SAS. The menu item also includes the name of the active SAS server that the SAS code will be submitted to.
You can also press the F8 key (press COMMAND-SHIFT-R on Macintosh).
To run SAS code using a JSL script:
Write and run a JSL script that uses either the SAS Submit or SAS Submit File JSL functions. For more information about writing JSL scripts that submit SAS code, see the Scripting Guide.
To view the SAS Listing output:
If the submitted SAS code generates SAS Listing (textual) output, that output is automatically be displayed in a SAS Output window when the job is completed. If you need to view the SAS Listing output again later in the JMP session, select File > SAS > Open SAS Output Window. The SAS Output Window retains the listing output from the previous 25 submits to the active SAS server.
To view the SAS log:
If the submitted SAS code contained errors, the SAS Log window for the active SAS server is automatically opened, displaying the SAS Log for the job. However, you can view the SAS Log for the most recent 25 submits to the active server at any time by selecting File > SAS > Open SAS Log Window.
If you prefer that SAS Log information is appended to the JMP log after a submit completes:
1. Select File > Preferences (Windows) or JMP > Preferences (Macintosh).
2. Open the SAS Integration category.
3. In the Show SAS Log section, select JMP Log rather than Separate Window.
Also, in the Show SAS Log section, you can set whether the SAS Log should be displayed Always, Never, or On Error (the default).
Generate ODS Results
The SAS Output Delivery System (ODS) is a powerful mechanism for generating reports in HTML, RTF, PDF, and other formats. ODS output is generally much more attractive and customizable than plain-text SAS Listing output. You can set your submitted SAS code generate ODS results rather than SAS Listing output using Preferences.
To generate ODS results from your submitted SAS code:
1. Select File > Preferences (Windows) or JMP > Preferences (Macintosh).
2. Open the SAS Integration category and find the large SAS Submit Options group, as shown in Figure 3.23.
Figure 3.23 SAS Submit Options in Preferences
3. Select the Automatically generate ODS results option.
4. From the ODS Result Format list, select the format in which to generate the ODS results: HTML, PDF, RTF, or a JMP report.
5. (Optional) You can use other options to specify a style or style sheet to format the results or set the format for generated graphics. For more details, see SAS Integration in JMP Preferences.
Performing the previous steps causes JMP to generate additional SAS code, including an ODS statement, that is wrapped around the SAS code that you submit. The SAS code that you submit then automatically generates ODS results in the specified format. Those results are downloaded to your computer and displayed either within JMP, when possible, or in an appropriate external application.
Retrieve Generated SAS Data Sets
SAS code that you submit might generate SAS data sets. You can have them automatically imported into JMP for further analysis.
1. Select File > Preferences (Windows) or JMP > Preferences (Macintosh).
2. Open the SAS Integration category.
3. Select the Import generated SAS data sets into JMP option.
Export JMP Data Tables to SAS
You can export JMP data tables to a SAS Workspace Server.
1. Connect to the SAS Workspace Server.
2. Select File > SAS > Export Data to SAS.
If necessary, you are connected automatically using your profile’s user name and password.
Figure 3.24 Export Data to SAS
3. Select the data table that you want to export to SAS from the list of open data tables under Select Data to Export.
4. (Optional) To export only some of the columns in the data table, click Select Columns. See To select columns to export: for details.
5. Select the Destination Server.
6. Select the Library.
Tip: If your libraries do not appear, see Show Libraries in the Export Data to SAS Window.
A list of the data sets in the library appears.
7. Enter the name as you want it to appear in the SAS library.
8. (Optional) Set the export options that you want to use. See Export Options for details.
9. Click Export.
To select columns to export:
1. To export only some of the columns in the data table, click Select Columns.
2. In the window that appears, select the columns to export and click Add.
3. When all the columns have been added to the Selected Columns list, click OK.
Export Options
The available export options are as follows:
Ignore ‘excluded’ row state (export all rows)
Select this option to export all rows in the data table. Deselect this option to export only those rows that are not excluded. This option is on by default.
Preserve SAS variable names
This option is useful for data tables that were imported originally from SAS. When importing a SAS data set, the original SAS variable name is saved in a column property for each column. Select this option to use the SAS variable name for each column when exporting to SAS. Deselect this option to export the JMP variable names. This option is off by default.
Preserve SAS formats
This option is useful for data tables that were imported originally from SAS. When importing a SAS data set, the original SAS format and informat is saved in a column property for each column. Select this option to use the SAS format and informat for each column when exporting to SAS. Deselect this option to export the JMP formats instead. This option is on by default.
Show Libraries in the Export Data to SAS Window
If your libraries do not appear in the Export Data to SAS window, define the library in one of the following ways:
• Using JSL, submit code to the SAS server. The code defines a library using a libref command.
• Define an autoexec.sas file that runs a snippet of SAS code every time SAS is invoked. This creates the same librefs every time you connect to SAS. For details about autoexec.sas files, see the SAS documentation.
Libraries that are defined in metadata (such as libraries defined in the SAS Management Console under the Data Library Manager) cannot be accessed from the Export Data to SAS window.
Import Data from R
JMP provides a scripting interface to R. See the Scripting Guide for details.
Import Data Using the Excel Add-In
The add-in for Excel provides new capabilities to JMP and Excel users on Windows:
• Transfer selected cells in Excel to JMP data tables. See Transfer Excel Data to a JMP Data Table.
• Use the JMP Profiler with calculation models in Excel workbooks. The profiler tool is designed to bring the power of the JMP profiler to models residing in Excel spreadsheets. You do not have to recreate your Excel models in JMP, verify that they are correct, and maintain the model in both JMP and Excel. For more information, see the chapter on Profiling in Modeling and Multivariate Methods.
Note: During the JMP installation, select the Excel Add-In. This installs the add-in for your version of Microsoft Excel.
Microsoft Excel 2003, Microsoft Excel 2007, and Microsoft Excel 2010 are supported.
Transfer Excel Data to a JMP Data Table
To transfer data from Excel to a JMP data table:
1. In your Excel worksheet, show the JMP add-in on the ribbon. (For Excel 2003, this is not necessary, since the add-in is on the toolbar.)
2. Click the Preferences button.
3. Enter the name for the new JMP data table.
4. To use one or more rows of Excel data as JMP columns names, select the Use First Rows as Column Name option and enter the number of rows to use.
The cells that contain column names do not have to be the first rows in the spreadsheet. They need only be the first rows in the group of cells that you select to transfer to JMP.
5. Click OK.
6. Select the cells to transfer into JMP, including any cells that you want to use as column names.
If you are using cells as column names, they need to be the first rows in your selection.
If only one cell (or no cell) is selected, the entire Excel sheet is transferred to JMP.
7. Click Data Table (Excel 2007 and Excel 2010) or click Transfer to JMP (Excel 2003).
If JMP is not running, it is started, and the selected data is placed in a new JMP data table.
Note: Empty cells are brought in as missing data, and dates, numbers, and strings are recognized correctly.
8. (Optional) Click Graph Builder or Distribution to both transfer the data to JMP and to launch either the Graph Builder or Distribution platform.
Note: Your JMP windows might be hidden behind your Excel window, especially if you maximize Excel.
For more information about using Graph Builder and Distribution, see the Basic Analysis and Graphing book.
Uninstall the Excel Add-In
1. Open the Windows Add or Remove Programs utility (in the Control Panel).
2. Select JMP Profiler Core and click Uninstall.
3. Select JMP Profiler GUI and click Uninstall.
Uninstall Previous Versions of the Excel Add-In
JMP automatically installs the Excel Add-In for your version of Excel. If you have a previous version of this add-in, uninstall it:
1. Open the Windows Add or Remove Programs utility (in the Control Panel).
2. Select JMP_ExcelAddinSetup and click Uninstall.
About the JMP Add-In for Excel
The JMP add-in for Excel is installed in the following areas, depending on the version of Excel that you have:
• In Excel 2010, all options are on the JMP ribbon.
• In Excel 2007, all options are on the JMP ribbon.
• In Excel 2003, the menu is in the toolbar on a JMP button that produces a menu of options.
The functionality for all commands is the same for all versions of Excel.
Excel 2007 and 2010
The JMP add-in commands for Excel 2007 and Excel 2010 are in two groups:
Transfer to JMP
Preferences
Set preferences for transferring data from Excel to JMP.
Data Table
Transfer the selected data in your Excel file to a JMP data table.
Graph Builder
Transfer the selected data in your Excel file to a JMP data table and launch the Graph Builder platform.
Distribution
Transfer the selected data in your Excel file to a JMP data table and launch the Distribution platform.
Profile in JMP
Create/Edit Model
Set up preferences for using the JMP profiler with Excel data.
Run Model
Run the JMP profiler.
Excel 2003
The JMP add-in commands for Excel 2003 are on the JMP menu on the toolbar:
Preferences
Set preferences for transferring data from Excel to JMP.
Transfer to JMP
Transfer the selected data in your Excel file to a JMP data table.
Graph Builder()
Transfer the selected data in your Excel file to a JMP data table and launch the Graph Builder platform.
Distribution()
Transfer the selected data in your Excel file to a JMP data table and launch the Distribution platform.
Profiler Configuration
Set up preferences for using the JMP profiler with Excel data.
Run Profiler
Run the JMP profiler.
Import Data from a Database
You can import data from a database if you have an ODBC (Open Database Connectivity) driver for the database.
Your operating system provides an interface for JMP to communicate with databases using ODBC data sources. Data sources can be created and configured with operating system software: For example, on Windows XP, use Control Panel > Administrative Tools > Data Sources; on the Macintosh, use Applications > Utilities > ODBC Administrator.
When you import data from an ODBC database, within the data table, note the following:
• There is a script called Source that you can run to re-import and refresh the data.
• A table variable is added that might contain user ID and password information. There is a JSL-only preference that can be set to prevent including this possibly sensitive information. See the Scripting Guide for more details.
Open Data from a Database
To import data from a database:
1. Select File > Database > Open Table to display the window shown in Figure 3.25. The Connections box lists data sources to which JMP is connected. The Schemas box lists schemas for those databases that support them. The Tables box lists database tables for the currently selected data source connection.
Figure 3.25 Database Open Table Window
Note: The Fetch Procedures check box is disabled if the ODBC driver does not support fetching procedures.
2. If the desired data source is not listed in the Connections box, click Connect to choose a data source. The method of choosing a data source depends on your operating system. Figure 3.26 shows the data source chooser for Windows. Select a data source and click OK.
Figure 3.26 Select a Database Source (Windows)
3. Select the desired data source in the Connections box. The tables list in the Tables box updates accordingly. The update might take a several seconds, depending on the number of tables and the speed of the connection to the database. If your database supports schemas, tables are loaded for the first schema in the list, and on other schemas as you click on them.
4. Control which tables are listed by choosing the options in the Include in Table List group of check boxes. Different drivers interpret these labels differently. Your options are as follows:
User Tables When clicked, displays all available user tables in the Tables list. User tables are specific to which user is logged on to the computer.
Views When clicked, displays “views” in the Tables list along with all other file types that can be opened. “Views” are virtual tables that are query result sets updated each time you open them. They are used to extract and combine information from one or more tables.
System Tables When clicked, displays all available system tables in the Tables list. System tables are tables that can be used by all users or by a system-wide service.
Synonyms When clicked, displays all available ORACLE synonyms in the Tables list.
5. Select the desired table from the Tables list.
Note: If you are connected to a dBase database, select the database folder to which you would like to connect. Individual files are grayed out and cannot be selected.
6. Click Open Table to import all the data in the selected table, or click Advanced to specify a subset of the table to be imported.
Note: Some databases require that you enter the user ID and password to access the data.
Retrieve Data Using SQL Statements
You can use Structured Query Language (SQL) statements to control what you import from a database. When you open a database file in JMP, you are actually sending an SQL statement to the database. By default, this statement gets all files and records in the database table. In some cases, this is too much data. When you are interested only in a subset of the table’s data, you can customize the SQL request to only request the data that you want. After you execute an SQL query, the code for the query is stored in the data table in the SQL table variable.
Note: The SQL Query that you run in this window operates only on the tables and procedures that are displayed in the left panes of the window. Running unrelated SQL here has no results.
To retrieve data using SQL statements:
1. Connect to a database by following the steps in Open Data from a Database.
2. From the Database Open Table window, click the Advanced button to open specific subsets of a table.
3. Either type in a valid SQL statement, or modify the default statement. Figure 3.27 shows a default SQL Select statement appropriate for the selected file. See Structured Query Language (SQL): A Reference, for a description of SQL statements that you can use.
Alternately, you can add expressions by clicking the Where button and using the WHERE Clause editor to create expressions. See Use the WHERE Clause Editor, for details.
Figure 3.27 Reading All Variables from the Solubility Table Stored in an Excel File
4. Click Execute SQL. A JMP data table appears with the columns that you selected. The SQL statement becomes an SQL table variable in the JMP data table. (For details, see Use Table Variables in Enter and Edit Data.)
Note that you can enter any valid SQL statement and click Execute SQL to execute the command. Valid SQL varies with the data source and ODBC driver.
Structured Query Language (SQL): A Reference
The following sections are a brief introduction to SQL. They give you insight to the power of queries, and they are not meant to be a comprehensive reference.
Use the SELECT Statement
The fundamental SQL statement in JMP is the SELECT statement. It tells the database which rows to fetch from the data source. When you completed the process in Open Data from a Database with the Solubility.jmp sample data table, you were actually sending the following SQL statement to your data source.
SELECT * FROM "Solubil$"
The * operator is an abbreviation for “all columns.” So, this statement sends a request to the database to return all columns from the specified data table.
Rather than returning all rows, you can replace the * with specific column names from the data table. In the case of the Solubility data table example, you could select the ETH, OCT, and CCL4 columns only by submitting this statement:
SELECT ETH, OCT, CCL4 FROM "Solubil$"
Note: JMP does not require you to end SQL statements with a semicolon.
JMP provides a graphical way of constructing simple SELECT statements without typing actual SQL. To select certain columns from a data source, highlight them in the list of columns (Figure 3.27).
To highlight several rows:
• Shift-click to select a range of column names
• Ctrl-click (Windows) or Command-click (Macintosh) to select individual column names.
Note that the SQL statement changes appropriately with your selections.
Sometimes, you are interested in fetching only unique records from the data source. That is, you want to eliminate duplicate records. To enable this, use the DISTINCT keyword.
SELECT DISTINCT ETH, OCT, CCL4 FROM "Solubil$"
Sort Results
You can have the results sorted by one or more fields of the database. Specify the variables to sort by using the ORDER BY command.
SELECT * FROM "Solubil$" ORDER BY LABELS
selects all fields, with the resulting data table sorted by the LABELS variable. If you want to specify further variables to sort by, add them in a comma-separated list.
SELECT * FROM "Solubil$" ORDER BY LABELS, ETH, OCT
Use the WHERE Statement
With the WHERE statement, you can fetch certain rows of a data table based on conditions. For example, you might want to select all rows where the column ETH has values greater than 1.
SELECT * FROM "Solubil$" WHERE ETH > 1
The WHERE statement is placed after the FROM statement and can use any of the following logical operators.
|
Operator
|
Meaning
|
|
=
|
Equal to
|
|
!= or < >
|
Not equal to
|
|
>
|
Greater than
|
|
<
|
Less Than
|
|
>=
|
Greater than or equal to
|
|
<=
|
Less than or equal to
|
|
NOT
|
Logical NOT
|
|
AND
|
Logical AND
|
|
OR
|
Logical OR
|
When evaluating conditions, NOT statements are processed for the entire statement first, followed by AND statements, and then OR statements. Therefore
SELECT * FROM "Solubil$" WHERE ETH > -2 OR OCT < 1 AND CCL4 > 0
is equivalent to
SELECT * FROM "Solubil$" WHERE ETH > -2 OR (OCT < 1 AND CCL4 > 0)
Use the IN and BETWEEN Statements
To specify a range of values to fetch, use the IN and BETWEEN statements in conjunction with WHERE. IN statements specify a list of values and BETWEEN lets you specify a range of values. For example,
SELECT * FROM "Solubil$" WHERE LABELS IN (‘Methanol’, ‘Ethanol’, ‘Propanol’)
fetches all rows that have values of the LABELS column Methanol, Ethanol, or Propanol.
SELECT * FROM "Solubil$" WHERE ETH BETWEEN 0 AND 2
fetches all rows that have ETH values between 0 and 2.
Use the LIKE Statement
With the LIKE statement, you can select values similar to a given string. Use % to represent a string of characters that can take on any value. For example, you might want to select chemicals out of the "Solubil$" data that are alcohols, that is, have the –ol ending. The following SQL statement accomplishes this task.
SELECT * FROM "Solubil$" WHERE LABELS LIKE ‘%OL’
The % operator can be placed anywhere in the LIKE statement. The following example extracts all rows that have labels starting with M and ending in OL:
SELECT * FROM "Solubil$" WHERE LABELS LIKE ‘M%OL’
Use Aggregate Functions
Aggregate functions are used to fetch summaries of data rather than the data itself. Use any of the following aggregate functions in a SELECT statement.
|
Function
|
Meaning
|
|
SUM( )
|
Sum of the column
|
|
AVG( )
|
Average of the column
|
|
MAX( )
|
Maximum of the column
|
|
MIN( )
|
Minimum of the column
|
|
COUNT( )
|
Number of rows in the column
|
Some examples include:
• The following statement requests the sum of the ETH and OCT columns:
SELECT SUM(ETH), SUM(OCT) FROM "Solubil$"
• This statement returns the number of rows that have ETH values greater than one:
SELECT COUNT(*) FROM "Solubil$" WHERE ETH > 1
• The following statement lets you know the average OCT value for the data that are alcohols:
SELECT AVG(OCT) FROM "Solubil$" WHERE LABELS LIKE ‘%OL’
Note: When using aggregate functions, the column names in the resulting JMP data table are Expr1000, Expr1001, and so on. You probably want to rename them after the fetch is completed.
The GROUP BY and HAVING Commands
The GROUP BY and HAVING commands are especially useful with the aggregate functions. They enable you to execute the aggregate function multiple times based on the value of a field in the data set.
For example, you might want to count the number of records in the data table that have ETH=0, ETH=1, and so on, for each value of ETH.
• SELECT COUNT(ETH) FROM "Solubil$" GROUP BY (ETH) returns a single column of data, with each entry corresponding to one level of ETH.
• SELECT COUNT(ETH) FROM "Solubil$" WHERE OCT > 0 GROUP BY (ETH) does the same thing as the above statement, but only for rows where OCT > 0.
When using GROUP BY with an aggregate function of a column, include the column itself in the SELECT statement. For example,
SELECT ETH, COUNT(ETH) FROM "Solubil$" GROUP BY (ETH)
returns a column containing the levels of ETH in addition to the counts.
Use Subqueries
Aggregate functions are also useful for computing values to use in a WHERE statement. For example, you might want to fetch all values that have greater-than-average values of ETH. In other words, you want to find the average value of ETH, and then select only those records that have values greater than this average. Remember that SELECT AVG(ETH) FROM "Solubil$" fetches the average that you are interested in. So, the appropriate SQL command uses this statement in the WHERE conditional:
SELECT * FROM "Solubil$" WHERE ETH > (SELECT AVG(ETH) FROM "Solubil$")
Save and Load SQL Queries
After constructing a query, you might want to repeat the query at a later time. You do not have to hand-type the query each time you want to use it. Instead, you can export the query to an external file. To do this, click the Export SQL button in the window shown in Figure 3.27. This brings up a window that lets you save your SQL query as a text file.
To load a saved query, click the Import SQL button in the window shown in Figure 3.27. This brings up a window that lets you navigate to your saved query. When you open the query, it is loaded into the window.
Use the WHERE Clause Editor
JMP provides help building WHERE clauses for SQL queries during ODBC import. It provides a WHERE clause editor that helps you build basic expressions using common SQL features, allowing vendor-specific functions. For example, you do not need to know whether SQL uses ‘=’ or ‘==’ for comparison, or avg() or average() for averaging.
In addition, string literals should be enclosed by single quotes (‘string’)rather than double quotes (“string”).
To open the WHERE clause editor:
1. Connect to a database by following the steps in Open Data from a Database.
2. From the Database Open Table window, shown in Figure 3.25, click the Advanced button.
3. Click the Where button.
USE the WHERE Clause Editor to add any of the following from the work panel: expressions, functions, and terms. They are applied to the highlighted red box.
1. Click the Table Name Browser to select a table. The columns in that table appear in the list.
2. Click the SQL Vendor Name Browser to select the type of SQL that you want to use: GenericSQL, Access, DB2, MySQL, Oracle, SQL Server, or all of the above. Perform an action by clicking a function or operator in the list and selecting an operator from the list that appears.
Note: The following SQL Server data types are not supported: Binary, Geography, and Geometry.
3. Select an empty formula element in the formula editing area by clicking it. It is selected when there is a red outline around it. All terms within the smallest nesting box relative to the place that you clicked become selected. The subsequent actions apply to those combined elements.
4. Add operators to an expression by clicking buttons on the keypad.
5. (Optional) To customize your WHERE clause, select one of the options from the red triangle menu above the keypad:
Show Boxing
Show or hide boxes around the WHERE clause terms.
Larger Font
Increase the font size of the formula.
Smaller Font
Decrease the font size of the formula.
Simplify
Simply the WHERE clause statement as much as possible.
The WHERE clause editor works similarly to the Formula Editor, which is described in the Formula Editor.
Figure 3.28 The WHERE Clause Editor
Read in Real-Time Data
The term live data feed describes the way an external data source sends information via a physical or a logical communication link to another device. You can connect JMP to a live data feed through the serial port of your Windows computer to read a stream of incoming data in real time. Remember the following:
• The data feed must come through a standard nine-pin serial port. Data cannot be read through a USB port unless there is a driver that can simulate a serial port
• You need to know the exact baud rate, parity, stop bits, and data bits for the attached device.
Once you obtain the numbers for your device, enter them into the Open Datafeed() command in the script below. (The 4800, even, 2, and 7 in the script below are examples, so replace them with your information). Then connect the data feed to your computer and open and run the script:
streamScript = expr( line = feed <<Get Line;show(line);
len = length(line); show(len);
if (length(line)>=1, show("Hi"); show(line);
field = substr(line,5,8); show(field);
x = Num(field); show(x);
if (!IsMissing(x), current data table()<<add row({:Column1=x});
show(x);
)));
feed = open DataFeed(Baud Rate(4800),parity(even),Stop bits(2), Data bits(7));
feed<<Set Script(streamScript);
feed<<Connect;
To ensure harmony between the communications settings for JMP and the instrument reading data from an external source, select File > Preferences > Communications. Refer to the documentation for your instrument to find the appropriate settings.
For more details about scripting a Datafeed, see the Scripting Guide.
Create New Data Tables
To create a new data table by entering data manually:
1. Select File > New > Data Table. This shows an empty data table with no rows and one numeric column, labeled Column 1.
2. Move the cursor onto a cell.
3. Click in the cell. The cursor appears as a line in the cell, as shown in Figure 3.29.
Figure 3.29 A New Data Table
4. Enter a value.
There are several ways to fill a table with values:
• Create new rows and columns and type or paste data into the data grid. (See Add Rows in Enter and Edit Data.)
• Import data from another application. (See About Importing Data to JMP.)
• Copy values from another application and paste them into the table.
• Use a measuring instrument to read external measures. (See Read in Real-Time Data for details.)
• Drag columns from one table to another.
See the Enter and Edit Data for details about how to format, edit, and work with data tables.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.