macro to convert character to numeric

[mmehrd]

I have a dataset with a 1000 variables that are in character format. The values could be numbers or text. Does anyone know a macro that detect each field one by one and see if it’s a number converts it to numeric, if not leave it as character?

[PaigeMiller]

If possible, you need to examine the process that creates this data set and fix the problem there. If this is a conversion of an Excel file to a SAS data set, you probably can fix the problem by converting the Excel file again with the proper options, or by converting Excel to CSV, in which case then you can force certain columns to be numeric.

[Tom]

SAS has a tool for guessing what format text is using.  PROC IMPORT.

Convert your data to a CSV file and then use PROC IMPORT to read it.  Now compare the types between the two versions of the data and decide which one you want to use.

Just make sure not to use a tool for generating the CSV file that adds quotes around all character values (whether or not quotes are needed) as PROC IMPORT will assume that means the values should be read a character.

[Patrick]

Below a way how you could implement what @Tom suggests.

data have(drop=_:);
  array var {10} $10;
  do _k=1 to 100;
    do _i=1 to dim(var);
      var[_i]=put(_n_,z9.);
      if ceil(ranuni(1)*1000)>990 then var[_i]=cats('A',var[_i]);
    end;
    output;
  end;
run;

filename extfile temp;
proc export 
  data=have
  file=extfile
  dbms=csv
  replace
  ;
run; 

proc import 
  out=want
  file=extfile
  dbms=csv
  replace
  ;
  guessingrows=max;
run;
filename extfile clear;

title 'Variable Types Before and After';
proc sql;
  select libname, memname, name, type
  from dictionary.columns
  where 
    libname='WORK' and
    memname in ('HAVE','WANT')
  order by memname, varnum
  ;
quit;
title;