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.
Hi,
Please try this.
proc sql;
create table want as
select ID, company, max(variable) as variable from have
group by id;
quit;
Forgot to mention that variable is not fixed and can be solely determined by known variables only with the same ID number.
Hi,
Please try this.
proc sql;
create table want as
select ID, company, max(variable) as variable from have
group by id;
quit;
Thank you! That works. Btw, does that also apply to string variables too?
is it that simple to fill with max of values as your sample data suggests?
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!
Then please accept one of the answers as solution so this question gets closed.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.