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, Dec 29 '14 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, Dec 29 '14 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, Dec 29 '14 at 15:24

score 10 · Accepted Answer · answered Dec 29 '14 at 15:30

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;

score 4 · Answer 2 · answered Dec 29 '14 at 14:14

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 '14 at 14:14

in_user

1,948
1
15
22

Yeah :) ..old habit dies hard. – in_user Dec 29 '14 at 15:42
1

Thanks for your help, it's appreciated ! – Vincent Dec 29 '14 at 15:43

score 2 · Answer 3 · answered Sep 26 '18 at 12:14

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 '18 at 12:14

Allan Bowe

12,306
19
75
124

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` ? – Vincent Sep 27 '18 at 13:24
1

`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.. – Allan Bowe Sep 27 '18 at 14:16

SAS : Convert character to numeric without creating another variable

3 Answers3

Linked