topic Re: Impute missing value with mean of other available variable values if one of them is missing in SAS Programming

Impute missing value with mean of other available variable values if one of them is missing

chinna0369 — Thu, 01 Aug 2024 20:04:48 GMT

Hi,

I have 10 variables v1 v2 v3 v4 v5 v6 v7 v8 v9 v10. I want to assign mean of others when one of them is missing. I am uisng below code but it looks like it is not working. Can you please help me with this.

proc stdize data=have reponly method=mean out=want;
	var v1-v10;
run;

Re: Impute missing value with mean of other available variable values if one of them is missing

Kurt_Bremser — Thu, 01 Aug 2024 20:15:38 GMT

Where is usable example data in a DATA step with DATALINES?

Where is the log?

Where is a clear description why the outcome does not meet your expectations?

We really should not need to ask for this from a user with 147 posts here.

Re: Impute missing value with mean of other available variable values if one of them is missing

ballardw — Thu, 01 Aug 2024 21:03:33 GMT

@chinna0369 wrote:

Hi,

I have 10 variables v1 v2 v3 v4 v5 v6 v7 v8 v9 v10. I want to assign mean of others when one of them is missing. I am uisng below code but it looks like it is not working. Can you please help me with this.
proc stdize data=have reponly method=mean out=want;
	var v1-v10;
run;

What exactly is "mean of others" in terms of calculations?

The code you show tells SAS to replace the missing values of each variable with mean of that variable.

Example small data set:

data have;
  input obs v1 v2;
datalines;
1   1   .
2   1   2
3   1   3
4   1   4
5   1   5
6   .   6
7   1   7
8   1   8
9   1   9
10  1   10
;

Proc stdize data=have reponly method=mean out=want;
   var v1 v2;
run;

This imputes a value of 1 for variable V1 on obs 6 and for V2 on obs 1 imputes 6.

What values would you want given your " mean of others " rule?

Re: Impute missing value with mean of other available variable values if one of them is missing

chinna0369 — Thu, 01 Aug 2024 21:38:12 GMT

Sorry, for not giving proper details. Below is the data and procedure I am using result should be mean for missing variable which is 1/8=0.125 here.

data have;
input id 1-2 visitn 3-7 v1 v2 v3 v4 v5 v6 v7 v8 v9;
datalines;
1 1320 0 0 . 0 0 0 0 0 1 0
2 1160 0 . 0 1 0 0 0 0 0 0
;
run;

proc stdize data=have reponly method=mean out=want;
	var v1 v2 v3 v4 v5 v6 v7 v8 v9;
run;

Re: Impute missing value with mean of other available variable values if one of them is missing

chinna0369 — Thu, 01 Aug 2024 21:48:30 GMT

And this is my log:

Re: Impute missing value with mean of other available variable values if one of them is missing

ballardw — Thu, 01 Aug 2024 21:54:39 GMT

Not a job for Stdize. Your rule is observation based not variable across observations.

data want;
   set have;
   array v (*) v1-v9;
   /* calculate the mean of the values present before
      changing any of them
   */
   replacement = mean (of v(*));
   /* replace the missing values with the replacement value*/
   do i=1 to dim(v);
      if missing(v[i]) then v[i]=replacement;
   end;
   drop replacement;
run;

Arrays are often indicated as a choice when doing similar things to multiple variables.

Re: Impute missing value with mean of other available variable values if one of them is missing

data_null__ — Thu, 01 Aug 2024 22:02:19 GMT

CALL STDIZE

data have;
   input id avisitn v1 v2 v3 v4 v5 v6 v7 v8 v9;
   call stdize('none','replace','missing=',mean(of v:),of v:);
   datalines;
1 1320 0 0 . 0 0 0 0 0 1 0
2 1160 0 . 0 1 0 0 0 0 0 0
;
   run;
proc print;
   run;

Re: Impute missing value with mean of other available variable values if one of them is missing

chinna0369 — Thu, 01 Aug 2024 22:27:25 GMT

Thank you so much for your solution.

Re: Impute missing value with mean of other available variable values if one of them is missing

Kurt_Bremser — Thu, 01 Aug 2024 22:42:07 GMT

Transpose the dataset to long, and use BY in the procedure.