Help using Base SAS procedures

convert char to num for all _CHARACTER_

Reply
Contributor
Posts: 73

convert char to num for all _CHARACTER_

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

Respected Advisor
Posts: 3,799

Re: convert char to num for all _CHARACTER_

Posted in reply to smilingmelbourne

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;

Respected Advisor
Posts: 3,156

Re: convert char to num for all _CHARACTER_

Posted in reply to data_null__

Bookmarked!

PROC Star
Posts: 7,492

Re: convert char to num for all _CHARACTER_

Posted in reply to smilingmelbourne

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_Smiley Happy;

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

  &makenum;

run;

Super Contributor
Posts: 1,636

Re: convert char to num for all _CHARACTER_

Posted in reply to smilingmelbourne

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

PROC Star
Posts: 7,492

Re: convert char to num for all _CHARACTER_

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.

Super Contributor
Posts: 1,636

Re: convert char to num for all _CHARACTER_

Hi Art,

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

Ask a Question
Discussion stats
  • 6 replies
  • 916 views
  • 1 like
  • 5 in conversation