BookmarkSubscribeRSS Feed

Supercharge your Column Management with the Manage Columns Step in SAS Studio 2023.12

Started ‎02-23-2024 by
Modified ‎02-26-2024 by
Views 732

Step into the future of data management! With the recent release of SAS Viya (2023.12), the Manage Columns step has undergone a major upgrade, catapulting its functionality to a new level. From the ability to modify column data types, lengths, informats, and formats to creating entirely new columns, the possibilities have expanded significantly. Moreover, the option to output a view instead of a table adds a new layer of flexibility to your workflow. The upgraded Manage Columns step isn't just about features - it's a game-changer for migration from DI Studio to SAS Studio too.

 

The Manage Columns step is available in SAS Studio Basic, SAS Studio Analyst, and SAS Studio Engineer. These licenses also include the Query step, which encompasses functionality similar to the Manage Columns step, with the exception that the Manage Columns step allows for input and output CAS tables—a feature currently not available in the Query step.

 

Let’s demonstrate the enhanced features of the Manage Columns step by using a SAS table as input and a view as output, briefly assess the generated code and then compare it with code generated using CAS tables.

 

Enhanced functionalities

 

  1. To create a new flow, use the SAS Studio toolbar and select New > Flow
  2. Add the CARS SAS table to the new flow by clicking 01_GR_Library.png (Libraries) in the navigation pane and expanding SASHELP. Right-click the CARS table and select Add to flow. Alternatively, you can drag the CARS table onto the flow canvas.
  3. Click 02_GR_FlowStep.png (Steps) in the navigation pane and, if necessary, expand Transform Data.
  4. Drag the Manage Columns step onto the CARS Table node until the mouse tool-tip changes to Connect to output port. The node details show the options for the Manage Columns node when selected.

001_GR_Z_Picture1.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

 

  1. Click 03_GR_Maximize.png (Maximize preview) on the node details to focus only on the node details
  2. In the Filter text field of the Available Columns pane, enter M. Double-click the Make and MSRP columns to add them to the Select tab.
  3. Add a label to the MSRP row of Manufacturer's Suggested Retail Price ($).
  4. Click 04_GR_Find.png (Change Format) in the MSRP row and expand Numeric. Select COMMA, and assign an overall width of 8. Click OK.

002_GR_Z_Picture2.png

  1. Click 05_GR_ClearFilter.png (Clear filter text) in the Filter field to return to the full list of columns in the Available Columns pane.
  2. Double-click the Invoice column to add it to the canvas.  Alternatively, right-click Invoice and choose Add.
  3. Add a label to the Invoice row of Invoice ($).
  4. Click 06_GR_Find.png (Change Format) in the Invoice row and expand Numeric. Select COMMA, and assign an overall width of 8. Click OK.

003_GR_Z_Picture3.png

  1. Click New Column. The Edit Expression window appears. Here you can add data and functions to the expression box to create new columns.
  2. In the expression box, enter 1-(. Notice the close bracket is also included. Use the Data tab on the left and double-click Invoice. Enter / and double-click MSRP.
  3. Enter Discount in the Column name text field and Discount (%) in the Column label.
  4. Use the drop-down box for Type and choose Numeric.
  5. Click 06_GR_Find.png (Change Format) for the Format field and expand Numeric. In the  Filter field enter per. The formats beginning with per are filtered. Select PERCENT and click OK.

004_GR_Z_Picture4.png

  1. Click Save. The new column has been added to the list of columns to output.
  2. The order of the columns can be modified by selecting the columns and using the Move columns drop-down. I will leave them as is.

005_GR_Z_Picture5.png

  1. Click 07_GR_Restore.png (Restore preview) to return the focus to the flow and node details.
  2. Select the output node of the Manage Columns node. Alternatively, you can add a Table step to the output node of the Manage Columns node.
  3. Select the Create a view radio button.

006_GR_Z_Picture8.png

  1. Click 08_GR_Run.png (Run entire flow).
  2. If necessary, click the output node of the Manage Columns node and select Preview Data.
  3. The view appears in the node details, with 4 columns and an unknown number of table rows. The view is a definition of a virtual data set. It contains the information required to access the data values and is stored separately from the data values.

007_GR_Z_Picture10.png

  1. Click Submitted Code and Results and scroll to line 158 (approximately). Notice the code generated is a Proc SQL.

008_GR_Z_Picture11.png

 

Using CAS Tables.

 

Let’s substitute the input and output options with CAS tables. For demonstration purposes, I have simply uploaded the CARS table to the CASUSER CAS library using the following code:

 

cas;
caslib _all_ assign;

proc casutil;
load data=sashelp.cars outcaslib="casuser"
casout="cars";
run;

 

  1. Click Flow to return to the Flow.
  2. Select the CARS table node and click 01_GR_Library.png (Library) in the Table node details.
  3. Double-click CASUSER and select CARS. Click OK.
  4. Right-click the output port of the Manage Columns node and select Add a table.
  5. In the Table node details, click 01_GR_Library.png (Library), double-click CASUSER, and name the table cars_discount. Click OK. Note the change in the Table node icon.
  6. Click 08_GR_Run.png (Run entire flow).

009_GR_Z_Picture12.png

  1. If necessary, click the cars_discount node and select Preview Data. The cars_discount table contains 428 rows and 4 columns. This table can now be used by other SAS Viya applications.

010_GR_Z_Picture13.png

  1. Click Submitted Code and Results and scroll to line 152 (approximately). Notice the code generated is now a Data step.

011_GR_Z_Picture14.png

 

Summary

 

To summarise:

  • The enhanced features of the Manage Columns step empower you to effortlessly navigate, customize, and organize your columns with precision.
  • The Manage Columns step supports CAS input and output tables. In cases where both input and output tables are in CAS format, the step will automatically generate DATA STEP code, as PROC SQL lacks support for scenarios where both input and output tables are in CAS.

 

Other notes:

  • Reducing output column lengths in the Manage Columns step may result in truncated values. The generated log will include a SAS note indicating the truncated target columns.
  • The icon of a Manage Columns step enables you to ascertain, at a glance, the purpose of the node. However, for broader data manipulation requirements on SAS tables, the Query step is recommended.

 

Here are some other methods of managing columns in SAS Studio on the SAS Viya Platform.

 

  Manage Columns Step Query Step Stand-alone Query Table Viewer
Select and Order Output Columns
      Modify Output Column Names  
      Modify Output Column Labels  
      Modify Output Length  
      Modify Output Informat  
      Modify Output Format  
Group Rows    
Filter Groups    
Join Tables    
Filter Rows  
Sort Rows  
      Remove Duplicate Rows  
      Remove Duplicates by Sort Columns       visible columns only
Calculate Columns  
Create a View  
Create a SAS Table  
Create a Report      

 

 

Reference

 

Thanks go to @ChrisLysholm of SAS R&D for editing, contributions and valuable suggestions for improvements of this article.

 

 

Find more articles from SAS Global Enablement and Learning here.

Comments

Thanks for sharing, @GemmaR . This is very interesting, especially the summary table at the end.  Thanks to David Weik's Change report with its filtering capabilities ( https://criptic.github.io/SAS-Whats-New/index.html ) I could trace back this Column feature to Viya Stable 202312 version (LTS can only refer to 202303 or 202310 bi-annual releases); next LTS in 2024 will also use this feature presumably.

Hi @ronan , thank you so much for reaching out! You are, of course, correct and I have updated the article accordingly. My apologies for the oversight. 

 

 

 

Version history
Last update:
‎02-26-2024 12:43 PM
Updated by:
Contributors

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags