topic Re: proc sql order by in SAS Programming

proc sql order by

deltaskipper — Sat, 01 Sep 2018 17:17:59 GMT

In mysql server this code run without error

SELECT TOP 1 COUNT(TRANSACTION_ID) AS NO_OF_TRANSACTION,STORE_TYPE
FROM TBL_TRANSACTION
GROUP BY store_type
ORDER BY COUNT(TRANSACTION_ID) DESC

but in sas when using proc sql the code:

proc sql;

create table delta as

SELECT TOP 1 COUNT(TRANSACTION_ID) AS NO_OF_TRANSACTION,STORE_TYPE
FROM TBL_TRANSACTION
GROUP BY store_type
ORDER BY COUNT(TRANSACTION_ID) DESC

;

quit;

run;

shows an error

Summary functions are restricted to the SELECT and HAVING clauses only

why the same code run in mysql but not in sas proc sql

Re: proc sql order by

Tom — Sat, 01 Sep 2018 19:40:28 GMT

Not sure why SAS sends that error since it is demonstrably false.

proc sql;
create table delta as
  SELECT  age, COUNT(name) AS n 
  FROM sashelp.class 
  GROUP BY age
  ORDER BY calculated n desc
;
quit;

Perhaps it means you cannot use them in ORDER BY unless they are ALSO included in the SELECT?

Re: proc sql order by

Reeza — Sat, 01 Sep 2018 22:21:18 GMT

TOP 1 won't work in SAS SQL, that is invalid.

You also cannot use a function in ORDER BY, but you can use the variable you already created you just need to add the CALCULATED keyword in front of it, as demonstrated in @Tom's example.

@deltaskipper wrote:

In mysql server this code run without error

SELECT TOP 1 COUNT(TRANSACTION_ID) AS NO_OF_TRANSACTION,STORE_TYPE
FROM TBL_TRANSACTION
GROUP BY store_type
ORDER BY COUNT(TRANSACTION_ID) DESC

but in sas when using proc sql the code:

proc sql;

create table delta as

SELECT TOP 1 COUNT(TRANSACTION_ID) AS NO_OF_TRANSACTION,STORE_TYPE
FROM TBL_TRANSACTION
GROUP BY store_type
ORDER BY COUNT(TRANSACTION_ID) DESC

;

quit;

run;

shows an error
Summary functions are restricted to the SELECT and HAVING clauses only
why the same code run in mysql but not in sas proc sql

Re: proc sql order by

PGStats — Sun, 02 Sep 2018 03:01:46 GMT

The TOP predicate is not supported by SAS/SQL. Here are two ways to get the most abundant age in sashelp.class

proc sql outobs=1;
create table delta1 as
SELECT age, COUNT(name) AS n 
FROM sashelp.class 
GROUP BY age
ORDER BY calculated n desc;
quit;


proc sql;
create table delta2 as
SELECT age, n 
FROM (
    select age, count(name) as n 
    FROM sashelp.class 
    GROUP BY age )
HAVING n = max(n);
quit;

The second query may return more than one record.

Re: proc sql order by

deltaskipper — Sun, 02 Sep 2018 05:21:10 GMT

Thanks for your help.

I really appreciate it.

Re: proc sql order by

deltaskipper — Sun, 02 Sep 2018 05:21:57 GMT

Thank you.