fill missing values with known non-missing values

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

fill missing values with known non-missing values

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
B .

B .

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.


Accepted Solutions
Solution
‎01-05-2018 12:22 AM
Trusted Advisor
Posts: 1,263

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

Hi,

 

Please try this.

 

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

View solution in original post


All Replies
New Contributor
Posts: 4

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

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

Solution
‎01-05-2018 12:22 AM
Trusted Advisor
Posts: 1,263

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

Hi,

 

Please try this.

 

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

New Contributor
Posts: 4

Re: fill missing values with known non-missing values

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

 

 

PROC Star
Posts: 829

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

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

Contributor
Posts: 28

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

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!

New Contributor
Posts: 4

Re: fill missing values with known non-missing values

Posted in reply to PaulBrownPhD
Thank you, that works as well!
Respected Advisor
Posts: 4,278

Re: fill missing values with known non-missing values

Posted in reply to phdstudent123

@phdstudent123

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

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 229 views
  • 0 likes
  • 5 in conversation