Date:         Fri, 23 May 2008 09:52:18 -0700
Reply-To:     "Ramsey, Julia" <Julia.Ramsey@CXTOWN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Ramsey, Julia" <Julia.Ramsey@CXTOWN.COM>
Subject:      Re: how to associate item sold dates and inventory dates
Comments: To: r_poetic <radford.schantz@MMS.GOV>
In-Reply-To:  A<3388f789-2233-4666-947c-0874517c2c64@34g2000hsh.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

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.Ramsey@cxtown.com

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
r_poetic
Sent: Friday, May 23, 2008 9:29 AM
To: SAS-L@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!