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.

Figure 3.55 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.54).

• 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.

Figure 3.56 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.

Figure 3.57 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.

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.

Import a Sample of a SAS Data Set

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.58 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.59 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.60 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 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.61 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, 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.

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.