[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Re: Min function
From:       Guido T <cymraeg_erict () HOTMAIL ! COM>
Date:       2005-10-31 8:56:02
[Download RAW message or body]

>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

[prev in list] [next in list] [prev in thread] [next in thread]