## Left Join and aggregate on date intervals

Solved
Occasional Contributor
Posts: 8

# Left Join and aggregate on date intervals

[ Edited ]

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
Solution
‎04-11-2016 12:08 AM
PROC Star
Posts: 2,339

## Re: need some help with left join tables

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

All Replies
PROC Star
Posts: 2,339

## Re: need some help with left join tables

How many Cummulative_news_dayxxx columns do you expect?

You could have millions considering all the combinations of days that are possible..

Occasional Contributor
Posts: 8

## Re: need some help with left join tables

nono... just these two here...
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
Super User
Posts: 23,683

## Re: need some help with left join tables

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?

Occasional Contributor
Posts: 8

## Re: need some help with left join tables

not by month... the L.date is the date of an event happened..
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
Super User
Posts: 23,683

## Re: need some help with left join tables

Let's try turning that into words

Join table1 to table2, summing the column B within 3 and 5 days of the event?

I think @ChrisNZ has the correct answer.

This confused me, since it's 1997....

ygity wrote:

Cumulative news from 31/12/1997 to 03/01/1998

Occasional Contributor
Posts: 8

## Re: need some help with left join tables

yep because i want from day-1 to day3 of the L.date..
Super User
Posts: 23,683

## Re: need some help with left join tables

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.

Occasional Contributor
Posts: 8

## Re: need some help with left join tables

thanks so much... i am new to the SAS world.. thanks for the fast and helpful replys
Solution
‎04-11-2016 12:08 AM
PROC Star
Posts: 2,339

## Re: need some help with left join tables

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
Super User
Posts: 10,766

## Re: Left Join and aggregate on date intervals

``````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;``````
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 10 replies
• 382 views
• 0 likes
• 4 in conversation