How to convert char data type to numeric data type in sas

2
votes

I have a table whose description is as shown in the log is:

create table WORK.EMP( bufsize=65536 )
(

    Series_Name char(21) format=$21. informat=$21.,
    Series_Code char(17) format=$17. informat=$17.,
    Country_Name char(19) format=$19. informat=$19.,
    Country_Code char(3) format=$3. informat=$3.,
    _2000__YR2000_ char(12) format=$12. informat=$12.,
    _2001__YR2001_ char(12) format=$12. informat=$12.,
    _2002__YR2002_ char(12) format=$12. informat=$12.,
    _2003__YR2003_ char(12) format=$12. informat=$12.,
    _2004__YR2004_ char(11) format=$11. informat=$11.,
    _2005__YR2005_ char(11) format=$11. informat=$11.,
    _2006__YR2006_ char(11) format=$11. informat=$11.,
    _2007__YR2007_ char(12) format=$12. informat=$12.,
    _2008__YR2008_ char(12) format=$12. informat=$12.,
    _2009__YR2009_ char(12) format=$12. informat=$12.,
    _2010__YR2010_ char(12) format=$12. informat=$12.,
    _2011__YR2011_ char(12) format=$12. informat=$12.,
    _2012__YR2012_ char(12) format=$12. informat=$12.,
    Total12 num label='Total12'
   );

Now I want to sum the columns from year 2000 to 2012 and put the result into the column named 'Total12', but first i think should convert the 'char' data types into 'int' to do the calculations. I have tried the following code in SAS:

proc sql;    
alter table emp modify _2000__YR2000_ num;    
alter table emp modify _2001__YR2001_ num;    
run;

But it gives me following error:

ERROR: You cannot alter '_2000__YR2000_' to be a numeric column.

What should i do now?

3
mysqlsas
Is this tagged mysql because this is coming from a mysql database table that SAS is connecting to? If so, you have options. – Joe
Actually i am new in sas and i have read in the tutorials that we can use sql in sas using 'proc sql' but this is not coming from mysql database. – Krishan Avtar Singh

3 Answers

1
votes

One way to do it would be do use an input statement in a data step to create a new variable / column named something different, drop the original, then rename the new variable to match the original.

data work.want(rename=(_2000_YR2000_b=_2000_YR2000_));
   set work.emp;
  _2000__YR2000_b=input(_2000__YR2000_,8.);
  drop _2000_YR2000_;
run;

And so on.

1
votes

This should work for you:

data have;
_2000__YR2000_ = "1";
_2001__YR2001_ = "2";
_2002__YR2002_ = "3";
_2003__YR2003_ = "4";
_2004__YR2004_ = "5";
_2005__YR2005_ = "6";
_2006__YR2006_ = "7";
_2007__YR2007_ = "8";
_2008__YR2008_ = "9";
_2009__YR2009_ = "10";
_2010__YR2010_ = "11";
_2011__YR2011_ = "12";
_2012__YR2012_ = "13";
run;

data want;
set have;
array years[*]
_2000__YR2000_ 
_2000__YR2000_
_2001__YR2001_ 
_2002__YR2002_
_2003__YR2003_ 
_2004__YR2004_ 
_2005__YR2005_
_2006__YR2006_
_2007__YR2007_ 
_2008__YR2008_
_2009__YR2009_ 
_2010__YR2010_
_2011__YR2011_ 
_2012__YR2012_ 
;
total = sum( of years[*]);
run;
1
votes

A macro is not necessary if the variable names are in good shape.

data test;
    set have;
    array year20{*} _20:;
    call missing(year);
    do i = 1 to dim(year20);
        year = sum(year, input(year20{i}, ?? best.));
    end;
run;