topic Replace the missing value by using coalesce function in SAS Programming

Replace the missing value by using coalesce function

Divya_Joseph — Wed, 09 Aug 2017 03:37:57 GMT

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

Re: Replace the missing value by using coalesce function

PGStats — Wed, 09 Aug 2017 03:52:59 GMT

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;

Re: Replace the missing value by using coalesce function

Divya_Joseph — Wed, 09 Aug 2017 04:31:21 GMT

Thank you.