BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Anna_DY
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

 

View solution in original post

8 REPLIES 8
Mazi
Pyrite | Level 9

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?

 

 

 

 

DrAbhijeetSafai
Pyrite | Level 9

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

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real
ballardw
Super User

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.

Kurt_Bremser
Super User

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
Jade | Level 19

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 621 views
  • 0 likes
  • 6 in conversation