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