08-30-2017 12:06 PM
I have two tables for example
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:
create table test as
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;
Any help would be very much appreciated!!
08-30-2017 01:20 PM - edited 08-30-2017 01:35 PM
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;