Replace the missing value by using coalesce function

[Divya_Joseph]

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

[PGStats · Accepted solution]

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;

[Divya_Joseph]

Thank you.