Solved
Contributor
Posts: 39

# Help imputing minimum value

Hello all, here is what I have

YearVarValue
20003
20005
2000
200010
20017
20012
2002
20029
2002
20042
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!!

YearVarValue
20003
20005
20003
200010
20017
20012
20029
20029
20029
20042
20042

Accepted Solutions
Solution
‎03-25-2014 02:53 PM
Super User
Posts: 6,785

## Re: Help imputing minimum value

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;

All Replies
Solution
‎03-25-2014 02:53 PM
Super User
Posts: 6,785

## Re: Help imputing minimum value

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;

Super User
Posts: 23,776

## Re: Help imputing minimum value

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;

Contributor
Posts: 39

## Re: Help imputing minimum value

Thank you both

🔒 This topic is solved and locked.