topic Re: Help imputing minimum value in SAS Programming

Help imputing minimum value

spirto — Tue, 25 Mar 2014 18:26:14 GMT

Hello all, here is what I have

Year	VarValue
2000	3
2000	5
2000
2000	10
2001	7
2001	2
2002
2002	9
2002
2004	2
2004

I would like to impute the missing values of VarValue for each year with the minimum VarValue for that year. Something like this below. A datastep would be preferable as I do not know SQL (but I am not opposed to SQL as long as I can modify it easily). Thanks!!

Year	VarValue
2000	3
2000	5
2000	3
2000	10
2001	7
2001	2
2002	9
2002	9
2002	9
2004	2
2004	2

Re: Help imputing minimum value

Astounding — Tue, 25 Mar 2014 18:53:17 GMT

Here's one fairly standard way to use a DATA step:

data want;

do until (last.year);

set have;

by year;

minimum_value = min(minimum_value, VarValue);

end;

do until (last.year);

set have;

by year;

if VarValue=. then VarValue = minimum_value;

output;

end;

drop minimum_value;

run;

Re: Help imputing minimum value

Reeza — Tue, 25 Mar 2014 19:24:45 GMT

Here's the SQL way.

proc sql;

create table want as

select a.year, a.varvalue, min(a.varvalue) as min, coalesce(a.varvalue, calculated min) as new_varValue

from want a

group by year;

quit;

Re: Help imputing minimum value

spirto — Tue, 25 Mar 2014 19:28:51 GMT

Thank you both