/*-----
 * group: Data management
 * purpose: Ensure specified columns are numeric.  Convert character data to numeric values when possible.
 * notes: Useful for preparing supposedly similar data for stacking or merging. Code also posted on <a href="stackoverflow.com">stackoverflow.com</a>
 */

 %macro enforce_num(data, vars, out=&data);

  /*
   * Author:
   *   Richard A. DeVenezia, 1/29/2020
   *
   * Arguments:
   *   data - name of input data set
   *   vars - space separated list of variables that must be numeric, convert type if necessary
   *   out  - name of output data set, default same as input data set
   *
   * Output:
   *   - Unchanged data set if data and out are the same and no conversion needed
   *   - Changed data set if some columns in data need conversion to numeric
   *     - replaces data if out is same as data
   *     - replaces out if out is different then data
   *     - the column order of the changed data set will be the same as the original data set
   */

  %local dsid index index2 vars varname vartype varnames debug;

  %let index2 = 0;  %* number of variables determined to be requiring conversion;
  %let debug = 0;

  %if &debug %then %put NOTE: &SYSMACRONAME: data=%superq(data);

  %let dsid = %sysfunc(open(&data));
  %if &dsid %then %do;
    %do index = 1 %to %sysfunc(attrn(&dsid, nvars));
      %let varname = %sysfunc(varname(&dsid, &index));

      %let varnames = &varnames &varname;

      %if %sysfunc(indexw(&varname, &vars)) %then %do;
        %if C = %sysfunc(vartype(&dsid, &index)) %then %do;
          %* Data contains character variable requiring enforcement;
          %let index2 = %eval(&index2+1);
          %local convert&index2;
          %let convert&index2 = &varname;

          %let varnames = &varnames ___&index2 ;   %* Variables that will be converted will be named __<#> during conversion;
        %end;
      %end;
    %end;
    %let dsid = %sysfunc(close(&dsid));
  %end;
  %else
    %put %sysfunc(sysmsg());

  %*put NOTE: &=vars;
  %*put NOTE: &=varnames;

  %if &index2 = 0 %then %do;
    %* No columns need to be converted to numeric, copy to out if necessary;
    %if &data ne &out %then %do;
      data &out;
        set &data;
      run;
    %end;
    %return;
  %end;

  %* Some columns need to be converted to numeric;
  %* Ensure the converted column is at the same position (varnum) as in the original data set;

  data &out;
    retain &varnames;

    set &data;

    %do index = 1 %to &index2;
      ___&index = input(&&convert&index,?? best12.);
    %end;

    drop
      %do index = 1 %to &index2;
        &&convert&index
      %end;
    ;

    rename
      %do index = 1 %to &index2;
        ___&index = &&convert&index
      %end;
    ;
  run;

  %put NOTE: ------------------------------------------------;
  %put NOTE: &data has been subjected to numeric enforcement.;
  %put NOTE: ------------------------------------------------;
%mend enforce_num;