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:
Figure 2 shows the results of the code we ran 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 4 shows the results when running distributed DATA Step in SAS® Cloud Analytic Services in SAS® Viya™.
Conclusion
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.
