mean of 10 variables with different starting point (SAS)

Question

I have 18 numerical variables pm25_total2000 to pm25_total2018

Each person have a starting year between 2013 and 2018, we can call that variable "reqyear".

Now I want to calculate mean for each persons 10 years before the starting year.

For example if a person have starting year 2015 I want mean(of pm25_total2006-pm25_total2015)

Or if a person have starting year 2013 I want mean(of pm25_total2004-pm25_total2013)

How to do this?

data _null_;
 set scapkon;
   reqyear=substr(iCDate,1,4)*1;
 call symput('reqy',reqyear);
 run;


data scatm;
 set scapkon;
 
/* Medelvärde av 10 år innan rekryteringsår */
pm25means=mean(of pm25_total%eval(&reqy.-9)-pm25_total%eval(&reqy.));

run;

%eval(&reqy.-9) will be constant value (the same value for all as for the first person) , in my case 2007 That doesn't work.

Answer 1

NOTE: My first answer did not address the OP's question, so this a redux.

For this solution, I used Richard's code for generating test data. However, I added a line to randomly add missing values.

      x = _n_;
      if ranuni(1) < .1 then x = .;
      _n_+1;

This alternative does not perform any checks for missing values. The sum() and n() functions inherently handle missing values appropriately. The loop over the dynamic slice of the data array only transfers the value to a temporary array. The final sum and count is performed on the temp array outside of the loop.

data want;
set have;
array x(2000:2018) x:;
array t(10) _temporary_;

j = 1;
do i = start-9 to start;
    t(j) = x(i);
    j + 1;
end;

sum = sum(of t(*));
cnt = n(of t(*));
mean = sum / cnt;

drop x: i j;
run;

Result:

id  start   sum cnt mean
1   2014    72  7   10.285714286
2   2015    305 10  30.5
3   2016    458 9   50.888888889
4   2017    631 9   70.111111111

Answer 2

• use an array and loop
• index the array with years
• accumulate the sum of the values
• accumulate the count to account for any missing values
• divide to obtain the mean value

data want;

set have;

array _pm(2000:2018) pm25_total2000 - pm25_total2018;

do year=reqyear to (reqyear-9) by -1;
    *add totals;
    total = sum(total, _pm(year));
    *add counts;
    nyears = sum(nyears,not missing(_pm(year)));
end;

*accounts for possible missing years;
mean = total/nyears;

run;

Note this loop goes in reverse (start year to 9 years previous) because it's slightly easier to understand this way IMO. If you have no missing values you can remove the nyears step, but not a bad thing to include anyways.

Answer 3

You can compute the mean with a traditional loop.

data want;
  set have;
  array x x2000-x2018;

  call missing(sum, mean, n);
  do _n_ = 1 to 10;
    v = x ( start - 1999 -_n_ );
    if not missing(v) then do;
      sum + v;
      n + 1;
    end;
  end;

  if n then mean = sum / n;
run;

If you want to flex your SAS skill, you can use POKE and PEEK concepts to copy a fixed length slice (i.e. a fixed number of array elements) of an array to another array and compute the mean of the slice.

Example:

You will need to add sentinel elements and range checks on start to prevent errors when start-10 < 2000.

data have;
  length id start x2000-x2018 8;
  do id = 1 to 15;
    start = 2013 + mod(id,6);
    array x x2000-x2018;
    do over x;
      x = _n_;
      _n_+1;
    end;
    output;
  end;

  format x: 5.;
run;

data want;
  length id start mean10yrPriorStart 8;

  set have;
  array x x2000-x2018;
  array slice(10) _temporary_;

  call pokelong (
         peekclong ( addrlong ( x(start-1999-10) ) , 10*8 ) ,
         addrlong ( slice (1))
       );

  mean10yrPriorStart = mean(of slice(*));
run;