CODEWITHSUNDEEP
sascharacternumeric
User981636
User981636

Reputation: 3621

SAS Overwrite numeric variable to character

I have a numeric variable (e.g. cylinders) and I want to overwrite it to a character (and keep it in the same order in the table).

I would like to do it in one step. Is there any smart way to do it? 

data cars; set sashelp.cars; 
if cylinders >= 6 then cylinders = 'Big'; 
if cylinders < 6 then cylinders 'Ecocar'; 
run;

I am just trying to avoid the tedious steps of creating a new character variable and then having to reposition that new character variable to where cylinders was in the table.

Upvotes: 1

Views: 671

Answers (4)

delete
delete

Reputation: 75

You can use rename and retain:

data cars(drop=temp);
retain Make Model Type Origin DriveTrain MSRP Invoice EngineSize cylinders Horsepower 
       MPG_City MPG_Highway Weight Wheelbase Length;
set sashelp.cars(rename=(cylinders=temp));
if temp >= 6 then cylinders = 'Big'; 
if temp < 6 then cylinders = 'Ecocar'; 
run;

You can also use proc sql:

proc sql;
create table cars as 
select Make, Model, Type, Origin, DriveTrain, MSRP, Invoice, EngineSize,
        case when cylinders >= 6 then 'Big'
             when cylinders < 6 then 'Ecocar' end as cylinders,
        Horsepower, MPG_City, MPG_Highway, Weight, Wheelbase, Length
from sashelp.cars
;
quit;

Upvotes: 1

Tom
Tom

Reputation: 51566

You can query metadata to find the order of the variables and use that to define the new variable at the right place.

But let's assume that if the order of the variables is that important to you then you already know what the name of the first variable is.

Include just the variables up to CYLINDERS by using a position based variable list. Then define your new variable. Then re-read the observation again with ALL of the variables so that the variables after CYLINDERS appear after the new variable.

Now you add code to populate the new variable. Drop the old one. And rename the new one to have the old name.

So if the first variable is named MAKE your code looks like:

data want ;
  set sashelp.cars(keep=MAKE -- CYLINDERS);
  length new_var $10 ;
  set sashelp.cars;

  if cylinders >= 6 then new_var = 'Big'; 
  else if not missing(cylinders) then new_var = 'Ecocar'; 

  drop CYLINDERS;
  rename new_var=CYLINDERS;
run;

Upvotes: 2

H_J
H_J

Reputation: 486

Try using Proc format

Proc format;
      Value CylSize
           0 - 6 = ‘Ecocar’
           7 - high = ‘Big’;
Run;

Data cars;
   Set sashelp.cars;
    Format Cylinders CylSize.;
Run;

Upvotes: 5

Stu Sztukowski
Stu Sztukowski

Reputation: 12899

Once a variable is defined, you cannot change it from one type to another without creating a new variable. You will need to define where it needs to be in the table before making it. The quick way would be using a retain statement before your set statement to define the order of pre-existing variables without worrying what type they are.

data cars; 
    retain make model type origin drivetrain _cylinders;
    length cylinders $15.;

    set sashelp.cars(rename=(cylinders = _cylinders)); 

    if _cylinders >= 6 then cylinders = 'Big'; 
    if _cylinders < 6 then cylinders = 'Ecocar'; 

    drop _cylinders;
run;

Another way is by first creating a dummy table that looks exactly the way you want it and has all of the variable attributes that you need. Populate it only with one single empty row so it's easy to rearrange later. Let's call it cars_attribs. For example:

Type    C    C     C    C      C          C         N    N       N          ...
Column  make model type origin drivetrain cylinders msrp invoice enginesize ...
Row                                                 .    .       .          ...

Use this table in a set statement with obs=0 to always hold your favorite variable positions.

   data cars; 
    set cars_attribs(obs=0)
        sashelp.cars(rename=(cylinders = _cylinders));
    ;

    if _cylinders >= 6 then cylinders = 'Big'; 
    if _cylinders < 6 then cylinders = 'Ecocar'; 

    drop _cylinders;
run;

If you ever want to change variable positions, simply re-arrange cars_attribs in Enterprise Guide by dragging columns.

Upvotes: 2

Related Questions