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
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?
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?
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 onlywhy the same code run in mysql but not in sas proc sql
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.
Thanks for your help.
I really appreciate it.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.