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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

2 REPLIES 2
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 2 replies
  • 5323 views
  • 0 likes
  • 2 in conversation