Dynamic code to convert char to numeric

[Ronein]

Hello

I want to convert char columns into numeric.

I want to do it for all char columns in the data set .

The number of the char columns can be different so I would like to have dynamic code that do it for each number of columns.

Please note that all the char columns have only digits so the conversion must work.

I also want to keep the original column names.

The code below work 100% but I want to find code that will work for any number of columns to be converted.

 Data have;
 input mon1 $ mon2 $ mon3 $ mon4 $ mon5 $ mon6 $;
 cards;
 2301 2302 2303 2303 2305 2306
;
 Run;
DATA want;
SET have;
ARRAY old(6) mon1 mon2 mon3 mon4 mon5 mon6;
ARRAY new(6) b1 b2 b3 b4 b5 b6;
DO i=1  to dim(old);
new{i} = input(old{i},best.);
END;
drop i mon1 mon2 mon3 mon4 mon5 mon6;
rename b1=mon1 b2=mon2 b3=mon3 b4=mon4 b5=mon5 b6=mon6;
RUN; 

[WarrenKuhfeld]

https://blogs.sas.com/content/sastraining/2017/06/27/how-to-perform-an-operation-on-all-numeric-or-a...

[Reeza]

How are the columns to be converted specified?

You could could be simplified.

DATA want;
SET have;
ARRAY old(6) mon1 mon2 mon3 mon4 mon5 mon6;
ARRAY new(6) b1-b6;
DO i=1 to dim(old);
new{i} = input(old{i},best.);
END;
drop i mon1-mon6;
rename b1-b6=mon1-mon6;
RUN;

[Reeza]

Also, please try searching.

https://communities.sas.com/t5/SAS-Programming/Convert-many-character-to-numeric-without-changing-or...

[Tom · Accepted solution]

To make you dynamic you will need to use code generation.

First get the list of variables you want to convert.

Make up some temporary names for the extra variables you will need to make.  Perhaps just using the variable order number as some strange prefix that is unlikely to be used for a real variable name.

proc sql;
create table charvars as
select varnum
     , nliteral(name) as charname
     , cats('_XX_',varnum) as numname
from dictionary.columns
where libname="WORK"
  and memname="HAVE"
  and type='char'
order by 1
;
quit;

Now you can use that information to generate code.

If the list number of variables is reasonable you can just generate the code into macro variables (macro variables can hold up to 64K bytes).

proc sql noprint;
select charname
     , numname
     , catx('=',numname,charname)
  into :charlist separated by ' '
     , :numlist separated by ' '
     , :renames separated by ' '
  from charvars
  order by varnum
;
quit;

Now you can use those macro variable to help generate a data step.

data want ;
  set have;
  array _char_ &charlist ;
  array _num_ &numlist ;
  do _n_=1 to dim(_char_);
    _num_[_n_] = input(left(_char_[_n_]),32.);
  end;
  drop &charlist;
  rename &renames;
run;

NOTE: The INPUT() function does not care if the width on the informat is larger then the length of the character string being read so just use the maximum 32 width that the numeric informat permits. 

NOTE:  BEST is the name of a FORMAT, although if you accidentally use it as the name of an INFORMAT SAS will just treat it as an alias for the normal numeric informat.

[RichardDeVen]

If your original data already has some numeric columns you probably want to retain the original column order in your new data set.

This version of the conversion approach uses code generation that explicitly codes the INPUT transform for each character variable (no array in the DATA Step).  This can have a minor performance improvement because all the 'array' computations are done at step compilation time instead of at step runtime, the downside being that the convert codegen can exceed 64k bytes if the original data set has several thousand character columns.

Also, if your character column names are very long (28-32 long) some renaming schemes for the worker variable involving the original column name could exceed 32 char and cause compile time error.

This example retains original column order and uses a shorter working variable name (__<varnum>)

data have;
  array x[10] (1:10);
  array s[10] $20 ('10', '9', '8.8', '777', '666', '55', '4', '3.14159', '2','1.000001') ;
run;

proc contents noprint data=have out=have_meta;
run;

proc sql noprint;
  select 
    case when type=2 then cats(name,'=__',varnum) else '' end,
    case when type=2 then cats(name,'= input(__',varnum,',32.);') else '' end,
    case when type=2 then cats('__',varnum) else '' end,
    name
  into 
    :rename separated by ' ',
    :convert separated by ';',
    :drop separated by ' ',
    :all_columns separated by ' ' 
  from have_meta 
  order by varnum
  ;
quit;

options symbolgen;
data want(drop=&drop);
  retain &all_columns;
  set have (rename=(&rename));
  &convert;
run;