Date:         Fri, 23 May 2008 13:27:51 -0400
Reply-To:     Nathaniel.Wooding@DOM.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Nat Wooding <Nathaniel.Wooding@DOM.COM>
Subject:      Re: how to associate item sold dates and inventory dates
Comments: To: r_poetic <radford.schantz@MMS.GOV>
In-Reply-To:  <40ec4f75-b8be-4e1b-bead-00462670e351@k37g2000hsf.googlegroups.com>
Content-Type: text/plain; charset="US-ASCII"

Try the following. It assumes that both data sets are sorted;

data itemsales;
       input id  date date9.;
       seldate=date;
 cards;
1 20MAY2007
2 27MAY2007
3 29MAY2007
4 30MAY2007
run;

 data inventory;
       input  date date9.;
       invdate=date;
 cards;
10MAY2007
14MAY2007
20MAY2007
22MAY2007
run;
Data all;
      set itemsales inventory;
      by date;
      format date seldate invdate lastinv date9.;
      if id = . then lastinv=date;
      retain lastinv;
      if id then output;

      * for production, turn turn this on   drop date invdate;
run;
proc print
run;

Nat Wooding
Environmental Specialist III
Dominion, Environmental Biology
4111 Castlewood Rd
Richmond, VA 23234
Phone:804-271-5313, Fax: 804-271-2977



             r_poetic
             <radford.schantz@
             MMS.GOV>                                                   To
             Sent by: "SAS(r)          SAS-L@LISTSERV.UGA.EDU
             Discussion"                                                cc
             <SAS-L@LISTSERV.U
             GA.EDU>                                               Subject
                                       Re: how to associate item sold
                                       dates and inventory dates
             05/23/2008 01:13
             PM


             Please respond to
                 r_poetic
             <radford.schantz@
                 MMS.GOV>






On May 23, 12:52 pm, Julia.Ram...@CXTOWN.COM ("Ramsey, Julia") wrote:
> Hello,
>
> How about something like this?
>
> data itemsales;
> input id selldate date9.;
> cards;
> 1 20MAY2007
> 2 27MAY2007
> 3 29MAY2007
> 4 30MAY2007
> ;
> run;
>
> data inventory;
> input id invdate date9.;
> cards;
> 1 10MAY2007
> 1 14MAY2007
> 1 22MAY2007
> 2 20MAY2007
> 2 22MAY2007
> 3 18MAY2007
> 3 20MAY2007
> 4 12MAY2007
> 4 19MAY2007
> 4 25MAY2007
> ;
> run;
>
> proc sql;
> create table lastinventory as select distinct
> t1.id,
> t1.selldate format date9.,
> max(t2.invdate) as lastinv format date9.
> from itemsales t1, inventory t2
> where t1.id = t2.id and
>          t1.selldate > t2.invdate
> group by t1.id
> order by t1.id;
> quit;
>
> OUTPUT:
> id      selldate        lastinv
> 1       20MAY2007       14MAY2007
> 2       27MAY2007       22MAY2007
> 3       29MAY2007       20MAY2007
> 4       30MAY2007       25MAY2007
>
> .*^*.*^*.*^*.*^*.*^*.
> Julia Ramsey
> Julia.Ram...@cxtown.com
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SA...@LISTSERV.UGA.EDU] On Behalf Of
>
> r_poetic
> Sent: Friday, May 23, 2008 9:29 AM
> To: SA...@LISTSERV.UGA.EDU
> Subject: how to associate item sold dates and inventory dates
>
> A problem that ought to be easy to solve but I can't think of answer
> --
> I have 2 datasets, A lists items by ID number and, for each item, the
> date it was sold.
> B lists dates on which the story took an inventory of items (but just
> the date of the inventory, no list of items in stock at the time).
> I want to identify, for each item sold, the date of the last inventory
> prior to the date it was sold, and add that inventory date to the
> record for the item.  I presume DATA steps would be used, but perhaps
> not.  Suggestion?
> Thanks!- Hide quoted text -
>
> - Show quoted text -

Thanks -- Unless I've missed your point, I think my inventory dates
dataset is not quite the same as your inventory set because mine is
just the dates on which inventory was reviewed; that is, there are no
item ID's in it.  That is, my two sets are like:

 data itemsales;
 input id selldate date9.;
 cards;
 1 20MAY2007
 2 27MAY2007
 3 29MAY2007
 4 30MAY2007
and

 data inventory;
 input invdate date9.;
 cards;
 10MAY2007
 14MAY2007
 20MAY2007
 22MAY2007

Maybe the crux of the problem is that there is no common key for the
two sets, at least initially.


-----------------------------------------
CONFIDENTIALITY NOTICE:  This electronic message contains
information which may be legally confidential and/or privileged and
does not in any case represent a firm ENERGY COMMODITY bid or offer
relating thereto which binds the sender without an additional
express written confirmation to that effect.  The information is
intended solely for the individual or entity named above and access
by anyone else is unauthorized.  If you are not the intended
recipient, any disclosure,  copying, distribution, or use of the
contents of this information is prohibited and may be unlawful.  If
you have received this electronic  transmission in error, please
reply immediately to the sender that you have received the message
in error, and delete it.  Thank you.