topic Re: Replace missing values of one variable with non-missing values of the other variable in SAS Programming

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

Anna_DY — Fri, 15 Mar 2024 03:59:14 GMT

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!

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

Mazi — Fri, 15 Mar 2024 05:23:41 GMT

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?

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

DrAbhijeetSafai — Fri, 15 Mar 2024 05:51:57 GMT

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

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

ballardw — Fri, 15 Mar 2024 06:34:23 GMT

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.

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

Kurt_Bremser — Fri, 15 Mar 2024 07:39:32 GMT

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)?

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

FreelanceReinh — Fri, 15 Mar 2024 09:18:39 GMT

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.

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

Anna_DY — Fri, 15 Mar 2024 13:57:22 GMT

Thank you, Jade, It worked perfectly

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

Anna_DY — Fri, 15 Mar 2024 13:58:24 GMT

Thak you for your reply!

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

Anna_DY — Fri, 15 Mar 2024 14:01:53 GMT

Thank you for your advice!