SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ygity
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

10 REPLIES 10
ChrisNZ
Tourmaline | Level 20

How many Cummulative_news_dayxxx columns do you expect?

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

ygity
Calcite | Level 5
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
Reeza
Super User

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?

 

 

ygity
Calcite | Level 5
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
Reeza
Super User

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

 

 

 

 

 

 

ygity
Calcite | Level 5
yep because i want from day-1 to day3 of the L.date..
Reeza
Super User

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. 

ygity
Calcite | Level 5
thanks so much... i am new to the SAS world.. thanks for the fast and helpful replys
ChrisNZ
Tourmaline | Level 20

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
Ksharp
Super User
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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