The question about how to schedule a SAS Studio 5.1 (Studio V) job in SAS Viya has come up from several people recently, so I decided to write an article that gives a step-by-step example of how to do this.   The process of scheduling a SAS Studio 5.1 (Studio V) program involves the following steps: Write and save the SAS Studio 5.1 program(s) you want to schedule Determine the host and port for the job schedule execution Write the code to call the SAS Studio 5.1 program(s) you want to schedule Create and submit the SAS Job Execution code Schedule the Job in SAS Environment Manager. As part of this article, I will use an example to walk through each of these steps.   Write and save SAS Studio 5.1 program(s) First you need to write and save the program(s) in SAS Studio 5.1 that you want to be part of a scheduled job execution. In my case I have two SAS Studio 5.1 program files that I want to be called one after the other in a scheduled job.   The first program file called Generate CSV File Example.sas uses proc export to write all the cars of type Sedan to a CSV file.   Select any image to see a larger version.   The second program file called Load File to Memory Example.sas drops a table called CARS_CSV from the Public caslib and then loads the CSV file created by the previous program and promotes the table CARS_CSV to the Public caslib.     Both of these program files have been saved in a folder called gelcontent within the SAS Content section of SAS Viya in SAS Studio 5.1.     Determine host and port for job schedule execution Now that I have the program file(s) to call, I need to determine the host and port to use for the job schedule execution. To do this, I go to the Servers page in SAS Environment Manager. The host and port listing for the CAS Controller is the one you want to use. In my case, that means the host is intcas01.race.sas.com and the port is 5570.     Write code to call the SAS Studio 5.1 program(s) Next, you can test your code by writing and testing a program in SAS Studio 5.1 to execute the program(s).  Alternatively, you can perform this step directly in the SAS Job Execution web application.   First, you need to define the CAS session with the appropriate host, port, and any wanted sessopts. Next, you need to use the following syntax to reference and include the file with SAS program.   filename <JobName> FILESRVC folderpath='<program_folderpath>' filename='<program_filename>'; %include <JobName> / source;   Repeat this code for any other program files you want to reference and then finally you terminate your CAS session.   The code for my example looks like this:     I submit the code and confirm that the CARS_CSV table was loaded to the Public caslib.     Now that you've confirmed that the code works, copy the code from this test file, so you can later paste it in a SAS Job Execution file.     Create and submit SAS Job Execution code To open the SAS Job Execution application, enter http://ViyaServerName/SASJobExecution/ in a web browser.   On the Content tab, navigate to the folder where you want to store the job execution file and click to create a new file in the selected folder.     Enter a name and description for the job execution file. Select the file type of Job definition. Then, click OK to create the new file.     With the new file name selected, click to edit the contents of the file.     In the edit window for the job execution file, paste the code you copied previously from your SAS Studio 5.1 test job. Note: You must use Ctrl + V to paste the copied code. Click  to save the code. Click  to submit and execute the code in the Job Execution file.     Submitting the job opens a SAS Output tab for the contents from the executed file. Since the job I ran has no content to display, the tab is empty.     You can close this tab after reviewing it. Back on the Content tab, click Close to close the edit window for the file.     You may also want to consider adding parameters to your SAS Job Execution file. One way to do this is to use the application's Job Submit Options which will apply to all submitted jobs for your browser session until you change the options.     In the example below, I have set the parameter _output_type=none which means the SAS Output tab will not be displayed after submitting the job. I have also checked the box for Show log which means the SAS Log will be displayed when submitting the job.     Select the Jobs tab, to view the executed jobs and their status. Confirm that the job that was just executed has a status of .     A successful execution indication on the Jobs tab doesn't necessarily mean all steps within the program ran successfully, it just means the program itself executed. Therefore, I confirm that the CARS_CSV table was loaded to the Public caslib as expected by the steps within the program.     Note: It has a later date and time than when I ran the test in SAS Studio 5.1.   For more information on the SAS Job Execution web application, please refer to the documentation.   Schedule Job in SAS Environment Manager Now that the SAS Execution Job has been tested successfully, the job can now be scheduled. To schedule the job, on the Jobs tab in SAS Job Execution select the job you want to schedule and click .     This will add a job entry on the SAS Environment Manager – Jobs tab and open that application. On the SAS Environment Manager – Jobs tab, select the Scheduling section and select the SAS Job Execution job that was just added. Click to create a schedule for the job.     Now you can schedule the job as usual in SAS Environment Manager. For example, if I want to run this job every day at noon New York time, I would create the following trigger and enable it.       The job is now scheduled to run at the specified trigger time.     For more information on scheduling jobs in SAS Environment Manager, refer to the documentation.   After the time has passed for the scheduled job to run, I first confirm on the SAS Environment Manager – Jobs tab in the Monitoring section that the scheduled job executed successfully.     Then, I confirm that the CARS_CSV table was loaded to the Public caslib.     Note: It has a later date and time than when I ran the file in the SAS Job Execution application. The date and time correspond to the scheduled time for the job in SAS Environment Manager.   Thanks for reading this article on how to schedule a SAS Studio 5.1 (Studio V) job in SAS Viya. Here is a related article that you may find of interest: Making SAS Programs stored on the filesystem available to SAS Viya folders     Thanks to my colleague, Clemens Knobloch, for his work on this subject that I leveraged to write this article. ... View more

In SAS Viya 3.4, you can use tags to add extra information beyond simple metadata to tables, columns, and other objects. In the user interface, these tags can be added to columns automatically when profiling the data or manually added. At this time in the user interface, you can add a new tag name, but there is not currently a way to perform the following actions: Search for items that have a designated tag Update an existing tag name Delete an existing tag name. However, these actions are available using the Annotations REST API service.   I will demonstrate how the three actions listed above can be performed using the Annotations REST API service in a 3-part series. My previous posts covered the first two items: search for items that have a designated tag update an existing tag name. This article will focus on how you can delete an existing tag name.   Before using the Annotations REST API service, you must first obtain and OAuth token. For more information on this, you can read the following the following articles: Authentication to SAS Viya: a couple of approaches Introducing Python-based command-line tools for SAS Viya by Gerry Nelson Note: Make sure the username you use to retrieve the OAuth token has the necessary permissions to run any of the REST APIs you want to execute.   Once you have your OAuth token, you can then use the Annotation REST API service. In my case, I want to delete the tag with the name of Demo2_Update. First, I need to make a call to search for the annotation (tag) with the name of Demo2_Update to get its ID information by using this call:   GET  http://sasviya01.race.sas.com/annotations/annotations/?filter=eq(name,'Demo2_Update') Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.collection+json Header:  Authorization= Bearer <ReplaceWithOAuthToken>   Select any image to see a larger version.   Note: You can use many different tools to perform REST API calls. For my demonstration, I am using a tool called Postman.   This returns the following results:     Note: c2a28476-d82a-4ec9-af55-de063cc4d720 is the Annotation ID for the Demo2 tag name.   Now that I have the ID for the tag with name of Demo2_Update, I can submit the request to delete it:   DELETE  http://sasviya01.race.sas.com/annotations/annotations/c2a28476-d82a-4ec9-af55-de063cc4d720 Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=*/* Header:  Authorization= Bearer <ReplaceWithOAuthToken>     Note: No content is returned from the DELETE request.     The tag with the name of Demo2_Update has now been deleted. I submit the following request to return all tag names that contain the word "Demo":   GET  http://sasviya01.race.sas.com/annotations/annotations/?filter=contains(name,'Demo') Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.collection+json Header:  Authorization= Bearer <ReplaceWithOAuthToken>     The results returned now show that there is only one tag with a name containing the word "Demo" – Demo1.     This concludes the demonstration on how to delete an existing tag name using the Annotations REST API. For more information on working with tags (annotations) using REST API, you can refer to the documentation. ... View more

In SAS Viya 3.4, you can use tags to add extra information beyond simple metadata to tables, columns, and other objects. In the user interface, these tags can be added to columns automatically when profiling the data or manually added. At this time in the user interface, you can add a new tag name, but there is not currently a way to perform the following actions: Search for items that have a designated tag Update an existing tag name Delete an existing tag name. However, these actions are available using the Annotations REST API service.   I will demonstrate how the three actions listed above can be performed using the Annotations REST API service in a 3-part series. My previous article covered the first item – search for items that have a designated tag. This entry will focus on how you can update an existing tag name using the Annotations REST API.   Before using the Annotations REST API service, you must first obtain and OAuth token. For more information on this, you can read the following articles: Authentication to SAS Viya: a couple of approaches Introducing Python-based command-line tools for SAS Viya by Gerry Nelson Note: Make sure the username you use to retrieve the OAuth token has the necessary permissions to run any of the REST APIs you want to execute.   Once you have your OAuth token, you can then use the Annotation REST API service. In my case, I want to change the tag name from Demo2 to Demo2_Update. First, I need to make a call to search for annotation (tag) with the name of Demo2 to get its ID information by using this call:   GET  http://sasviya01.race.sas.com/annotations/annotations/?filter=eq(name,'Demo2') Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.collection+json Header:  Authorization= Bearer <ReplaceWithOAuthToken>   Select any image to see a larger version.   Note: You can use many different tools to perform REST API calls. For my demonstration, I am using a tool called Postman.   This returns the following results:     Note: c2a28476-d82a-4ec9-af55-de063cc4d720 is the Annotation ID for the Demo2 tag name.   Now that I have the ID for the tag with name of Demo2, I need to make a call to get its eTag information. The eTag is used to identify the specific version of the tag entry, so it can be updated. This is the call I need to make to get the eTag information for the Demo2 tag:   GET  http://sasviya01.race.sas.com/annotations/annotations/c2a28476-d82a-4ec9-af55-de063cc4d720 Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.annotation+json Header:  Authorization= Bearer <ReplaceWithOAuthToken>     This returns the following results:     The eTag information is in the Headers section of the returned results.     Note: "jqpaybnl" is the eTag for the current version of the Demo2 tag.   Now that I have both the Annotation ID and the eTag information for the Demo2 tag, I can make the call to update its name by using this call:   PUT  http://sasviya01.race.sas.com/annotations/annotations/c2a28476-d82a-4ec9-af55-de063cc4d720 Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.annotation+json Header:  Authorization= Bearer <ReplaceWithOAuthToken> If-Match: "jqpaybnl"     With the following Body information in the request:   {   "id": "c2a28476-d82a-4ec9-af55-de063cc4d720",   "domain": "SAS.COMMON.TAG",   "name": "Demo2_Update",   "label": "Demo2_Update",   "description": "Updated Tag Name" }     This returns the following results:     The tag name has now been changed to Demo2_Update. I submit the following request to return all tag names that contain the word "Demo":   GET  http://sasviya01.race.sas.com/annotations/annotations/?filter=contains(name,'Demo') Header:  Content-Type=application/vnd.sas.annotation+json Header:  Accept=application/vnd.sas.collection+json     The results returned now show that the two tag names containing the word "Demo" are Demo1 and Demo2_Update.     This concludes the demonstration on how to update an existing tag name using the Annotations REST API. In my next article in this series, I will explore using the Annotations REST API to delete an existing tag name.   For more information on working with tags (annotations) using REST API, you can refer to the documentation. ... View more

In SAS Viya 3.4, you can use tags to add extra information beyond simple metadata to tables, columns, and other objects. In the user interface, these tags can be added to columns automatically when profiling the data or manually added. At this time in the user interface, you can add a new tag name, but there is not currently a way to perform the following actions: Search for items that have a designated tag Update an existing tag name Delete an existing tag name. However, these actions are available using the Annotations REST API service.   I will demonstrate how the three actions listed above can be performed using the Annotations REST API service in a 3-part series. This article will focus on how you can search for items that have a designated tag.   Before using the Annotations REST API service, you must first obtain and OAuth token. For more information on this, you can read the following articles: Authentication to SAS Viya: a couple of approaches Introducing the GEL pyviyatools Note: Make sure the username you use to retrieve the OAuth token has the necessary permissions to run any of the REST APIs you want to execute.   Once you have your OAuth token, you can then use the Annotation REST API service. In my case, I want to search for items that have a tag that contains the word "Demo". To do this I use the GET method for the Annotations REST API with a filter option:   GET http://<ViyaServerName>/annotations/annotations?filter=contains(name,'<TagNameToFind>')   For my specific example this is the REST API call I make to return any tag names that contain the word "Demo".   GET http://sasviya01.race.sas.com/annotations/annotations/?filter=contains(name,'Demo') Header: Content-Type=application/vnd.sas.annotation+json Header: Accept=application/vnd.sas.collection+json Header: Authorization= Bearer <ReplaceWithOAuthToken>   Select any image to see a larger version.   Note: You can use many different tools to perform REST API calls. For my demonstration, I am using a tool called Postman.   This returns the following results:     From these results I can see that two items containing the word Demo in their tag name are returned: Demo1 Demo2. Note: When running any of these Annotation API requests, there is by default a maximum limit of 10 items returned. To increase the number of items returned, you can use the limit option when making your Annotation REST API call.   Now that I know the IDs of the annotations with the word "Demo" in the name, I can run a call based on their returned URIs for the members relationship type. For example, to retrieve the member items for the Demo2 tag name, I run the following request based on its members relationship URI of /annotations/annotations/c2a28476-d82a-4ec9-af55-de063cc4d720/members . Note: c2a28476-d82a-4ec9-af55-de063cc4d720 is the Annotation ID for the tag with the name of Demo2.   GET http://sasviya01.race.sas.com/annotations/annotations/c2a28476-d82a-4ec9-af55-de063cc4d720/members Header: Content-Type=application/vnd.sas.annotation+json Header: Accept=application/vnd.sas.collection+json Header: Authorization= Bearer <ReplaceWithOAuthToken>     This returns five items that are tagged with the name Demo2.     The column and table names are returned when using the Annotations REST API call for its members: GET http://<ViyaServerName>/annotations/annotations/<AnnotationID>/members   For my specific call the table and column names that are tagged with the name Demo2 are:   Table Name CUSTOMERS in the DM CAS Library Table Name CUSTOMERS_NEW in the DM CAS Library Column Name name on the CUSTOMERS table in the DM CAS Library Column Name name on the CUSTOMERS_NEW table in the DM CAS Library   For items that are not tables or columns, I need to run the REST API for its resource URI to get its actual name. For example, to retrieve the Data Plan name based on its URI /dataPlans/plans/916318ea-c759-4dc1-a4b1-5796bee9976c , I need to run the following request:   GET http://sasviya01.race.sas.com/dataPlans/plans/916318ea-c759-4dc1-a4b1-5796bee9976c Header: Content-Type=application/vnd.sas.data.plan+json Header: Accept=application/vnd.sas.data.plan+json Header: Authorization= Bearer <ReplaceWithOAuthToken>   Note: The header information for Content-Type and Accept will vary depending upon the object.     This call returns that the data plan name that is tagged with Demo2 is named Plan 1.     This concludes the demonstration on how to search for items that are assigned a particular tag name using the Annotations REST API. In my next two articles, I will explore using the Annotations REST API to perform the following actions: Update an existing tag name Delete an existing tag name. For more information on working with tags (annotations) using REST API, you can refer to the documentation. ... View more

In the SAS Viya 3.4 user interface you can create connections to databases in SAS Data Explorer, SAS Environment Manager, or in the Data selection dialogs within specific applications (e.g., SAS Visual Analytics, SAS Data Studio, etc.).  Note: You must have the appropriate SAS Data Connector licensed and installed in order to setup a database connection in SAS Viya 3.4.  However, there may be connection options that are not listed in the documentation for a database type. This is where the conopts parameter is useful since it allows you to pass connection options directly to the database.  The conopts parameter is specified in the user interface by using an Advanced parameter.   There have been several questions on the Hub and ListServs about how to connect to a PostgreSQL database that uses SSL in SAS Viya 3.4 since SSL is not a connection option that is listed in the SAS Data Connector documentation for PostgreSQL connections.  It was these questions that prompted me to write this blog to show how this can be accomplished by using the conopts parameter to connect to a PostgreSQL database that uses SSL.     To specify an option using the conopts parameter, first I need to know what the specific connection option parameter name and value to use is for my database type.  In my case, I want to connect to a PostgreSQL database using SSL, so I need to look up what is the parameter name and value(s) used by PostgreSQL for this option.  By looking at the PostgreSQL documentation for database connections – Parameter Key Words section, I find that one of the parameters is called sslmode.  Here is a listing of the possible settings for this parameter:     (click to enlarge image)     To connect to my desired PostgreSQL database, I need to set the sslmode parameter to require.     In the SAS Viya 3.4 user interface on the Connection Settings dialog, I enter the database connection information for my PostgreSQL database in the Settings section.  Note: Don't forget to check the option for Persist this connection beyond the current session.       In the Advanced section, I first select the Authentication Domain for my connection.  Next, I enter the following in the Additional Parameters section to add my sslmode=require setting using the conopts parameter.        Now, I can Test and Save my connection to a PostgreSQL database that uses SSL.  After the connection is saved, I may want to consider authorizing other users and/or groups to use this connection. The connection is now ready for use in SAS Viya 3.4 and can be used to create reports, data plans, etc.       This post just reviewed one use of the conopts property.  You can use this property to pass other connection options to your database if needed.  For more information on Creating Database Connections in SAS Viya 3.4, you can refer to the documentation.     ... View more

SAS Data Studio 2.2 powered by SAS Viya 3.4 provides a mechanism for performing simple, self-service data preparation tasks to prepare data for use in SAS Visual Analytics or other applications. It is accessed via the Prepare Data menu item from SAS Drive. Note: A user must belong to the Data Builders custom group to have access to this menu item.   I have had several people ask during training classes, how they can control the column order and names for their saved tables resulting from a data plan in SAS Data Studio. The best way to do this is to use the Code Transform in Data Studio to write custom code to create custom code to perform actions or transformations on a table. In previous posts, I have explored some uses of the Code Transform. In this article, I will show how you can use the Code Transform to rearrange the fields on the results table by writing CASL code using the action set fedSQL. For more information on the fedSQL action set, refer to the documentation.   In my Data Studio plan, my current results table after doing all my desired transformations to the source table is:   click on images to see a larger version   However, I want my results table to look like this:     In order to achieve this desired result, I write the following CASL code using the fedSQL action set in a Code transform in my Data Studio plan.     This code keeps only the desired columns: Team, Name_GivenName_Parse, Name_FamilyName_Parse, YrMajor, nAtBat, and nHits. It also renames the Name_GivenName_Parse and Name_FamilyName_Parse columns to First Name and Last Name respectively as well as rearranges the columns into the desired order.   Now, when I run and save the plan and create its resulting table, the following table is saved with only the desired column names and the specified order.       For more information on SAS Data Studio 2.2 powered by SAS Viya 3.4, you can refer to the documentation for SAS Data Studio 2.2: User's Guide. ... View more

In this YouTube video I introduced the Data Profiling feature that is part of SAS Data Preparation powered by SAS Viya.  Now with the release of SAS Viya 3.4, there is even more functionality that is part of the profiling feature in SAS Data Preparation.  In this article, I will explore the additional functionality of Identification Analysis and Tagging.   The identification of what type of data is in a column uses an Identification Analysis definition from the SAS Quality Knowledge Base.  The definition makes a best guess of what type of data is contained in the column by assigning an ID analysis score for different identity designations.  The column is then tagged with the designation with the highest score.  This tagging can then be used to help determine what data preparation to perform or identify personal identifying information.  Note:  The analysis is based on the locale (country/language combination) that is specified in your Default locale for Quality Knowledge Base setting.  In my case, it is set to Use the default server setting which is English-United States.     Also, keep in mind the SAS Quality Knowledge Base QKB) for Contact Information (CI) version 29 or later is required as it contains an identification analysis definition called Field Content which is used to perform this analysis during profiling.   First, I need to turn on the option to perform the identification analysis of columns when profiling the data set.  This is done in the application settings for SAS Data Explorer by checking the box for the option Analyze column contents while running a profile in the Profile section.  Note:  Selecting this option does impact the profiling performance and will take longer to receive the profile report results.     Next, I navigate to the data set I want to profile and select the button to run the profile.     After the profile has finished executing, I review the profile report on the Profile tab.     I can drill down into of the columns such as ADDRESS_1 and view its column-specific profile information.  The column's properties now display an ID analysis score result based on the identification analysis.  The results are relative scores and do not correspond to actual record counts.  In this case, the value with the highest score is Delivery Address.     That means that the ADDRESS_1 column is automatically tagged with the Delivery Address value.  To view a column's tag, I select the Details tab and then click on the tag button for the column.     I confirm that the ADDRESS_1 column is auto-tagged with the value Delivery Address based on the identification analysis performed on the column.     I can choose to remove this tag or add others to the column.  This tagging can be used to help determine what kind of data preparation I want to perform on the column.   As a final step, you may want to uncheck the option Analyze column contents while running a profile in the Profile section since it affects the profile performance and only turn it back on if you need this identification analysis for another data set.       For more information on SAS Data Preparation 2.2 powered by SAS Viya 3.4, you can refer to the documentation for SAS Viya 3.4: Data Preparation. ... View more

With the release of Viya 3.4, there are now new advanced options available when importing local data files in the user interface.  These options are on the Advanced tab and are:   Unique ID – provides the option to add a column that contains a unique value for each row imported. You can specify the name of the column and select to replace an existing column, if desired. Additional Input Options – enables you to specify additional input options not available on the File Specifications tab that might be needed when importing the file.   Note: The Additional Input Options selection is only available for delimited text files.   Let's look at an example of using these new advanced options when importing a local delimited text file. Here is an image of the file I want to import.     Notice that the rows do not have a unique identifier and there are leading and trailing blanks in some columns.  I will use the new advanced import options to address these issues when I import the data.   First, I set up the local text file import as usual on the File Specifications tab.     Next, I select the Advanced tab.  Both new advanced options are displayed since I am importing a local delimited text file.     To address the issue of no unique identifier for the rows, I check the Unique ID option and name the new column Unique_ID.   There is also an option to Replace column if it already exists on the local file.     Next, to address the issue of leading and trailing blanks in columns, I check the Additional Input Options box and add an option called stripBlanks which is type Boolean and set the value to True in order to have leading and trailing spaces stripped from the columns when the file is imported.     Finally, I import the local file by selecting the Import Item button and then navigate to the CAS library where I imported the data and view the results.     As you can see on the Sample Data tab, I now have a Unique_ID column by using the Unique ID option and have removed the leading and trailing spaces in columns by using the Additional Input Option stripBlanks.   For more information on SAS Viya 3.4 and importing local files, you can refer to the documentation on Importing Local Data Files. ... View more

In a previous post on SAS Data Studio, I showed how you could use the Code transform to create a Unique Identifier and Cluster records.  SAS Data Studio 2.2 which is powered by SAS Viya 3.4, now has built-in transforms to perform these functions and you no longer need to write your own code.   In this article, I will introduce you to the two new transforms in SAS Data Studio that perform this functionality: Unique identifier – enables you to create a column in the target table that contains a value to uniquely identify each row in the table. These unique row identifiers are used in text topics in SAS Visual Analytics. Match and cluster – enables you to match columns according to clustering rules and conditions resulting in the matching records receiving the same cluster ID for grouping purposes. Note: To use this transform you must be licensed for SAS Data Preparation.   For my examples, I am using the following list of contact records as the source table for my data preparation plan in SAS Data Studio.     Unique Identifier Transform Example As part of my data preparation process I would like to generate a unique identifier for each of the rows in the table.  To do this, I select the new Unique identifier transform in the Row Transforms section and select the option to create a new column called Unique_ID. I also have the option to replace an existing column with generated unique identifier.     The results of running the Unique identifier transform on my contact list table are shown below:     Match and Cluster Transform Example Continuing with the contact records example, I want to match and cluster my records on the following conditions: Name, Address, and Zip OR Name and Phone.   Prior to applying these matching rules, I will perform some data quality operations on the data in order to achieve better matching results.   First, I generate some match codes for the fields Name, Address, and Zip to facilitate the fuzzy matching of the information in these columns. I used the Matchcodes transform in the Data Quality Transforms section to generate these matchcodes.     Next, I standardize the Phone field using the Standardize transform in the Data Quality Transforms section.     Now that I have prepared my data for my matching purposes, I select the new Match and cluster transform in the Data Quality Transforms section. I have called the new column Cluster_ID and added my matching conditions as shown in the screenshot below:     The Match and cluster transform has some advanced options you can select: Interpret empty strings as null values. This option is checked by default. Allow null values to match. For matching purposes, if two columns have a null value then they will not match unless this option is checked. Exclude rows from matching if the value of the following column is "True". Specify the name of a column that contains the "do not cluster" flag. If a row is flagged as "do not cluster", then that row will be placed in a cluster all by itself and be assigned a unique cluster ID value. The transform will not attempt to match the row to any other rows in the source table.   The results of running of the Match and cluster transform on my table are displayed below:     Notice that all the variations of the "Susan Woodard" records are assigned the same Cluster_ID value as are the "James Briggs" records.   With the addition of these two new transforms in SAS Data Studio 2.2. users no longer need to write custom code to achieve these common data preparation functions.  For more information on the new release of SAS Data Studio 2.2, you can refer to the documentation for SAS Viya 3.4: Data Preparation. ... View more

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. ... View more