```Date: Sat, 28 Jul 2001 05:15:28 GMT Reply-To: Ya Huang Sender: "SAS(r) Discussion" From: Ya Huang Organization: AT&T Worldnet Subject: Re: SAS SQL problem Good catch! Now the n=_n_ or monotonic() is no longer necessary. I wish I had thought about that. Thanks for sharing. Regards, Ya Huang Francis Harvey wrote in message <08B08C9FA5EBD311A2CC009027D5BF810137CDB9@remailnt2-re01.westat.com>... >Greetings, > >Simplifying a little on Sig's solution: > >proc sql; > select dlrid, min(source) > from (select * > from (select dlrid, source, count(*) as cnt > from xx > group by dlrid, source) > group by dlrid > having cnt=max(cnt)) > group by dlrid; >quit; > >Francis R. Harvey III >WB303, x3952 >harveyf1@westat.com > >> -----Original Message----- >> From: Sigurd Hermansen >> Sent: Thursday, July 26, 2001 5:40 PM >> To: SAS-L@LISTSERV.VT.EDU >> Subject: Re: SAS SQL problem >> >> >> I have made a couple of small changes to Ya's excellent >> solution. It seems a >> good place to test the experimental (and undocumented) >> MONOTONIC() function. >> Thanks to the usual suspects at SI for the inside information. >> >> I have taken the SELECT statement another level down to apply the >> MONOTONIC() function. The SQL solution now generates a a counter n >> automatically. It not only qualifies, in my view, as a "one >> step" solution, >> in an important sense it improves on possible one step data >> step solutions. >> The innermost query selects only the columns needed for the >> solution. Sig >> >> data xx; >> input Dlrid source \$; >> x=_n_; >> cards; >> 1 A >> 1 A >> 1 B >> 1 B >> 1 A >> 2 C >> 2 B >> 2 A >> 2 B >> 3 C >> 4 A >> 4 C >> ; >> run; >> >> proc sql; >> select dlrid, source >> from (select * >> from (select *, count(*) as cnt >> from (select dlrid, source, monotonic() as n >> from xx) group by dlrid, source) >> group by dlrid >> having cnt=max(cnt)) >> group by dlrid >> having n=min(n) >> ; >> quit; >> ```

Back to: Top of message | Previous page | Main SAS-L page