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 |
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;
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;
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;
Thank you both
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.