Can I use Case when expression with a single condition and no new variable

[VarunD]

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;

[Amir]

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]

Thanks for your help. I appreciate it. It sound like it should work buy I wanted to find out if case when expression can be used in this case.

[PaigeMiller]

---

@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

[VarunD]

Code runs but the missing values are still missing.

I get a warning that "Variable Car_Model already exists on file WORK.Car_History" 

[PaigeMiller]

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...

[VarunD]

But this will create a new variable car_model1, isn't it ?

[PaigeMiller · Accepted solution]

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,

[VarunD]

Thanks a lot. That was the problem. Great catch.

[Amir]

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.

[Reeza]

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;

---

[aaronh]

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;

[VarunD]

Very Interesting ! Thanks a lot for your help.