topic Re: Can I use Case when expression with a single condition and no new variable in New SAS User

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

VarunD — Fri, 24 May 2019 14:16:17 GMT

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;

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

Amir — Fri, 24 May 2019 14:24:44 GMT

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.

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

PaigeMiller — Fri, 24 May 2019 14:25:19 GMT

@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

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

VarunD — Fri, 24 May 2019 15:13:53 GMT

Code runs but the missing values are still missing.

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

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

VarunD — Fri, 24 May 2019 15:15:45 GMT

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.

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

PaigeMiller — Fri, 24 May 2019 15:21:09 GMT

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-data-AKA-generate/ta-p/258712

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

Reeza — Fri, 24 May 2019 15:20:12 GMT

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;

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

aaronh — Fri, 24 May 2019 15:21:17 GMT

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;

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

VarunD — Fri, 24 May 2019 15:37:03 GMT

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

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

PaigeMiller — Fri, 24 May 2019 15:39:39 GMT

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,

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

Amir — Fri, 24 May 2019 15:42:39 GMT

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.

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

VarunD — Fri, 24 May 2019 15:57:42 GMT

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

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

VarunD — Fri, 24 May 2019 15:59:09 GMT

Very Interesting ! Thanks a lot for your help.