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
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 |
How many Cummulative_news_dayxxx columns do you expect?
You could have millions considering all the combinations of days that are possible..
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?
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.
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 |
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;
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.
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.