DATA Step, Macro, Functions and more

Help imputing minimum value

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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: 5,076

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;

View solution in original post


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

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: 17,771

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: 38

Re: Help imputing minimum value

Thank you both

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 238 views
  • 3 likes
  • 3 in conversation