DATA Step, Macro, Functions and more

Replace the missing value by using coalesce function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Replace the missing value by using coalesce function

Hi,
 
Proc SQL;
Select *,Coalesce(MSRP,80000) AS MSRP ,Coalesce(Length,500) AS Length
From Cars;
Quit;
 
There is no changes happens in the missing values of MSRP and Length. Just added new entries as MSRP and Length again in which replacements happened.
Anyone help me how to replace the missing value without adding new entries .
 
Please find the  output of above query.

 

The SAS System


Origin       Make MSRP    Length   MSRP  Length
Australia  Audi      .              .          80000  500
India       Benz   $80,000   400       80000  400
UK          BMW  $20,000     .          20000  500

 

Thank you


Accepted Solutions
Solution
a week ago
Respected Advisor
Posts: 4,644

Re: Replace the missing value by using coalesce function

When you say select * you select all existing variables, including the original version of your variables. To get only the new version of your variables, you can't use the * shortcut. So, you can either explicitly list all the variables in a select statement or use an update statement such as:

 

Proc SQL;
create table cars as select * from sashelp.cars;
update cars 
set msrp = Coalesce(MSRP,80000), length = Coalesce(Length,500);
Quit;
PG

View solution in original post


All Replies
Solution
a week ago
Respected Advisor
Posts: 4,644

Re: Replace the missing value by using coalesce function

When you say select * you select all existing variables, including the original version of your variables. To get only the new version of your variables, you can't use the * shortcut. So, you can either explicitly list all the variables in a select statement or use an update statement such as:

 

Proc SQL;
create table cars as select * from sashelp.cars;
update cars 
set msrp = Coalesce(MSRP,80000), length = Coalesce(Length,500);
Quit;
PG
Occasional Contributor
Posts: 8

Re: Replace the missing value by using coalesce function

Thank you.

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 102 views
  • 0 likes
  • 2 in conversation