DATA Step, Macro, Functions and more

Left Join and aggregate on date intervals

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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: 1,760

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

View solution in original post


All Replies
PROC Star
Posts: 1,760

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: 19,822

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: 19,822

Re: need some help with left join tables

Let's try turning that into words Smiley Happy

 

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: 19,822

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: 1,760

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,041

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
  • 335 views
  • 0 likes
  • 4 in conversation