topic Re: Proc FEDSQL and Order By in SAS Viya

Proc FEDSQL and Order By

Will168 — Wed, 30 Jun 2021 23:28:05 GMT

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

Re: Proc FEDSQL and Order By

SASKiwi — Thu, 01 Jul 2021 02:08:21 GMT

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

Re: Proc FEDSQL and Order By

Will168 — Thu, 01 Jul 2021 02:10:41 GMT

We are using SAS Viya 3.5

Re: Proc FEDSQL and Order By

Naz10 — Fri, 24 Sep 2021 17:42:41 GMT

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

Re: Proc FEDSQL and Order By

Tom — Fri, 24 Sep 2021 21:16:43 GMT

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.

Re: Proc FEDSQL and Order By

suprax1979 — Tue, 07 Dec 2021 20:04:29 GMT

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.

Re: Proc FEDSQL and Order By

sqlGoddess — Fri, 28 Oct 2022 16:17:24 GMT

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.