Date:         Mon, 26 Jan 2009 21:31:36 -0500
Reply-To:     Akshaya Nathilvar <akshaya.nathilvar@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Akshaya Nathilvar <akshaya.nathilvar@GMAIL.COM>
Subject:      Re: Merge 2 tables using middle charge date fall in between first
              and last date.
Comments: To: Pareen Patel <mditconsult@gmail.com>
In-Reply-To:  <200901270213.n0QLjOLZ014542@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

Two things:
One is month should be 3 characters(Jan,Mar,Jul) to read the dates
correctly. Second inner join be based on card members and the charge_dt
occur b/w fi.dt and la.dt (on a.cm=b.cm and (charge_dt between firstdate and
lastdate)).

AkshayA!

On Mon, Jan 26, 2009 at 9:13 PM, Pareen Patel <mditconsult@gmail.com> wrote:

> I have this variables in table A.
> CM firstdate         lastdate
> 1    01march2008      14nov2008
> 2    22july2008      19aug2008
> 3    01jan2008      14sep2008
>
> I have also table B.
>
> Cm charge_dt
> 1    29may2008
> 2    01march2008
> 3    01march2008
> 1    01jan 2008
> 3    10sep2008
>
>
> I only want those card members whose charge_dt falss between first date and
> last date. like below
>  CM firstdate         lastdate    charge_dt
> 1    01march2008      14nov2008   29may2008
> 3    01jan2008      14sep2008     01march2008
> 3    01jan2008      14sep2008     10sep2008
>
>
> I wrote code below, but for some reason it worked before and it is giving
> me
> error now.
>
> Thanks
>
>
> proc sql;
> create table final as
> select
>       b.*,
>    a.firstdate,
>    a.lastdate
> from spend a
> inner join test b
>
> on firstdate<=b.charge_dt<=lastdate
>
> order by cm;
> quit;
>