SAS Viya 3.3: Add to Import Feature

[#1]

Steve Foerster's recent article on CAS Cross Loading prompted me to write this entry on the Add to Import feature in SAS Viya 3.3. In his article he talked about "cross loading" data from one CAS library to another using code. When you do this, you can load data from one CAS library to a different CAS library's in-memory location without creating a physical table or file in the target CAS library.

A colleague made a comment about the article and mentioned that you could move data from one CAS library to another in the user interface using the SAS Data Explorer application or the data selection dialog in one of the other SAS Viya 3.3 applications (e.g., the Open Data Source dialog in SAS Visual Analytics). The Add to Import feature does provide the ability to move data from one CAS library to another; however, it will create a physical copy of the data in the other CAS library as well as an in-memory copy.

The Add to Import feature enables you to copy a table or file from the Available or Data Sources tab to the Import tab and then copy that data to the same or different CAS library. To select this feature, highlight a table on the Available or Data Sources tab and right-click to select the menu item for Add to import.

To demonstrate the Add to Import feature, I will show how I can use the mega_corp.sas7bdat file in the VISUAL CAS library to create a 2010_mega_corp table in the Public CAS library with only records from the year 2010 and the resulting table will only have select columns from the original mega_corp.sas7bdat file.

In the screenshot below I have selected the physical file mega_corp.sas7bdat and have right-clicked to reveal the menu and will select Add to import.

Note:  The original mega_corp.sas7bat file has 46 columns and 1.6M rows.

After selecting the Add to import menu option, you are taken to the Import tab where you can specify the following options:

• Target table name – name of the table to create during the import. The default name is the same as the source file or table.
• Target destination – the CAS library where the target table will be saved during the import. This could be the same CAS library as the source file or table or it could be a different CAS library.  The default selection is based on your configuration settings.  Note:  Copying data to another CAS library could make data available to a group or individuals that wouldn't otherwise have access to that data in the source data's CAS library. You should keep in mind this potential security concern when moving data between different CAS libraries.
• If the target table name exists – select the behavior of what to do if the target table already exists in the target destination. The options are to Cancel import or Replace file. The default selection is Cancel import.
• Label – set an optional description for the imported data.

In the screenshot below I have changed the target table name to 2010_mega_corp and set the target destination to the Public CAS library. I have also selected the option to replace file if it already exists and added an optional label of subset if Mega Corp data from 2010.

The Create Job button can be selected to create an import job based on these specifications for the selected Import entry. This job can then be scheduled. For more information on Job scheduling, refer to the documentation.

If you select a physical item (not an in-memory table) as the source for your Add to Import, then there are additional options you can specify.

• File Specifications – import settings for physical files (e.g., SAS data sets, text files, Excel files, etc.). The settings will vary based on file type. Note: This tab will not be displayed for CAS (.sashdat) files or database tables. For my demonstration, I can keep the default settings for the File Specification for the mega_corp.sas7bdat file.

• Filter Rows – limit the data imported based on a SAS WHERE clause. When writing the clause omit the WHERE= portion of the expression. For more information about the syntax of the WHERE clause, refer to the documentation. If possible, the filter expression is translated into native database syntax and is pushed down to the database for processing.

  In order to retrieve just the rows from 2010, I enable row filtering option and I enter the filter as shown in the screenshot below.

• Select Columns – limit the columns of data to be imported.

  On this tab, I enable column selection and I select the subset of columns that I want to be part of my 2010_mega_corp table. I have selected 6 columns to be part of the new table as depicted in the screenshot below.

Once all these selections are made, you can select the Import Item button and the target table will be created in the target destination as specified.  Note: If you have setup multiple imports, then you can also select the Import All button to import multiple items at the same time.

Now that I have made all my selections, I select Import Item button to import the 2010_mega_corp table to the Public CAS library as specified.

Once the table is successfully imported, I receive this message.

I navigate to the Public CAS library to confirm that the table is there.

Note: The 2010_mega_corp table that was imported has 6 columns and 519K rows. It is a subset of the original mega_corp.sas7bdat file in the VISUAL CAS library. Also, the imported 2010_mega_corp table has a physical copy as well as an in-memory one.

For more information on the Add to Import feature in SAS Viya 3.3, refer to the documentation.