How to Emulate DESCENDING BY Variables in DATA Step Code that Runs Distributed in SAS® Viya™


SAS Viya 3.5+ Supports Descending BY statement in DATA Step

The DESCENDING BY statement is supported in a DATA step that is running in CAS for the second and subsequent variables specified in the BY statement. The DESCENDING option is not supported for the first variable specified in the BY statement in a DATA step that is running in CAS; link to documentation.

A very common coding technique SAS programmers use is identifying the largest value for a given column using DATA Step BY statement with the DESCENDING option. In this example I wanted to find the largest value of the number of runs (nRuns) by each team in the SASHELP.BASEBALL dataset. Using a SAS workspace server, one would write:

DESCENDING BY Variables in SAS Viya
Figure 1. Single Threaded DATA Step in SAS Workspace Server

Figure 2 shows the results of the code we ran in Figure 1:

Figure 2. Result from SAS Code Displayed in Figure 1

To run this DATA Step distributed we will leveraging the SAS® Cloud Analytic Services in SAS® Viya™. Notice in Figure 3, there is no need for the PROC SORT step which is required when running DATA Step single threaded in a SAS workspace server. This is because SAS® Cloud Analytic Services in SAS® Viya™ performs implicit ordering on BY variables. Notice on line 104 we do not supply the DESCENDING option because it is not supported in distributed DATA Step running in the SAS® Cloud Analytic Services in SAS® Viya™. Instead we will use LAST. processing, line 107, on the BY variable “team” to output the last record for each team. The last record of the BY variable “team” contains the largest value for the BY variable “nRuns”.

Figure 3. Distributed DATA Step in SAS® Cloud Analytic Services in SAS® Viya™

Figure 4 shows the results when running distributed DATA Step in SAS® Cloud Analytic Services in SAS® Viya™.

Figure 4. Results of Distributed DATA Step in SAS® Cloud Analytic Services in SAS® Viya™


It is a very common coding technique to process data with a DESCENDING BY statement using DATA Step. With Viya 3.5+ the DESCENDING BY statements is supported, for numeric and character data in DATA Step, with this caveat: DESCENDING works on all but the first BY variable on the BY statement. For earlier versions of SAS Viya this emulation is the best practices for numeric data that you want in DESCENDING order.

Read more SAS Viya posts.

Read our SAS 9 to SAS Viya whitepaper.


About Author

Steven Sober

Advisory Solutions Architect, Data Management

Steven is responsible for empowering SAS sales and system engineers in the positioning and integration of SAS® Viya, SAS® In-Database Code Accelerator for Hadoop, SAS® Scalable Performance Data Server, and SAS® Grid Manager. During his tenure at SAS, Steven has traveled globally working with customers to quickly integrate the SAS system to automate processes. Before joining SAS in 1989 as the first employee of SAS Switzerland, Steven worked for the U.S. Geological Survey, Water Resources Division, assisting hydrologists to leverage the SAS system to derive intelligence on ground and surface water in Southern Colorado. He holds a Bachelor of Science degree in Computer Science from the University of Southern Colorado in Pueblo, Colorado.

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top