Date:         Thu, 8 Jul 2010 13:39:32 -0400
Reply-To:     oloolo <dynamicpanel@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         oloolo <dynamicpanel@YAHOO.COM>
Subject:      Re: by Group trick,merge data
Comments: To: Arthur Tabachneck <art297@NETSCAPE.NET>

I would like to offer a shorter solution:
*****************************************;

data have;
  input datasource brand $ quarter $  sale;
  cards;
1               toyota 1977q1 15
1               toyota 1977q2 25
1               toyota 1977q3 35
1               ford 1977q1 45
1               ford 1977q2 55
1               ford 1977q3 65
2               toyota 1977q1 15
2               toyota 1977q2 25
2               toyota 1977q3 99
3               toyota 1977q3 99
;
run;

proc freq data=have noprint;
     table brand*quarter*sale/out=_freq;
  table brand*quarter/out=_freq2;
run;

data _freq3;
     merge _freq(drop=percent in=_1)
           _freq2(rename=(count=count2)  drop=percent);
  by brand quarter;
  if _1;
  conflict_flg=(count^=count2);
  drop count2;
run;
proc sort data=_freq3; by brand quarter descending count descending sale;
run;
data _freq3;
     set _freq3; by brand quarter;
  if first.quarter;
  drop count;
run;

data _null_;
     set _freq3;
     put _ALL_;
run;
*****************************************;
On Thu, 8 Jul 2010 07:41:49 -0700, Arthur Tabachneck <art297@NETSCAPE.NET>
wrote:

>Hewei,
>
>There is probably a much easier and straight forward way of doing what
>you want but, since no one else has responded, I'll offer the
>following possible solution:
>
>data have;
>  input datasource brand $ quarter $  sale;
>  cards;
>1               toyota 1977q1 15
>1               toyota 1977q2 25
>1               toyota 1977q3 35
>1               ford 1977q1 45
>1               ford 1977q2 55
>1               ford 1977q3 65
>2               toyota 1977q1 15
>2               toyota 1977q2 25
>2               toyota 1977q3 99
>3               toyota 1977q3 99
>;
>
>proc sort data=have;
>  by brand quarter;
>run;
>
>proc freq data=have noprint;
>  tables sale/out=mode (
>                        rename=(sale=want_sale)
>                        drop=percent);
>  by brand  quarter;
>run;
>
>proc sort data=mode;
>  by brand quarter descending count want_sale;
>run;
>
>proc sort data=mode nodupkey;
>  by brand quarter;
>run;
>
>data have1 have2 have3;
>  set have;
>  if datasource eq 1 then output have1;
>   else if datasource eq 2 then output have2;
>   else output have3;
>run;
>
>data want;
>  merge have1 (rename=(sale=sale1))
>        have2 (rename=(sale=sale2))
>        have3 (rename=(sale=sale3));
>  by brand quarter;
>run;
>
>data want;
>  merge want mode;
>  by brand quarter;
>run;
>
>data want (drop=aa: count i sale: datasource);
>  array aa_sales(3);
>  array a_sales(*) sale1-sale3;
>  set want;
>  do i=1 to dim(a_sales);
>    aa_sales(i)=a_sales(i);
>  end;
>  call sortn (of aa_sales[*]);
>  if mean(of sale:) ne aa_sales(dim(a_sales)) then
>    conflict_flag=1;
>   else conflict_flag=0;
>run;
>
>HTH,
>Art
>-----------
>On Jul 7, 8:00 pm, hewei2004 <hewei2...@gmail.com> wrote:
>> On Jul 7, 5:44 pm, Arthur Tabachneck <art...@netscape.net> wrote:
>>
>> > Hewei,
>>
>> > What if there are multiple modes?
>>
>> > Art
>> > -------------
>>
>> Then use the smallest of the mode please.
>> Thank you.