SAS Data Studio 2.2: Use the Code Transform to Rearrange Fields for the Results Table

[#1]

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.