Hi,
I am wondering if it is possible to use case when expression in Proc SQL to replace missing values of a variable based on a single condition without creating a new variable.
I know that I can work around this by creating a new variable and then dropping it later if needed but I wanted to find out if it is possible without creating a new variable.
For e.g. If I have to replace missing values of Car_Model variable to Unspecified from Car_History dataset, can I do something like this ?
Proc Sql;
create table New_Cars as
select distinct *,
case when Car_Model is missing then 'Unspecified'
end
from Car_History;
run;
Yes, it will.
If you don't want that, then you have to replace
select distinct *,
with a list of variables that does not include car_model (where I assume that you have variables named VARIABLE1, VARIABLE2, etc.) and the car_model is specifically left out of this list.
select distinct variable1,variable2,
Does the following help:
/* set all names beginning with 'J' to missing */
data have;
set sashelp.class;
if name =: 'J' then
call missing(name);
run;
/* update missing values */
proc sql;
update
have
set
name = 'N/A'
where
missing(name)
;
quit;
Amir.
@VarunD wrote:
For e.g. If I have to replace missing values of Car_Model variable to Unspecified from Car_History dataset, can I do something like this ?
Proc Sql;
create table New_Cars as
select distinct *,
case when Car_Model is missing then 'Unspecified'
end
from Car_History;
run;
Looks like it should run, but maybe you want this:
case when missing(Car_Model) then 'Unspecified' else car_model end as car_model
Code runs but the missing values are still missing.
I get a warning that "Variable Car_Model already exists on file WORK.Car_History"
Try this:
case when missing(Car_Model) then 'Unspecified' else car_model end as car_model1
If that doesn't fix the problem, then you need to provide a portion of your actual data set following these instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...
Yes, it will.
If you don't want that, then you have to replace
select distinct *,
with a list of variables that does not include car_model (where I assume that you have variables named VARIABLE1, VARIABLE2, etc.) and the car_model is specifically left out of this list.
select distinct variable1,variable2,
I think the reason you're getting the 'already exists' message is because you are selecting all columns to start with using '*'.
Instead, try listing the columns individually and in place of 'Car_Model' use the logic suggested by others so it won't try to create a duplicate.
Amir.
You can, but maybe you want the COALESCE() function instead? It goes through a list of variables and picks the first non-missing, but you can also include a string.
select distinct *, coalesce(car_model, 'Unspecified') as Car_Model
from car_history;
@VarunD wrote:
Hi,
I am wondering if it is possible to use case when expression in Proc SQL to replace missing values of a variable based on a single condition without creating a new variable.
I know that I can work around this by creating a new variable and then dropping it later if needed but I wanted to find out if it is possible without creating a new variable.
For e.g. If I have to replace missing values of Car_Model variable to Unspecified from Car_History dataset, can I do something like this ?
Proc Sql;
create table New_Cars as
select distinct *,
case when Car_Model is missing then 'Unspecified'
end
from Car_History;
run;
Hi,
If all you want to do here is to update the "Car_model" variable by changing missing values to 'Unspecified', you could consider using a format, such as:
**Make a format**;
proc format;
value $model_miss (default = 50)
' ' = 'Unspecified'
;
run;
**Apply the format to car_model and save as a new dataset**;
data car_history2;
set car_history;
car_model = put(car_model, $model_miss.);
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.