Converting character variables to numeric

[RobertHuey]

Hi all,

Is there a way for me to convert a dataset of 14 character variables and one numeric variable to all numeric variables without having to use input statements?  I would also like to keep the names of the variables too.  Is there a way to do this in an array?

Thanks in advance!

[RW9]

Not easily no.  You could write the data out to a temp file and then read it back in (note of course, that non-numeric will throw errors!):

data have;
  var1="123"; var2="456"; var3="678"; var4=678; output;
run;

data _null_;
  file "s:\temp\rob\temp.csv" dlm=",";
  set have;
  put var1 var2 var3 var4;
run;

data want;
  infile "s:\temp\rob\temp.csv" dlm=",";
  input var1 var2 var3 var4;
run;

If you combine this with sashelp.vcolumn in a macro or generated code you could do it for limitless unknown columns.

/* Write out file */

data _null_;

  set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;

  if _n_=1 then call execute('data _null_; file "s:\temp\rob\temp.csv" dlm=","; set work.have; put ');

  call execute(' '||strip(name));

  if last then call execute(';run;');

run;

data _null_;

/* Read in file */

  set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE")) end=last;

  if _n_=1 then call execute('data want; infile "s:\temp\rob\temp.csv" dlm=","; input ');

  call execute(' '||strip(name));

  if last then call execute(';run;');

run;

[art297]

It would be cleaner using an input statement rather than +0, but the following should work:

data have;

  input (var1-var7) ($) var8 (var9-var14) ($);

  cards;

1 2 3 4 5 6 7 8 9 0 1 2 3 4

6 5 4 3 2 1 . 1 2 3 4 5 6 7

;

proc sql noprint;

  select catt(name,'=_',name),

         catt(name,'=_',name,'+0;')

    into :renames separated by ' ',

         :convert separated by ' '

      from dictionary.columns

        where libname='WORK' and

        memname='HAVE'

  ;

quit;

data want (drop=_:);

  set have (rename=(&renames.));

  &convert.

run;

[RobertHuey]

Thank you for your response!  I have a question about my memname.  I tried using a macro variable with an ampersand and it couldn't read it.

It looks as follows... 

memname='monthreport_&month'

Is there a way to deal with this?

Also, when I do memname='monthreport_april', it converts my character variable, month.  Is there a way for me to keep that variable as a character variable?

Thanks again!

[art297]

In using dictionary.columns the values for both libname and memname MUST be in upper case. Additionally, macro variables won't resolve unless they are enclosed with DOUBLE quotes.

[RobertHuey]

Thanks again Arthur.  In doing so, it has a note that says that no rows were selected in the log.  Should that be a concern?....Actually it turns out that it didn't select my data.  None of the variables were converted when using double quotes and the macro variable.  Also, is there a way to keep one of my variables that is already stored as a character variable as a character variable?  Sorry for the confusion!

[art297]

You would have to post more info, such as the actual file names and the code you ran (including how you set the macro variable &month. Since proc sql didn't select any rows, it wasn't able to obtain the necessary variable names. Should be easy to fix, but not without knowing what you actually have and tried.

[RamKumar]

Hi Aruthur,

Your code sounds good. Could you please tell me the role of macro 'convert' in your code?

[art297]

&renames and &convert aren't macros but, rather, macro variables used to add text to the program.

&renames simply adds an underscore to the beginning of each variable name.

&convert computes numeric variables with the original names using the form:

  var1=_var1+0;

I would have preferred to use the input function in creating that variable, but the OP didn't want to use input.