convert char to num for all _CHARACTER_

[smilingmelbourne]

Hi,

I have a series of data sets of equal size, each one of which has over 100 columns containing numbers. Due to data downloads into Excel, each data set often differs from the others in the data type of the column. For example, column 2 of data set 1 is numeric but column 2 of data set 2 is character. This poses a problem to me because later on I will need to stack up all these data sets vertically.

I wrote the following code to convert any columns which are of character types to numeric, so that all data sets will have numeric that will be joined vertically. I don't know why the code doesn't work. Errors occur, and if not, then character columns remain of character types and no conversion at all.

*Convert any column of char types to numeric;

data sheet1_1;

    set sheet1 (drop=ISIN);

    array vars{*}$ _CHARACTER_; *** work on all those columns in the data set that are of character type and ignore those which already are numeric;

        do i=1 to dim(vars) by 1;

        vars{i}=input(vars{i},BEST12.); *** convert from char to num;

    end;

run;

Can you please help? Thanks a lot

[data_null__]

You have to create NEW variables.  One way is to use transpose and do a FLIP/FLOP.

data test;
   id + 1;
   input (x1-x10)(:f8. :$f8.);
   cards;
1 2 3 4 5 6 7 8 9 10
3 49 98 17 87 18 18 18 77 77
;;;;
   run;
proc sql noprint;
   select name into :nums separated by ' '
      from dictionary.columns
      where libname eq 'WORK' and memname eq 'TEST' 
         and upcase(name) ne 'ID'
         and upcase(type) eqt 'N';
   quit;
   run;
%put NOTE: NUMS=&nums;
proc transpose data=test out=chars;
   by id;
   var _char_;
   copy &nums;
   run;
data chars;
   set chars;
   num = input(col1,f16.);
   run;
proc transpose data=chars out=allnums(where=(not missing(_name_)));
   by id; 
   var num;
   copy &nums;
   run;
proc print;
   run;

[Haikuo]

Bookmarked!

[art297]

Here is another approach.  Use the proc sql call to create the renames and new calculations.  e.g.:

data sheet1;

  input isin $ a b $ c d $ e f $;

  cards;

x 1 2 3 4 5 6

;

proc sql noprint;

  select trim(name)||"=input(n_"||trim(name)||",BEST12.);",

         trim(name)||"=n_"||trim(name)

    into :makenum separated by " ",

         :renames separated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="SHEET1" and

              type="char" and

              name ne "isin"

  ;

quit;

data sheet1_1 (drop=n_:);

  set sheet1 (drop=ISIN rename=(&renames.));

  &makenum;

run;

[Linlin]

Hi Art,

I have included the numeric variables in the final dataset:

data have;

  aa=66;

  bb=88;

  cc=99;

  input (a b c d e)($) ;

  cards;

  1 2 3 4 5

  ;

  proc sql noprint;

select catx(' ','input(',name,',best12.) as',name) into : list1 separated by ','

  from dictionary.columns

   where libname='WORK' and memname='HAVE' and type ='char';

select name into : list2 separated by ','

  from dictionary.columns

   where libname='WORK' and memname='HAVE' and type ne 'char';

create table want as

  select &list1,&list2 from have;

quit;

proc print data=want;run;

proc contents data=want;run;

Linlin

Message was edited by: Linlin

[art297]

Linlin: Since the OP indicated having both numeric and character variables in the file you would have to expand on that to account for the numeric variables.

[Linlin]

Hi Art,

Thank you. I just focused on “convert char to num for all _CHARACTER_”.