Date:         Thu, 3 Mar 2011 15:17:35 -0500
Reply-To:     John Mike <sasslick@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         John Mike <sasslick@GMAIL.COM>
Subject:      Re: proc sql
Comments: To: "Terjeson, Mark" <Mterjeson@russell.com>
In-Reply-To:  <16FD64291482A34F995D2AF14A5C932C0A3F0337@MAIL002.prod.ds.russell.com>
Content-Type: text/plain; charset=ISO-8859-1

mark
I need one combined result ( A compared to B & c)
thanks


On Thu, Mar 3, 2011 at 3:04 PM, Terjeson, Mark <Mterjeson@russell.com>wrote:

> Hi John,
>
> Was not sure if you needed one combined result
> or separate results. The sample below may have
> enough to give you the mechanics and you can go
> from there.  (you can always ask for more help)
>
> The key here is that the INTNX() function can
> give you offsets from a date on-the-fly.
>
> I also presumed that this was to be done within
> the sid groups as opposed to dates of the whole
> file.
>
>
>
> data A;
>    input sid date date9.;
>    format date date9.;
> cards;
> 1 01JUL2009
> 1 10NOV2009
> 2 04MAY2009
> 2 11DEC2009
> 2 13SEP2009
> 3 02FEB2009
> 3 03JUN2009
> ;
> run;
>
>
> data B;
>    input sid date date9.;
>    format date date9.;
> cards;
> 1 25JUN2009
> 1 1NOV2009
> 2 24MAY2009
> 2 10SEP2009
> 3 22FEB2009
> 3 18JUN2009
> ;
> run;
>
>
>
> data C;
>    input sid date date9.;
>    format date date9.;
> cards;
> 1 25JUL2009
> 1 11OCT2009
> 2 20MAY2009
> 3 02FEB2009
> 3 28JUN2009
> ;
> run;
>
>
>    * for visibility of what we are doing ;
> proc sql;
>    create table test as
>    select a.*,
>           intnx('day',b.date,-14) as minus14 format=date9.,
>           b.date as dateB,
>           intnx('day',b.date,+14) as plus14 format=date9.,
>           a.date between intnx('day',b.date,-14) and
> intnx('day',b.date,+14) as result
>    from A
>    left join B
>    on a.sid eq b.sid
>    ;
> quit;
>
>
>
>    * final SQL query ;
> proc sql;
>    create table A_B_result as
>    select a.*
>    from A
>    join B
>    on a.sid eq b.sid
>    and a.date between intnx('day',b.date,-14)
>                   and intnx('day',b.date,+14)
>    ;
> quit;
>
>
>
>    * final SQL query ;
> proc sql;
>    create table A_C_result as
>    select a.*
>    from A
>    join C
>    on a.sid eq c.sid
>    and a.date between intnx('day',c.date,-14)
>                   and intnx('day',c.date,+14)
>    ;
> quit;
>
>
>
>
> Hope this is helpful.
>
>
> Mark Terjeson
> Investment Business Intelligence
> Investment Management & Research
> Russell Investments
> 206-505-2367
>
>
> Russell
> Global Leaders in Multi-Manager Investing
>
>
>
>
>
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> John Mike
> Sent: Thursday, March 03, 2011 10:37 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: proc sql
>
> Hello All
> I have following datasets
>
> dataset A
>
> sid        dateA
> 1          01JUL2009
> 1         10NOV2009
> 2         04MAY2009
> 2         11DEC2009
> 2         13SEP2009
> 3         02FEB2009
> 3         03JUN2009
>
> dataset B
>
> sid       dateB
> 1          25JUN2009
> 1         1NOV2009
> 2         24MAY2009
> 2         10SEP2009
> 3         22FEB2009
> 3         18JUN2009
>
>
> dataset C
>
> sid       dateC
> 1          25JUL2009
> 1         11OCT2009
> 2         20MAY2009
> 3         02FEB2009
> 3         28JUN2009
>
> I need to create a new dataset with the records from dataset A. The
> dates of
> the dataset A should be in between *dateB-14 and dateB+14 *  or  in
> between
> *dateC-14 and dateC+14*
> *Can any one help me with this? *
> *Thanks*
> *John*
>