LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 2011, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Thu, 3 Mar 2011 12:04:45 -0800
Reply-To:   "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:   Re: proc sql
Comments:   To: John Mike <sasslick@GMAIL.COM>
In-Reply-To:   A<AANLkTikz+SZ_UbnG1JrGRBtAaNfVaJtQyqg=E-40Kzg1@mail.gmail.com>
Content-Type:   text/plain; charset="us-ascii"

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*


Back to: Top of message | Previous page | Main SAS-L page