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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.