New Contributor
Posts: 2

# how to calculate mean of two values from another table?

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!

Super User
Posts: 13,583

## Re: how to calculate mean of two values from another table?

[ Edited ]

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;

```
New Contributor
Posts: 2

## Re: how to calculate mean of two values from another table?

Thank you so much for replying. This is working.

Discussion stats
• 2 replies
• 110 views
• 0 likes
• 2 in conversation