Date:         Fri, 2 Nov 2007 01:17:42 -0700
Reply-To:     Eric Hoogenboom <erichoogenboom@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Eric Hoogenboom <erichoogenboom@YAHOO.COM>
Organization: http://groups.google.com
Subject:      Re: I need only the newest date
Comments: To: sas-l@uga.edu
In-Reply-To:  <1193943178.782329.144930@o38g2000hse.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

On 1 nov, 19:52, gsc...@hotmail.com wrote:
> I got the follow from searching comp.soft-sys.sas.  but
> it just giving me the newdate per cont_id.....I need the new date per
> cont_id and prj_nbr.  Not sure if I adjusted the code right for me?
> Can anyone see what I am doing wrong and tell me how to correct it or
> is there an easier way to get what I need in a dataset?
> Thanks in advance.
>
> proc sort data =clean7 out = CLEAN8;
> by cont_id prj_nbr ;
> run;
>
> DATA newvards (DROP=datevar); * or explicitely KEEP variables;
>    SET CLEAN8;
>     BY cont_id prj_nbr datevar; * BY is required for applying FIRST
> and LAST;
>    RETAIN newvar; * Declare new variable, keeping its value over the
> implicit data step loop;
>    IF (FIRST.datevar) THEN newvar = 0; * reset to 0 for each new
> date;
>    newvar = /* whatever you want, based on oldvar, e.g. summing */
> newvar +
> datevar;
>    IF (LAST.datevar) THEN OUTPUT; * Only at the last multiple input
> record, one
> record per date;
> RUN;
>
> * calculating a minimum [or maximum] may be done as;
>  *RETAIN maximum; * [minimum];
>  *IF (FIRST.Date) THEN maximum = temp; * [minimum];
>  *ELSE IF (temp GT maximum) THEN maximum = temp; * [LT minimum]
> [minimum];
>  *IF (LAST.datevar) THEN OUTPUT; * Only at the last multiple input
> record, one
> *record per date;
>
> Obs    CONT_ID     datevar    PRJ_NBR          COUNT      perc
> 6      29453           09/11/2007    0501242            4       47.022
> 7      29453           10/07/2007    0501242            4       47.022
> 8      29453           10/07/2007    0501242            4       47.022
> 9      29453           10/21/2007    0501242            4       47.022
> 10    29785           09/20/2007    0002790            3        2.509
> 11    29785           10/06/2007    0002790            3        2.509
> 12    29785           10/20/2007    0002790            3        2.509
>  13  29785           09/20/2007    9900700            3       28.660
>  14  29785           10/06/2007    9900700            3       28.660
>  15  29785           10/20/2007    9900700            3       28.660
>
> Here is what i am getting:
> Obs    CONT_ID      PRJ_NBR  COUNT      perc     newvar
> 6     29453           0501242            4       47.022     17420
> 7    29453           0501242            4       47.022     34892
>  8   29453           0501242            4       47.022
> 17460            9    29785           0002790            3
> 2.509     17429
> 10    29785           0002790            3        2.509     17445
> 11    29785           0002790            3        2.509     17459
> 12    29785           9900700            3       28.660     17429
> 13    29785           9900700            3       28.660     17445
>  14   29785           9900700            3       28.660     17459
>
> What i need to get:
>
> CONT_ID      PRJ_NBR  COUNT      perc     newvar
> 29453           0501242            4       47.022     34892
> 29785           0002790            3        2.509     17459
> 29785           9900700            3       28.660     17459


gsc,

Instead of the datevar variable, which suddenly appears after having
sorted the dataset by cont_id and prj_nbr only, you need the first.
and last. of prj_nbr since that is your aggregation level.

It may help learning by putting the first. and last. variables (one
pair for every by variable) to the log or create variables
(first_cont_id = first.cont_id;).

For safety reasons use the same by statements in proc sort and the
datastep.

Hth,
Eric