fill missing values with known non-missing values

[phdstudent123]

I run into a problem of filling the missing values with non-missing values. What's tricky here is the nonmissng values are random, so I cannot determine whether to use the first non-missing value or the last non-missing value.

Example: Variable is my variable of interest

ID company variable
1 A 123
1 A . 
1 A 123
1 A 123
1 A .
2 B 321
2 B .

2 B .

2 B .

What i want here is to fill the variable with 123 if the id=1 and variable with 321 if id=2.

Any suggestion will be appreciated ! Thanks.

[phdstudent123]

Forgot to mention that variable is not fixed and can be solely determined by known variables only with the same ID number.

[stat_sas · Accepted solution]

Hi,

Please try this.

proc sql;
create table want as
select ID, company, max(variable) as variable from have
group by id;
quit;

[phdstudent123]

Thank you! That works. Btw, does that also apply to string variables too?

[novinosrin]

is it that simple to fill with max of values as your sample data suggests?

[pau13rown]

not sure i understand the data but id remove duplicates then merge back on

proc sort data=A;

by id company descending variable;

run;

proc sort data=A out=B nodupkey;

by id company;

run;

data C;

merge A (drop=variable) B;

by id company;

run;

but the other solution suggested is more pithy!

[phdstudent123]

Thank you, that works as well!

[Patrick]

@phdstudent123

Then please accept one of the answers as solution so this question gets closed.