proc sql order by

[deltaskipper]

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 

[Tom · Accepted solution]

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?

[Reeza]

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 

---

[deltaskipper]

Thank you.

[PGStats]

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.

[deltaskipper]

Thanks for your help.

I really appreciate it.