BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RonLee
Fluorite | Level 6

Hi everyone,

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frank_Boekamp
Quartz | Level 8

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.

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

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
Quartz | Level 8

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
Quartz | Level 8

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???

 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2685 views
  • 2 likes
  • 3 in conversation