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!
... View more