topic Re: fill missing values with known non-missing values in SAS Programming

fill missing values with known non-missing values

phdstudent123 — Thu, 04 Jan 2018 05:48:11 GMT

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 .

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.

Re: fill missing values with known non-missing values

phdstudent123 — Thu, 04 Jan 2018 05:51:07 GMT

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

Re: fill missing values with known non-missing values

stat_sas — Thu, 04 Jan 2018 06:10:22 GMT

Hi,

Please try this.

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

Re: fill missing values with known non-missing values

novinosrin — Thu, 04 Jan 2018 06:38:22 GMT

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

Re: fill missing values with known non-missing values

pau13rown — Thu, 04 Jan 2018 06:45:58 GMT

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!

Re: fill missing values with known non-missing values

phdstudent123 — Fri, 05 Jan 2018 00:41:28 GMT

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

Re: fill missing values with known non-missing values

phdstudent123 — Fri, 05 Jan 2018 00:41:46 GMT

Thank you, that works as well!

Re: fill missing values with known non-missing values

Patrick — Fri, 05 Jan 2018 03:12:34 GMT

@phdstudent123

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