Date:         Fri, 23 May 2008 10:13:57 -0700
Reply-To:     r_poetic <radford.schantz@MMS.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         r_poetic <radford.schantz@MMS.GOV>
Organization: http://groups.google.com
Subject:      Re: how to associate item sold dates and inventory dates
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

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.