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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.