Date:         Tue, 27 Jan 2009 16:47:46 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: MAX of Count in SQL
Comments: To: SAS_learner <proccontents@GMAIL.COM>
In-Reply-To:  <c2192a610901271253p59ebfca6u154f3d76994f4150@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"

SAS SQL doesn't support summary functions of summary functions. The calculated mx_count represents Count(Smpbtc), so the query is specifying MAX(COUNT(Smpbtc)) generates an error since it "nests" the summary count function inside the summary max function. The SAS SQL compiler does support much the same in a nested query view:

/* Abbreviated example */
Proc Sql Noprint ;
Create table aa as
Select  mx_count, Max (mx_count ) as Max_count
From (select visit, count(Smpsmp) as mx_count
      From Smpsmp
      Group By visit
      )
      ;
Quit ;
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SAS_learner
Sent: Tuesday, January 27, 2009 3:54 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: MAX of Count in SQL


Hello All,

I am trying to find Max count of Smpbtc variable for that I am doing something like this, I can do it two steps but why does calculated mx_count does not work ?? Just curious

Proc Sql Noprint ;
Create table aa as
Select  Count(Smpbtc)   as mx_count, Max (calculated mx_count ) as Max_count

From Smpsmp
Group By sbjnbr, pt ,actevent,  visit ,cpevent;
Quit ;

With this Code there is

ERROR : Summary Functions Nested in this way are not Supported

What does it mean ??