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.
Figure 3.56 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 Figure 3.55).
• 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.
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.
When you select a data set, the Data Preview outline shows you the first ten rows and columns in the data set.
Figure 3.57 Data Preview
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.
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.
Figure 3.58 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.
You can import SAS data sets directly into JMP.
1. From the Browse SAS Data window, select a data set.
By default, JMP specifies All rows for import.
2. Click Import.
The entire 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.
You can import a sample of a SAS data set directly into JMP.
1. From the Browse SAS Data window, select a data set.
2. Open the Import Options outline.
Figure 3.59 Import Options
3. If you want to import only a portion of a data set, you can do any of the following:
– Select the first x number of rows only. See To import the first x number of rows only.
– Select to auto-sample a specified file size. See To import an auto-sample file of a specified size.
– 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 custom sample of the data. See Importing a Random Sample of the Data.
The following options are also available:
Use labels for imported column names
Converts SAS labels to column names.
Add SQL table variable to imported table
Saves the SQL table variable as a data table variable.
Restore JMP metadata from SAS extended attributes
Includes JMP metadata (table and column properties) that were saved in the SAS data set as extended attributes.
To import the first x number of rows only
1. In the Import Options section, select First x rows only and specify the number of rows to import.
2. In the Browse SAS Data window, click Import.
JMP imports the specified number of rows.
To import an auto-sample file of a specified size
1. In the Import Options section, select Auto-sample and specify the number of MB to import.
2. In the Browse SAS Data window, click Import.
JMP imports the specified number of MB.
To select a subset of columns
1. In the Import Options section, click Select Columns.
The Select Columns window appears.
Figure 3.60 Select Columns
2. Select the columns that you want to import.
To select more than one column at a time, press Ctrl and click each column.
3. Click Add.
4. When you have added all the columns that you want, click OK.
5. 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.
Figure 3.61 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.
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 Custom random sample check box. By default, 5% of the rows are imported. To change the random sample import settings, click the Settings button.
Figure 3.62 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.
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, deselect 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.
Restore JMP metadata from SAS extended attributes
Tip: If your data is password-protected, you might want to turn this option off, because your password might be shown in the SQL.
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.
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.