| 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 |
|---|
|
| 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*
| 
