topic Re: need some help with left join tables in SAS Programming

Left Join and aggregate on date intervals

ygity — Mon, 11 Apr 2016 04:01:47 GMT

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

Re: need some help with left join tables

ChrisNZ — Mon, 11 Apr 2016 03:02:00 GMT

How many Cummulative_news_dayxxx columns do you expect?

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

Re: need some help with left join tables

ygity — Mon, 11 Apr 2016 03:27:54 GMT

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

Re: need some help with left join tables

Reeza — Mon, 11 Apr 2016 03:37:56 GMT

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?

Re: need some help with left join tables

ygity — Mon, 11 Apr 2016 03:45:07 GMT

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

Re: need some help with left join tables

ChrisNZ — Mon, 11 Apr 2016 03:48:35 GMT

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

Re: need some help with left join tables

Reeza — Mon, 11 Apr 2016 03:50:25 GMT

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

Re: need some help with left join tables

ygity — Mon, 11 Apr 2016 03:53:32 GMT

yep because i want from day-1 to day3 of the L.date..

Re: need some help with left join tables

Reeza — Mon, 11 Apr 2016 04:02:44 GMT

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.

Re: need some help with left join tables

ygity — Mon, 11 Apr 2016 04:09:05 GMT

thanks so much... i am new to the SAS world.. thanks for the fast and helpful replys

Re: Left Join and aggregate on date intervals

Ksharp — Mon, 11 Apr 2016 08:40:36 GMT

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;