DATA Step, Macro, Functions and more

how to calculate mean of two values from another table?

Reply
New Contributor C_V
New Contributor
Posts: 2

how to calculate mean of two values from another table?

Hello,

I have two tables for example

Table_A:

 

BEG_PERIODEND_PERIODValue
1-Apr-121-Apr-131,181.00
1-Apr-131-Apr-141,214.99
1-Apr-141-Apr-151,256.47
1-Apr-151-Apr-161,292.00
1-Apr-161-Apr-171,324.30
1-Apr-171-Apr-181,357.41
1-Apr-181-Apr-191,391.34
1-Apr-191-Apr-201,426.13

 

Table_B:

Beg_PeriodEnd_period
1-Oct-141-Oct-15
1-Oct-151-Oct-16
1-Oct-161-Oct-17
1-Oct-171-Oct-18
1-Oct-181-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 C_V
New Contributor
Posts: 2

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

Thank you so much for replying. This is working.

Ask a Question
Discussion stats
  • 2 replies
  • 110 views
  • 0 likes
  • 2 in conversation