topic Re: Convert many character to numeric without changing order of variables in SAS Programming

Convert many character to numeric without changing order of variables

manonlyn — Wed, 29 May 2019 09:52:23 GMT

I have a dataset with 40 different columns that I need changing to numeric is there a way to change these without changing the order of the columns?

If I use the below the order of the columns change:

DATA WANT;
SET HAVE;
DATE_NUM = INPUT(DATE, 10.);
DROP DATE;
RENAME DATE_NUM = DATE;
RUN;

Re: Convert many character to numeric without changing order of variables

Patrick — Wed, 29 May 2019 10:16:39 GMT

@manonlyn

New columns get added at the end of a row.

Your code doesn't really change an existing column. It creates a new column, drops the old column, renames the new column to the name of the old column ...but it's still a new column.

What you want to do can be done but I can't think of a simple way to get there. Why is the internal order of the columns important to you? It actually shouldn't be of any relevance.

Looking into your code.

DATE_NUM = INPUT(DATE, 10.);

If the source is a date string then ideally use a Date informat to convert the string to an actual SAS Date value (=count of days since 1/1/1960). A SAS Date value then allows you to use all the nice SAS calendar functions like intnx() and intck().

Re: Convert many character to numeric without changing order of variables

manonlyn — Wed, 29 May 2019 10:26:22 GMT

It's important as they will be fed into another system and they need to be in a specific order.

I have looked for ways to change the column from character to numeric without creating a new column and I've not yet been able to find a way to do this. I've always used the method in my question.

Re: Convert many character to numeric without changing order of variables

Kurt_Bremser — Wed, 29 May 2019 11:07:11 GMT

If the order of the columns is important, use SQL:

data have;
input a $ b $ c $;
datalines;
10 20 30
;

proc sql;
create table want as
select
  input(a,10.) as a,
  input(b,10.) as b,
  input(c,10.) as c
from have;
quit;

You can create a list of variables from dictionary.columns, join that with a list of variables that need to be changed, and order it by the variable number (also contained in dictionary.columns).

Using that dataset, you can use call execute to create the SQL code dynamically:

data have;
input x $ a $ b $ c $;
datalines;
a 10 20 30
;

data vars_to_change;
input name :$32.;
datalines;
a
b
c
;

proc sql;
create table control as
select
  co.name,
  case
    when ch.name > " "
    then 'Y'
    else 'N'
  end as change
from dictionary.columns co
left join vars_to_change ch
on upcase(co.name) = upcase(ch.name)
where co.libname = 'WORK' and co.memname = 'HAVE'
order by co.varnum
;
quit;

data _null_;
set control end=eof;
if _n_ = 1 then call execute("
  proc sql;
  create table want as select
");
if _n_ > 1 then call execute(',');
if change = 'Y'
then call execute('input(' !! trim(name) !! ',10.) as ' !! trim(name));
else call execute(trim(name));
if eof then call execute(' from have;quit;');
run;

Re: Convert many character to numeric without changing order of variables

FreelanceReinh — Wed, 29 May 2019 11:24:40 GMT

If you have created a dataset or view with the converted variables (edit: and all the other variables from the initial dataset HAVE), let's call it TMP, you can apply a RETAIN statement to adjust the order of variables.

proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' & memname='HAVE'
order by varnum;
quit;

data want;
retain &varlist;
set tmp;
run;

Re: Convert many character to numeric without changing order of variables

Patrick — Wed, 29 May 2019 11:32:13 GMT

@manonlyn

I stay corrected by both @Kurt_Bremser and @FreelanceReinh . It's not that hard to code for.

I'd say the "best" way is using SQL as this only requires a single pass through the data. With a SQL you have to spell out all the columns. For lazy people like me one way to go if you've got a lot of columns is using the following code:

proc sql feedback inobs=0;
  select *
  from sashelp.class;
  ;
quit;

Then copy from the SAS log the expanded SQL into Notepad ++ and use the plugin "Poor Man's T_SQL Formatter" and you'll end up with code like below which you then copy back into a SAS editor.

SELECT CLASS.NAME
  ,CLASS.Sex
  ,CLASS.Age
  ,CLASS.Height
  ,CLASS.Weight
FROM SASHELP.CLASS;

Now add the changes to the columns and you're done.

Re: Convert many character to numeric without changing order of variables

Oligolas — Wed, 29 May 2019 12:45:46 GMT

data have;
   length x1-x40 $10;
   array x {40} x1-x40;
   do u = 1 to 100;
      do i=1 to 40;
      z=ranuni(0);
      if z<0.995 then x{i} = byte(int(rand("uniform",48,58)));
      else x{i} = byte(int(rand("uniform",65,66)));
      end;
      output;
   end;
   drop u z i;
run;

data _null_;
   set sashelp.vcolumn end=last;
   where libname eq 'WORK' and memname eq 'HAVE';
   if _N_ eq 1 then call execute('DATA want;set have;');
   call execute(cats(name,'_num')||'=input('||strip(name)||',?? best32.); drop '||strip(name)||';');
   if last then call execute('RUN;');
run;