Convert successive columns into numeric

[Ann297]

Hi everone,

Please let me know to convert multiple and successive columns (with type of char and num) into numeric? The variables name are 's09912_1--s16015_1' which including 1900 columns.

I tried ARRAY statement, but it can't be use with both character and numeric type.

And INPUT statement, can only convert one columns a time, and selecting character columns is too time-consuming.  

Simple code better. Thanks in advanced!

[ballardw]

That many variables of the wrong type means how ever you read the data was likely sub-optimal.

It is very likely that reading the data correctly in the first place would be preferred.

Please describe how you created the data set with the wrong types.

You also cannot 'convert' an existing variable, one the type is set it stays. There are ways that involve renaming the the variables and creating a new variable of the correct type with the old name. However that typically doesn't keep column order if that is important.

Note that for arrays it is very easy  to get all the character variables:

array x (*) _character_;

Your problem is actually how to get a matching number of new numeric variables.

Also, what do these character values that should be numeric actually look like? Best would be to copy the first 5 rows of data from the source file and paste them into a text box opened on the forum with the </> icon that appears above the message window.

IF your file stared as a spreadsheet you may be much better off saving the file to a CSV format and read that file as spreadsheets are full of potential landmines when it comes to data consistency.

[Ann297]

Thank you for you patience! The type of the file i got is 'sas7dbat'. This dataset was convert from character to numeric, so maybe that's reason. And i don't know how they converted this dataset. Anyway, i 'll try to ask for the original dataset first. 

[Kurt_Bremser]

Go back to the process that brought the data into SAS, and fix it there.

Which information do the numbers in the variable names represent? Is this some kind of date-related stuff?

[Astounding]

You can create an array holding only the variables that need to be converted.  This suggestion comes close but may include extra variables:

array charvals {*} _character_;

That array might also include character variables from other parts of your data set, character variables that you may not want to convert.  A more accurate array statement would be:

array chars {*} s09912_1-character-s16015_1;

Note that you still need new variable names to hold the numeric result of your conversion.  An existing variable cannot be changed from character to numeric.  So there is more work to be done to accurately define those new names.  It would help if you show us how you plan to convert a single variable from character to numeric.

[Tom · Accepted solution]

You can use a variable list to find all of the character variables between those two variables easy enough.  But to convert then you need to also know how many there are and what their names are.  One easy way to find out is to use PROC TRANSPOSE.

proc transpose data=have(obs=0) out=names;
  var s09912_1-character-s16015_1 ;
run;

Once you have the names it is not hard to generate code to convert the values to numeric.

filename code temp;
data _null_;
  set names ;
  file code ;
  length nliteral $60;
  nliteral=nliteral(_name_);
  put '_' _n_ '=input(' nliteral ',32.);'
    / 'drop ' nliteral ';'
    / 'rename _' _n_ '=' nliteral ';' 
  ;
run;

data want;
  set have;
  %include code / source2 ;
run;

But I think the real solution is to define the variables properly to begin with.  How did you create the dataset that has the wrong variable type for so many variables?  Did you perhaps use PROC IMPORT to make guesses about how to read a text file?  If so then just write your own data step to read the text file instead and you will have complete control over how the variables are created.

[Ann297]

Thank you! From original is a way, but this code worked ! And totally like magic!