- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
dear forum...
i need some help on writeing up the code....
the L.* looks like this
Date Code
01/01/1998 A
the R.* looks like this
Date Code News
02/01/1998 A 5
03/01/1998 A 2
05/01/1998 A 1
the combined i want looks like this
Date Code Cummulative_news_day0_day5 Cummulative_news_day-1_day3
01/01/1998 A 8(5+2+1) 7(5+2)
where day 0 indicates the L.date and day1 day2 means the following dates... and day-1 means the previous day..
in the example here Cummulative_news_day-1_day3 it got no data from 31/12/1997...
i got stuck at this problem like a week...
thanks for your time...you the best
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Like this?
data L;
input DATE ddmmyy10. CODE $;
cards;
01/01/1998 A
run;
data R;
input DATE ddmmyy10. CODE $ NEWS;
cards;
02/01/1998 A 5
03/01/1998 A 2
05/01/1998 A 1
run;
proc sql;
select a.CODE
,a.DATE format=date9.
,sum( (0<=b.DATE-a.DATE<=5) * NEWS ) as DAY_0_5
,sum( (1<=b.DATE-a.DATE<=3) * NEWS ) as DAY_1_3
from L a
,R b
where L.CODE=R.CODE
and (0<=b.DATE-a.DATE<=5)
group by a.CODE, a.DATE;
quit;
CODE | DATE | DAY_0_5 | DAY_1_3 |
---|---|---|---|
A | 01JAN1998 | 8 | 7 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
How many Cummulative_news_dayxxx columns do you expect?
You could have millions considering all the combinations of days that are possible..
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Cumulative_news_day0_day5
Cumulative news from 01/01/1998 to 05/01/1998
Cumulative_news_day-1_day3
Cumulative news from 31/12/1997 to 03/01/1998
thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I think you need to explain your problem some more and provide some better sample data.
What's the logic on the join - by month? Those periods appear to overlap as well?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
and surrounding L.date there is the R table observations...
i want to have the cumulative R.news to be joined with L.*..
the cumulative day0_day5 means L.date to L.date+5...
and day-1_day3 means L.date-1 to L.date+3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I modified the subject of the thread to better reflect your problem - helps when searching in the future.
If Chris's solution works for you please mark it as the solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Like this?
data L;
input DATE ddmmyy10. CODE $;
cards;
01/01/1998 A
run;
data R;
input DATE ddmmyy10. CODE $ NEWS;
cards;
02/01/1998 A 5
03/01/1998 A 2
05/01/1998 A 1
run;
proc sql;
select a.CODE
,a.DATE format=date9.
,sum( (0<=b.DATE-a.DATE<=5) * NEWS ) as DAY_0_5
,sum( (1<=b.DATE-a.DATE<=3) * NEWS ) as DAY_1_3
from L a
,R b
where L.CODE=R.CODE
and (0<=b.DATE-a.DATE<=5)
group by a.CODE, a.DATE;
quit;
CODE | DATE | DAY_0_5 | DAY_1_3 |
---|---|---|---|
A | 01JAN1998 | 8 | 7 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data L;
input DATE ddmmyy10. CODE $;
cards;
01/01/1998 A
run;
data R;
input DATE ddmmyy10. CODE $ NEWS;
cards;
02/01/1998 A 5
03/01/1998 A 2
05/01/1998 A 1
run;
proc sql;
select l.*,
(select sum(news)
from r
where r.code=l.code and r.date between l.date and l.date+4 )
as Cummulative_news_day0_day5,
(select sum(news)
from r
where r.code=l.code and r.date between l.date+1 and l.date+2 )
as Cummulative_news_day1_day3
from l;
quit;