topic Re: Proc Sql aggregate function error in SAS Programming

Proc Sql aggregate function error

Q1983 — Sun, 24 May 2020 23:34:07 GMT

47 proc sql;
48 create table lo as
49 SELECT DISTINCT LO_NAME,
50 Processing_Center_mlcs,
51 APP_DT as Year format mmddyy10.,
52 app_dt2 as Month format mmddyy10.,
53 Sum(cnt) AS Sum_Lo
54 FROM TBL2_DEDUP_4_
55 GROUP BY LO_NAME,Processing_Center_mlcs,
56 APP_DT,app_dt2,
57 Year(APP_DT), Year(app_dt2)
58 ORDER BY sum(cnt) DESC
59 /*having sum(cnt)*/
60 /*ORDER BY Sum(cnt) DESC;*/
61 ;
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

Why am I getting this error. I copied the code from MS Access sql

Re: Proc Sql aggregate function error

Reeza — Mon, 25 May 2020 00:17:01 GMT

Error message:

ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.

You have a SUM(), a summary function, in your ORDER BY statement. In SAS you can use CALCULATED if you've calculated this elsewhere. ie calculated sum_lo = sum(cnt)

47 proc sql;
48 create table lo as
49 SELECT DISTINCT LO_NAME,
50 Processing_Center_mlcs,
51 APP_DT as Year format mmddyy10.,
52 app_dt2 as Month format mmddyy10.,
53 Sum(cnt) AS Sum_Lo
54 FROM TBL2_DEDUP_4_
55 GROUP BY LO_NAME,Processing_Center_mlcs,
56 APP_DT,app_dt2,
57 Year(APP_DT), Year(app_dt2)
58 ORDER BY sum(cnt) DESC
59 /*having sum(cnt)*/
60 /*ORDER BY Sum(cnt) DESC;*/
61 ;

Something like this maybe, did you intend to take year(app_dt2) rather than month? That seems wrong to me. FYI please paste your code separately from your log in the future, it makes it easier to correct your code.

47 proc sql;
48 create table lo as
49 SELECT DISTINCT LO_NAME,
50 Processing_Center_mlcs,
51 APP_DT as Year format mmddyy10.,
52 app_dt2 as Month format mmddyy10.,
53 Sum(cnt) AS Sum_Lo
54 FROM TBL2_DEDUP_4_
55 GROUP BY LO_NAME,Processing_Center_mlcs,
56 APP_DT,app_dt2,
57 calculated year, Year(app_dt2)
58 ORDER BY calculated sum_lo DESC
59 /*having sum(cnt)*/
60 /*ORDER BY Sum(cnt) DESC;*/
61 ;

Re: Proc Sql aggregate function error

Tom — Mon, 25 May 2020 01:02:01 GMT

No need to call the aggregate function twice. Since you already included the sum in the set of variables being selected just refer to that new variable in the ORDER BY clause.

Example:

select sex,count(*) as n
 from sashelp.class
 group by sex
 order by n desc
;

If for some (?what possible?) reason you don't actually want to keep that variable even though you are using it for ordering use the DROP= dataset option.

create table gender_by_count(drop=n) as
select sex,count(*) as n
 from sashelp.class
 group by sex
 order by n desc
;

Re: Proc Sql aggregate function error

ballardw — Mon, 25 May 2020 01:21:02 GMT

Be extremely wary of assuming code that runs in one version of SQL will in any particular other.

I have an older Access manual with a page bookmarked that has line similar to "there are over 250 standard SQL features not implemented in Access". Almost every version of SQL has extensions that won't work in others. Sometimes they are as simple as functions, sometimes they are useful but not standard tools for manipulating sets of data such as Partition.