topic Re: convert char to num for all _CHARACTER_ in SAS Procedures

convert char to num for all _CHARACTER_

smilingmelbourne — Tue, 20 Mar 2012 14:28:31 GMT

Hi,

I have a series of data sets of equal size, each one of which has over 100 columns containing numbers. Due to data downloads into Excel, each data set often differs from the others in the data type of the column. For example, column 2 of data set 1 is numeric but column 2 of data set 2 is character. This poses a problem to me because later on I will need to stack up all these data sets vertically.

I wrote the following code to convert any columns which are of character types to numeric, so that all data sets will have numeric that will be joined vertically. I don't know why the code doesn't work. Errors occur, and if not, then character columns remain of character types and no conversion at all.

*Convert any column of char types to numeric;

data sheet1_1;

set sheet1 (drop=ISIN);

array vars{*}$ _CHARACTER_; *** work on all those columns in the data set that are of character type and ignore those which already are numeric;

do i=1 to dim(vars) by 1;

vars{i}=input(vars{i},BEST12.); *** convert from char to num;

end;

run;

Can you please help? Thanks a lot

Re: convert char to num for all _CHARACTER_

data_null__ — Tue, 20 Mar 2012 14:50:56 GMT

You have to create NEW variables. One way is to use transpose and do a FLIP/FLOP.

data test;
   id + 1;
   input (x1-x10)(:f8. :$f8.);
   cards;
1 2 3 4 5 6 7 8 9 10
3 49 98 17 87 18 18 18 77 77
;;;;
   run;
proc sql noprint;
   select name into :nums separated by ' '
      from dictionary.columns
      where libname eq 'WORK' and memname eq 'TEST' 
         and upcase(name) ne 'ID'
         and upcase(type) eqt 'N';
   quit;
   run;
%put NOTE: NUMS=&nums;
 
proc transpose data=test out=chars;
   by id;
   var _char_;
   copy &nums;
   run;
data chars;
   set chars;
   num = input(col1,f16.);
   run;
proc transpose data=chars out=allnums(where=(not missing(_name_)));
   by id; 
   var num;
   copy &nums;
   run;
proc print;
   run;

Re: convert char to num for all _CHARACTER_

Haikuo — Tue, 20 Mar 2012 15:02:40 GMT

Bookmarked!

Re: convert char to num for all _CHARACTER_

art297 — Tue, 20 Mar 2012 15:20:01 GMT

Here is another approach. Use the proc sql call to create the renames and new calculations. e.g.:

data sheet1;

input isin $ a b $ c d $ e f $;

cards;

x 1 2 3 4 5 6

;

proc sql noprint;

select trim(name)||"=input(n_"||trim(name)||",BEST12.);",

trim(name)||"=n_"||trim(name)

into :makenum separated by " ",

:renames separated by " "

from dictionary.columns

where libname="WORK" and

memname="SHEET1" and

type="char" and

name ne "isin"

;

quit;

data sheet1_1 (drop=n_:);

set sheet1 (drop=ISIN rename=(&renames.));

&makenum;

run;

Re: convert char to num for all _CHARACTER_

Linlin — Tue, 20 Mar 2012 15:29:32 GMT

Hi Art,

I have included the numeric variables in the final dataset:

data have;

aa=66;

bb=88;

cc=99;

input (a b c d e)($) ;

cards;

1 2 3 4 5

;

proc sql noprint;

select catx(' ','input(',name,',best12.) as',name) into : list1 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ='char';

select name into : list2 separated by ','

from dictionary.columns

where libname='WORK' and memname='HAVE' and type ne 'char';

create table want as

select &list1,&list2 from have;

quit;

proc print data=want;run;

proc contents data=want;run;

Linlin

Message was edited by: Linlin

Re: convert char to num for all _CHARACTER_

art297 — Tue, 20 Mar 2012 15:40:24 GMT

Linlin: Since the OP indicated having both numeric and character variables in the file you would have to expand on that to account for the numeric variables.

Re: convert char to num for all _CHARACTER_

Linlin — Tue, 20 Mar 2012 15:46:38 GMT

Hi Art,

Thank you. I just focused on “convert char to num for all _CHARACTER_”.