Replace missing values of one variable with non-missing values of the other variable

[Anna_DY]

I need to replace the missing  values in Rate1 with Rate2 ( see attached sample data) . I have tried  two  ways as below, but none of them worked.

Method 1: 

 data test; set test;

NewRate = coalescec(Rate1, Rate2);

run;

 Method 2:

proc sql; create table test2 as select *,
case when not missing(RATE1) then Rate1
else Rate2
end as NewRate from test order by ID, Date2;

However, Newrate only picked up the values from Rate1, but not  Rate2.

Thanks!

[Mazi]

Hi, 

Instead of attaching a dataset are you able to create sample data for us using datalines?

The code you have here should work, I suspect you may have special/non printable characters.

data test;
	infile datalines dlm=',' missover;
	input Rate1 $ Rate2 $;
	datalines;
abcd123,efgh456
ijkl789,mnop000
	,qrst123
;
run;
	
data test2; 
	set test;
	rate1=compress(rate1,,'kw');
	NewRate = coalescec(Rate1, Rate2);
run;

Can you try adding compress to rate1 and see if that fixes your issue?

[Anna_DY]

Thak you for your reply!

[DrAbhijeetSafai]

Hi @Anna_DY , you are using coalesce function. "The COALESCE function checks the value of each column in the order in which they are listed and returns the first nonmissing value. " I am not sure if you want to do that. 

It would be really great if you can share what you have and what you want. 

Thank you.

- Dr. Abhijeet Safai

[ballardw]

You should provide actual examples of where your first attempt with Coalescec didn't work. I'm not opening your data set but if the Rate variables are numeric the function should be COALESCE, the Coalescec is for character values.

Also, ANY time you test code DO not use the output data set name the same as the input.

When you use:

data test; set test;

The Test is completely replaced. So logic errors the first time may have corrupted your data so that you no longer have the same data to attempt to accomplish what you want. Really, in general this code makes it extremely difficult to track down where problems occur. I have seen people use this the same structure replacing the data set in multiple 
Data step blocks and involving Keep and Drop and then asking where their variables went (which had been explicitly dropped in one step and then not kept in another). And since the data was so badly mangled they had to start by rereading the raw source data files again.

[Anna_DY]

Thank you for your advice! 

[Kurt_Bremser]

Please do not attach dataset files.

Why?

To view your file, I would

• have to get up from bed 😉
• walk upstairs to my home office
• fire up my Mac
• download your file
• log in to SAS On Demand
• upload the file

since here, I am on my tablet where I can't use ODA.

If you provide your dataset as DATA step code with DATALINES, I can read that right here and get a clear picture of your dataset in my mind.

Do you really store rates as character (because you use COALESCEC)?

[FreelanceReinh · Accepted solution]

Hello @Anna_DY,

Your "missing values" are in fact single periods, preceded by blanks. To help SAS recognize these strings as missing, you can read them with the standard $w. informat and then apply the COALESCEC or MISSING function:

data want;
set test;
length NewRate $10;
NewRate = coalescec(input(Rate1,$10.), Rate2);
run;

I have also added a LENGTH statement because otherwise the COALESCEC function would define the length of variable NewRate as 200.

[Anna_DY]

Thank you, Jade, It worked perfectly