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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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