Re: Equivalent of the "Rollup" SQL function in SAS (and

ROLLUP and CUBE were on the 1999 SASWare Ballot (Google: rollup cube sql
group:comp.soft-sys.sas). Despite my lobbying for it on SAS-L, it didn't do
well, and I have been told informally that it's way down on the list of
things to do (and given other developments in SAS, I'd agree with its low
placement).



--
Jack Hamilton
Oakland, California






> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Howard Schreier <hs AT dc-sug DOT org>
> Sent: Tuesday, May 17, 2005 8:44 am
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Re: [SAS-L] Equivalent of the "Rollup" SQL function
> in SAS (and the CUBE
>
> This is one of those situations where PROC SUMMARY can do the
> job if it is
> run twice.
>
> Test data:
>
> data have;
> input id Year Dept $ Class $;
> cards;
> 1 2000 Sci. Math
> 1 2000 Sci. Math
> 1 2000 Sci. Phy
> 1 2000 Letters French
> 2 2000 Sci. Phy
> 2 2000 Sci. Phy
> 3 2000 Letters French
> 3 2000 Letters Span.
> ;
>
> The first pass:
>
> proc summary data=have;
> class Year Dept Class id;
> types Year *id
> Year*Dept *id
> Year*Dept*Class*id;
> output out=halfbaked(rename = (_type_ = cross)
> drop = _freq_
> );
> run;
>
> HALFBAKED contains an enumeration of ID values at each level:
>
> Obs Year Dept Class id cross
>
> 1 2000 1 9
> 2 2000 2 9
> 3 2000 3 9
> 4 2000 Letters 1 13
> 5 2000 Letters 3 13
> 6 2000 Sci. 1 13
> 7 2000 Sci. 2 13
> 8 2000 Letters French 1 15
> 9 2000 Letters French 3 15
> 10 2000 Letters Span. 3 15
> 11 2000 Sci. Math 1 15
> 12 2000 Sci. Phy 1 15
> 13 2000 Sci. Phy 2 15
>
> The second pass excludes ID and thus generates the needed counts:
>
> proc summary data=halfbaked missing nway;
> class cross Year Dept Class;
> output out=baked(drop = cross _type_
> rename = (_freq_=Nb)
> );
> run;
>
> Result:
>
> Obs Year Dept Class Nb
>
> 1 2000 3
> 2 2000 Letters 2
> 3 2000 Sci. 2
> 4 2000 Letters French 2
> 5 2000 Letters Span. 1
> 6 2000 Sci. Math 1
> 7 2000 Sci. Phy 2
>
> Here's a variation which requires less code but generates
> more information.
>
> proc summary data=have;
> class Year Dept Class id;
> output out=halfbaked(rename = (_type_ = cross)
> where = (mod(cross,2)=1)
> drop = _freq_
> );
> run;
>
> The WHERE= filter discards the crossings which do not include
> ID. It is
> needed because there is no TYPES statement.
>
> The second pass is unchanged:
>
> proc summary data=halfbaked missing nway;
> class cross Year Dept Class;
> output out=baked(drop = cross _type_
> rename = (_freq_=Nb)
> );
> run;
>
> Result:
>
> Obs Year Dept Class Nb
>
> 1 . 3
> 2 . French 2
> 3 . Math 1
> 4 . Phy 2
> 5 . Span. 1
> 6 . Letters 2
> 7 . Sci. 2
> 8 . Letters French 2
> 9 . Letters Span. 1
> 10 . Sci. Math 1
> 11 . Sci. Phy 2
> 12 2000 3
> 13 2000 French 2
> 14 2000 Math 1
> 15 2000 Phy 2
> 16 2000 Span. 1
> 17 2000 Letters 2
> 18 2000 Sci. 2
> 19 2000 Letters French 2
> 20 2000 Letters Span. 1
> 21 2000 Sci. Math 1
> 22 2000 Sci. Phy 2
>
> You now have counts for all combinations of categories. It's
> repetitious in
> this example, but if there were multiple years, or if CLASS
> did not nest
> under DEPT as it does here, it could well be useful to do it this way.
>
> On Tue, 17 May 2005 04:27:26 GMT, Louis-René Rheault
> <louisrho@HOTMAIL.COM>
> wrote:
>
> >Hello Toby,
> >
> >Thanks but my problem is precisely the totals... I'd like to
> know if the
> SQL
> >procedure could make something equivalent of the PROC
> SUMMARY without NWAY
> >option. The problem with the PROC SUMMARY is that it counts
> the repetitives
> >idnumber everytime instead of once.
> >
> >Example:
> >
> >Person A takes a MATH class (Sciences dept), a PHY class
> (Sciences dept)
> and
> >a FRENCH class (Letters dept). With the SQL code:
> > SELECT year, department, class, COUNT(DISTINCT idnumber) AS nb
> > FROM mytable
> > GROUP BY ROLLUP(year, department, class);
> >
> >This person will be counted once for every class in the
> class-detailed
> rows,
> >only once in the Science dept subtotals(even if it occurs
> twice) and only
> >once in the total of the whole population. That is the role
> of the "ROLLUP"
> >function in SQL.
> >
> >If I take the PROC SUMMARY (without the NWAY function), SAS
> will count the
> >person once for each class, will add all the classes for Science dept
> >subtotals to make 2 (even if the person A is in fact only 1
> person) and
> have
> >a total of 3 for the whole population (instead of 1 for the
> person A).
> >
> >
> >
> >
> >The totals are exactly the point of my question
> >"toby dunn" <tobydunn@HOTMAIL.COM> a écrit dans le message de news:
> >BAY101-F3949A17F0DA930E1458503DE150@phx.gbl...
> >> Louis,
> >>
> >> Here is some SQL code that gets the counts the way you
> want, I will let
> >> you
> >> add the Totals where you want them.
> >>
> >>
> >> proc sql ;
> >> create table two as
> >> select distinct year , dept , class , count(*) as Nb
> >> from (select distinct * from one) as aaa
> >> group by year , dept , class
> >> order by year , dept , class ;
> >> quit ;
> >>
> >>
> >>
> >> Toby Dunn
> >>
> >>
> >>
> >>
> >> From: Louis-René Rheault <louisrho@HOTMAIL.COM>
> >> Reply-To: Louis-René Rheault <louisrho@HOTMAIL.COM>
> >> To: SAS-L@LISTSERV.UGA.EDU
> >> Subject: Equivalent of the "Rollup" SQL function in SAS
> (and the CUBE
> >> function also)
> >> Date: Mon, 16 May 2005 19:22:44 GMT
> >>
> >> Hello,
> >>
> >> I can't find how to get this result...
> >>
> >> I'd like to count the number of different persons in classes, in
> >> departments
> >> and in the whole organization. But one person can be in
> more than one
> >> class
> >> and in more than one department.
> >>
> >> In Oracle's SQL, I would write:
> >> SELECT year, department, class, COUNT(DISTINCT idnumber) AS nb
> >> FROM mytable
> >> GROUP BY ROLLUP(year, department, class);
> >>
> >> An example:
> >>
> >> id Year Dept Class
> >> -------------------------
> >> 1 2000 Sci. Math
> >> 1 2000 Sci. Math
> >> 1 2000 Sci. Phy
> >> 1 2000 Letters French
> >> 2 2000 Sci. Phy
> >> 2 2000 Sci. Phy
> >> 3 2000 Letters French
> >> 3 2000 Letters Span.
> >>
> >> The result would give:
> >> Year Dept Class Nb
> >> ------------------------------
> >> 2000 Sci Math 1
> >> 2000 Sci Phy 2
> >> 2000 Sci (total) 2
> >> 2000 Letters French 2
> >> 2000 Letters Span. 1
> >> 2000 Letters (total) 2
> >> 2000 (total) (total) 3
> >>
> >> Thanks
>