Sorting Data

[RonLee]

Hi everyone,

In SAS Viya, How could you sort data ? Please advise if you have some information.

[Patrick]

You don't need to sort data in CAS in order to consume it sorted (by group processing, if first./last. in a data step).

[Frank_Boekamp · Accepted solution]

Sorting data sets in CAS is not even possible, proc sort and order by in proc FEDSQL for example do not exist in CAS.

Be careful using SAS base code in CAS: see running the DATA step in CAS with special attention to RETAIN, LAG, and _N_

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2184-2018.pdf

Using SAS Studio on Viya, you can use the 'old' style SAS Base code. But then you are not profiting from the big performance gain in CAS which originates from multiple threading and in-memory processing of data.

[Frank_Boekamp]

Creating s sorted table in CAS seems to be possible using partioning functionality. You can order variables within a partition. So to create an ordered CAS table, you should create one (dummy) partition for the whole table. 

/* Examples how to get an ordered CAS table */
cas;
caslib _all_ assign;
 
/* following code uses a 9.4 SAS dataset as input and creates a sorted table in CAS */
data work.unsorted;
     group = "A";       
     w1 = 5; w2 = 6; output;
     w1 = 3; w2 = 3; output;
     w1 = 1; w2 = 2; output;
     w1 = 3; w2 = 4; output;
run;

proc casutil
   outcaslib="casuser";
   load data= "unsorted" casout="sort1" replace partitionby=(group) orderby=(w1 w2) ;
run;

It's also possible to create a sorted CAS table based on a CAS table as input. This routine could replace a proc sort from 9.4:

/* Following code runs in CAS and sorts the columns in a CAS table */

/* A dummy group is added to put all data in the same partition */


data casuser.unsorted;
     group = "A";       
     w1 = 5; w2 = 6; output;
     w1 = 3; w2 = 3; output;
     w1 = 1; w2 = 2; output;
     w1 = 3; w2 = 4; output;
run;

data casuser.sort3 (partition=(group) orderby=(w1 w2));
    set casuser.unsorted ;
run;

You can use this sorted dataset for subsequent datasteps. But: to use constructs like retain and lag() you still have to change the datastep code: you could add the datastep option  / single=yes to force CAS to run the datastep single threaded or use some retain logic to deal with multi threading.

One thing that is not working like it should do: order variables in descending order like in following example:

https://documentation.sas.com/?docsetId=casref&docsetTarget=p12eiok32viouqn1huacr2d39nl0.htm&docsetV...

This example contains some syntax errors and is not sorting variable ch as it should do.

My own example to show this:

/* following code runs in CAS and should order column w2 descending within column w1 */
/* This is not working as expected: column w2 is not ordered descending within w1  */
data casuser.sort4 (partition=(group) orderby=(w1 DESCENDING w2));
    set casuser.unsorted ;
run;

Why is this not working???