SAS : Convert character to numeric without creating another variable

Question

I want to convert x to numeric.

DATA test;
  input x $1.;
  cards;
  1
  2
  0
  ;
run;

I tried different ways :

With *1 :

/* trial1 */
DATA test1;
  SET test;
  x = x*1;
run;

The log prints the following note :

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      2470:3
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      2470:4

And the format doesn't change.

With input() :

/* trial2 */
DATA test2;
  SET test;
  x = input(x,BEST1.);
run;`

The log prints the following note :

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
  2396:3

And the format doesn't change.

With informat :

/* trial3 */
DATA test3;
  SET test;
  informat x BEST1.;
run;

The log prints the following error :

ERROR 48-59: The informat $BEST was not found or could not be loaded.

Which is explained here and here : the compiler detects different types for variable and format, assumes it's a mistake, add the presumed-missing $ and therefore doesn't find the format.

All these trials would work if I created a second variable like for example :

DATA test4;
  SET test (rename=(x=x2));
  x = x2*1;
  drop x2;
run;

But I'm trying to clean up my code and I wonder if there is a way to do such a conversion without doing so ?

A variable cannot have its type defined as character and numeric in the same data step. If x only contains numeric values then define it as a numeric when you first use it. — Amir
– Amir, Commented Dec 29, 2014 at 14:03
I agree the database could have been built in a smarter way. But all is left to me is to use it the best I can, I can't write on it. I was hoping for an equivalent in SAS to R's : x<-as.numeric(x) — Vincent
– Vincent, Commented Dec 29, 2014 at 14:24
R is doing exactly the same thing as SAS (in NEOmen's solution or yours above - both have some minor flaws but the concept is the same). It's not changing the type of x, it is creating an entirely new x that replaces the old x. SAS just is a little more formal with how you have to do that (which is much safer). — Joe
– Joe, Commented Dec 29, 2014 at 15:24

Joe · Accepted Answer · 2014-12-29 15:30:48Z

10

As noted elsewhere, you do need to use a second variable. SAS won't let you alter the variable type of a column directly, but you can cheat things by using rename in a similar way as above.

The one thing I'm going to suggest different from NEOmen's answer or yours above is using input. Length/assignment or using the *1 method are both fine, but they rely on SAS's automatic type conversion, which will add a note to your log that it's doing it. You should avoid things like that in your log, as they are messy and make others think you may have done it on accident.

Using NEOmen's test dataset:

data test1;
  set test(rename=x=x_old);
  x=input(x_old,best12.); *whatever is appropriate informat for your variable;
run;

answered Dec 29, 2014 at 15:30

Joe

63.5k7 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

in_user · Accepted Answer · 2014-12-29 14:14:43Z

4

Once a variable is defined numeric or character, you cannot change it's data type, you could probably use the below workaround though.

DATA test;
input x $1.;
cards;
1
2
0
;
run;

data test1(drop=x_old);
length x 8.;
set test(rename = (x=x_old));
x=x_old;
run;

answered Dec 29, 2014 at 14:14

in_user

1,9581 gold badge16 silver badges24 bronze badges

2 Comments

in_user Over a year ago

Yeah :) ..old habit dies hard.

Vincent Over a year ago

Thanks for your help, it's appreciated !

Allan Bowe · Accepted Answer · 2018-09-26 12:14:48Z

2

The problem with the previous solutions is that they will not retain indexes.

The following solution is preferable if the intention is to update the target 'in place' (although this will still cause issues if the target column is itself within an index):

%let changeds=test;
%let changevar=x;

DATA &changeds;
input &changevar $1.;
cards;
1
2
0
;
run;

proc datasets lib=work noprint;
  modify &changeds;
    rename &changevar=_willerrorifthisvarexists_;
run;

proc sql;
alter table &changeds add &changevar num;
update &changeds set &changevar=input(_willerrorifthisvarexists_,best.);
alter table &changeds drop  _willerrorifthisvarexists_;

answered Sep 26, 2018 at 12:14

Allan Bowe

12.8k20 gold badges83 silver badges126 bronze badges

2 Comments

Vincent Over a year ago

Thanks for your answer ! However I don't quite get why this solution is different from the others. Does alter table x give a different output than DATA x;SET x ?

Allan Bowe Over a year ago

data x; set x; will REPLACE the table. That means it will destroy any primary keys / indexes / constraints (unless you rebuild them). The above solution will modify the existing table whilst retaining those constraints. Note that Neoman and Joe were both careful to create new tables in each of their suggested solutions..

Collectives™ on Stack Overflow

SAS : Convert character to numeric without creating another variable

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related