topic How to use mean to calculate missing value in SAS Programming

How to use mean to calculate missing value

nwang5 — Wed, 22 Feb 2023 00:30:23 GMT

Hi SAS Communities, I tried to use the mean to represent the missing value, but it still showed there were missing value.

data elsa02(drop = a _a m);

_a = a;

do _N_ = 1 by 1 until (last.idauniq | a);
set elsa01(rename = drinkd = a);
by idauniq ;
end;

m = mean(_a, a);

do _N_ = 1 to _N_;
set elsa01(rename = drinkd = a);
drinkd = coalesce(a, m);
output;
end;

run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
444 at 935:8
NOTE: There were 45648 observations read from the data set WORK.ELSA01.
NOTE: There were 45648 observations read from the data set WORK.ELSA01.
NOTE: The data set WORK.ELSA02 has 45648 observations and 50 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 second

Re: How to use mean to calculate missing value

Quentin — Wed, 22 Feb 2023 02:33:02 GMT

It's hard to tell from your code what you are trying to do, or what mean you want to calculate.

Could you edit your question to show some sample data (5 - 10 records made with a data step with cards statement), then run your code on that data, and describe how your results differ from the results you want?

It looks like your code should run without errors. But I can't quite tell the intent.

Re: How to use mean to calculate missing value

PGStats — Wed, 22 Feb 2023 04:53:40 GMT

Maybe all you want is :

proc sql;
create table elsa02 as
select *,
	coalesce(drinkd, mean(drinkd)) as newDrinkd
from elsa01
group by idauniq;
quit;

i.e. replace missing drinkd values with the mean of drinkd values in the same idauniq group.