how to calculate mean of two values from another table?

[C_V]

Hello,

I have two tables for example

Table_A:

BEG_PERIOD	END_PERIOD	Value
1-Apr-12	1-Apr-13	1,181.00
1-Apr-13	1-Apr-14	1,214.99
1-Apr-14	1-Apr-15	1,256.47
1-Apr-15	1-Apr-16	1,292.00
1-Apr-16	1-Apr-17	1,324.30
1-Apr-17	1-Apr-18	1,357.41
1-Apr-18	1-Apr-19	1,391.34
1-Apr-19	1-Apr-20	1,426.13

Table_B:

Beg_Period	End_period
1-Oct-14	1-Oct-15
1-Oct-15	1-Oct-16
1-Oct-16	1-Oct-17
1-Oct-17	1-Oct-18
1-Oct-18	1-Oct-19

I would like to add another column in Table_B that calculates the average of the Value column from Table_A based on the periods in Table_B.

So the first value of the new column in Table_B should be the average of 1,256.47 and 1,292.0, the second value should be the average of 1,292.00 and 1,324.30 etc..

I have this code but it didn't work:

proc sql;
create table test as
select A.*,
mean(B.Value) as Test
from Table_B as A left join Table_A as B
on B.Beg_Period < A.Beg_Period < B.End_Period and
 B.Beg_Period < A.End_Period < B.End_Period;
quit;

Any help would be very much appreciated!!

Thank you!

[ballardw]

Are your date values SAS dates or character?

If the rule to match the results on a 6-month difference this works for the example data. You would have to do the code separately, your choice of methods to get the 2 periods averaged. I put it in the data step to read values since I had to create data steps to have data to test. This is how you should post example data. If I make an assumption, such as your dates should be SAS date values but they aren't then the code will likely not work with your actual data.

data table_a;
   informat BEG_PERIOD END_PERIOD anydtdte. Value comma12.;
   format BEG_PERIOD END_PERIOD date9.;
   input BEG_PERIOD END_PERIOD Value ;
   avg = mean(value, lag(value));
   if _n_=1 then call missing(avg);
datalines;
1-Apr-12 1-Apr-13 1,181.00 
1-Apr-13 1-Apr-14 1,214.99 
1-Apr-14 1-Apr-15 1,256.47 
1-Apr-15 1-Apr-16 1,292.00 
1-Apr-16 1-Apr-17 1,324.30 
1-Apr-17 1-Apr-18 1,357.41 
1-Apr-18 1-Apr-19 1,391.34 
1-Apr-19 1-Apr-20 1,426.13 
;
run;
 

data Table_B;
   informat BEG_PERIOD END_PERIOD anydtdte. ;
   format BEG_PERIOD END_PERIOD date9.;
   input BEG_PERIOD END_PERIOD ;
datalines;
1-Oct-14 1-Oct-15 
1-Oct-15 1-Oct-16 
1-Oct-16 1-Oct-17 
1-Oct-17 1-Oct-18 
1-Oct-18 1-Oct-19 
;
run;

proc sql;
   create table want as
   select b.*, a.avg
   from table_b as b 
        left join
        table_a as a
        on b.end_period = intnx('month',a.beg_period,6,'same')
   ;
quit;
 

[C_V]

Thank you so much for replying. This is working.