topic Re: Obtaining mean for variable number of variables per subject in SAS Programming

Obtaining mean for variable number of variables per subject

TDC — Wed, 19 Oct 2016 19:49:27 GMT

Dear All,

I am working on a project for which I have to get mean levels of a disease marker (let's call it DM). The issue is that the mean must only include measures before the individual developed disease, and the time each person may have developed disease differs. So, for example, if ID=1 has 7 time points where DM was assessed and this person developed disease at time 5, I would need the mean of DM from time 1 to time 4. If ID=2 has DM measured from time 1 to 6 and the person developed disease at time 3, I'd need the mean DM from time 1 to time 2. If another person did not develop disease, I'd get the mean of all the available DM measures. I am really stuck on how to do this. Any help would be greatly appreciated!

Many thanks!

Re: Obtaining mean for variable number of variables per subject

PGStats — Wed, 19 Oct 2016 19:57:39 GMT

It all depends on your data structure. Please post an example.

Re: Obtaining mean for variable number of variables per subject

TDC — Wed, 19 Oct 2016 20:07:48 GMT

Dear PGStats,

I have a wide data set (an example is given below).

data test;

input id dm1-dm7 disease time;

cards;

1 35 38 40 43 53 55 55 1 7

2 25 29 20 . 25 30 28 0 .

3 38 40 40 50 48 30 35 1 5

4 44 43 41 39 38 . 37 1 3

5 28 25 30 28 30 27 31 0 .

6 38 40 45 . 50 29 28 1 5

;

run;

Above, ID 1 developed disease at the 7th time point, so I'd need to get the means of DM1-DM6.

ID 2 did not develop disease, so I'd need the means of all available DM values.

ID 3 developed disease at time 5 so I'd get the mean of DM from times 1 to 4, etc.

Thank you for any help!

TDC.

run;

Re: Obtaining mean for variable number of variables per subject

Shmuel — Wed, 19 Oct 2016 20:29:09 GMT

In case there is only one record per ID (and MEAN means AVERAGE)

than you can do it by:

data test;

input id dm1-dm7 disease time;

array dm {*} dm1-dm7;

sum_dm = 0;

if time > 1 then do;

do i=1 to (time -1);

sum_dm = sum(of sum_dm, dm(i));

end;

MEAN = sum_dm / (time -1);

end;

else MEAN = .;

cards;

1 35 38 40 43 53 55 55 1 7

2 25 29 20 . 25 30 28 0 .

3 38 40 40 50 48 30 35 1 5

4 44 43 41 39 38 . 37 1 3

5 28 25 30 28 30 27 31 0 .

6 38 40 45 . 50 29 28 1 5

;

run;

Re: Obtaining mean for variable number of variables per subject

ballardw — Wed, 19 Oct 2016 20:29:11 GMT

Something like this perhaps:


data want;
   set test;
   array dm dm1-dm7;
   if disease and not missing(time) then do;
      do i=1 to time;
         dmsum=sum(dmsum,dm[i]);
      end;
      dmmean= dmsum/time;
   end;
   drop i;
run;

Re: Obtaining mean for variable number of variables per subject

Reeza — Wed, 19 Oct 2016 20:30:23 GMT

Transpose and use proc means.

proc sort data=test;
by id disease time;
run;

proc transpose data=test out=long;
by id disease time;
var dm1-dm7;
run;

data long;
set long;
*calculate measurement time from variable name, generally bad practice to store data in column headers;
dm_time=input(compress(_name_, , 'kd'), 8.);

run;

proc means data=long nway mean n std;
class id disease ;
var col1;
where dm_time < time or time=.;
run;

Re: Obtaining mean for variable number of variables per subject

PGStats — Wed, 19 Oct 2016 21:43:02 GMT

If you want to keep it wide:

data want;
set test;
array dm dm1-dm7;
array x{7};
do i = 1 to coalesce(time, dim(dm)+1) - 1;
    x{i} = dm{i};
    end;
meanDm = mean(of x{*});
drop i x:;
run;

Re: Obtaining mean for variable number of variables per subject

PGStats — Wed, 19 Oct 2016 21:48:31 GMT

@Shmuel, your denominator will be wrong if missing dm values occur before time. If you want to calculate your own average, you must keep track of the count of non-missing dm values as well as the sum of dm values.

Re: Obtaining mean for variable number of variables per subject

TDC — Thu, 20 Oct 2016 12:10:20 GMT

Thank you All so much!! I really appreciate your help!!

TDC.