Date:         Sat, 28 Jul 2001 05:15:28 GMT
Reply-To:     Ya Huang <huanga@WORLDNET.ATT.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <huanga@WORLDNET.ATT.NET>
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;
>>