topic Re: macro to convert character to numeric in SAS Programming

macro to convert character to numeric

mmehrd — Sun, 26 Jul 2020 15:07:10 GMT

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?

Re: macro to convert character to numeric

PaigeMiller — Sun, 26 Jul 2020 15:14:24 GMT

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.

Re: macro to convert character to numeric

Tom — Sun, 26 Jul 2020 16:26:49 GMT

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.

Re: macro to convert character to numeric

Patrick — Mon, 27 Jul 2020 04:42:09 GMT

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;