SAS : Convert character to numeric without creatin

问题:

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 ?

回答1:

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;

回答2:

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;

回答3:

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_;