Proc FEDSQL and Order By

[Will168]

Hi,

Recently I came across an article 'Best Practices for Migrating SAS Code to Leverage CAS' - Page 43 that says we can use Order By in Proc Fedsql and running on CAS. 

proc fedsql sessref=casauto;
create table baseball_desc as
select distinct a.*
from baseball a
order by team DESC;
quit;

I tried and got error msg saying this is not supported.

ERROR: Unsupported operation in FedSQL query: ORDER BY.
ERROR: The action stopped due to errors.
ERROR: The FedSQL action was not successful.

I searched the net and found many examples for emulating proc sort or to run order by in proc sql before uploading the ordered data to CAS.  What are the options for doing sorting in CAS?

Thanks,

Will

[SASKiwi]

Out of curiosity, what version of Viya are you using? The article you refer to looks to be referencing Viya 3.4.

[Will168]

We are using SAS Viya 3.5

[Naz10]

Did you find a solution to your question? I'm getting the same error. Thanks

[Tom]

Are you sure the CAS object you are trying to create actually supports the concept of "order"?

Order BY is something you normally only need for printing reports.  

[suprax1979]

And to add on to this comment...

I was trying a similar operation in Proc FedSQL and received the same error message, and I found that it worked when I omitted the "CREATE TABLE... AS" statement. I believe it's not supported when creating a table, only when printing the output of the table.

[sqlGoddess]

jsut another behavior to be aware of. FEDSQL can't order by a calculated column, so any ordering has to be in a separate step, once the column has been built in a prior step.