BookmarkSubscribeRSS Feed
manonlyn
Obsidian | Level 7

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;

 

6 REPLIES 6
Patrick
Opal | Level 21

@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(). 

manonlyn
Obsidian | Level 7

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. 

Patrick
Opal | Level 21

@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.

Kurt_Bremser
Super User

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; 
FreelanceReinh
Jade | Level 19

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;

 

Oligolas
Barite | Level 11
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;
________________________

- Cheers -

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 3391 views
  • 7 likes
  • 5 in conversation