Date:         Mon, 31 Oct 2005 08:56:02 +0000
Reply-To:     Guido T <cymraeg_erict@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Guido T <cymraeg_erict@HOTMAIL.COM>
Subject:      Re: Min function
Comments: To: gscsrc@hotmail.com
In-Reply-To:  <1130444160.557259.82720@g14g2000cwa.googlegroups.com>
Content-Type: text/plain; format=flowed

>From: Greg Curson <gscsrc@HOTMAIL.COM>
>Reply-To: gscsrc@HOTMAIL.COM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Min function
>Date: Thu, 27 Oct 2005 13:16:00 -0700
>
>I would like to get the lowest value of every different contid,
>but below its giving me every value in bdtotal, so how do I change
>it to give me what I need?
>
>data minbdtotal;
>   set vendor;
>keep contid vendor bdbstat bdtotal lowest;
>by contid;
>lowest = MIN(bdtotal,lowest);
>run;

Hi Greg,

Try the following SQL to get the lowest non-missing BDTOTAL for each CONTID,
with VENDOR and BDDSTAT ...

proc sql;
   create table minbdtotal as
   select a.contid, vendor, bdbstat,bdtotal, lowest
   from vendor as a
      left join
        (select contid, min(bdtotal) as lowest
         from vendor
         where bdtotal is not null
         group by contid
        ) as b
   on a.contid = b.contid
   ;
quit;

Or perhaps a datastep version :

proc sort data=vendor out=tvendor;
   by contid  bdtotal;
run;

data tcontid(keep=contid bdtotal rename=(bdtotal=lowest));
   set tvendor;
   where not missing(bdtotal);
   by contid bdtotal;
   if first.contid;
run;

data minbdtotal2;
    merge tvendor tcontid;
    by contid;
run;



Regards
++ Guido