topic Re: Converting Character columns to numbers in SAS Programming

Converting Character columns to numbers

ubshams — Mon, 16 Sep 2019 01:28:04 GMT

My dataset came from Excel and some columns which should be numbers got converted as characters in SAS.

Apart from one column I want to convert all other character columns into number format. How do I do that?

Lets say variables "x", "y", "z","b", and "a" came in as character variables (Type= Char and format = $7.). Only variable x is okay to be a charcter. How do I keep variable x as is and conert y, z, and b to Type = Num and format = BEST. in a simple way?

Was trying to do something like this, but not working:

data new;

set old;

if variable not = 'x'

and format = '$7.'

then format best.;

run;

Thanks!

Re: Converting Character columns to numbers

singhsahab — Mon, 16 Sep 2019 01:54:41 GMT

Hi,

While converting variables from character to numeric make sure all the available data under character variable should be number.

data have;
input x $  y $ z $;
datalines;
9 1 2
;
run;
data want;
	set have; 
	array all_n[*] y z ;
	array t(2)  _y _z;
	do i = 1 to dim(all_n); 
		t[i] = input(all_n[i], best.);  
	end;
run; 
proc contents data = want; 
run;

Re: Converting Character columns to numbers

KachiM — Mon, 16 Sep 2019 03:21:28 GMT

Edit:

We may do it without using array. Borrowing from @singhsahab , we do it as:


data have(rename=(_y = y _z=z));
input x $  y $ z $;
_y = input(y, best12.); drop y;
_z = input(z, best12.); drop z;
datalines;
9 1 2
;
run;

Re: Converting Character columns to numbers

ballardw — Mon, 16 Sep 2019 18:10:00 GMT

@ubshams wrote:

My dataset came from Excel and some columns which should be numbers got converted as characters in SAS.

Apart from one column I want to convert all other character columns into number format. How do I do that?

Lets say variables "x", "y", "z","b", and "a" came in as character variables (Type= Char and format = $7.). Only variable x is okay to be a charcter. How do I keep variable x as is and conert y, z, and b to Type = Num and format = BEST. in a simple way?

Was trying to do something like this, but not working:

data new;

set old;

if variable not = 'x'

and format = '$7.'

then format best.;

run;

Thanks!

This sounds like using Proc Import to bring in the data. Have you verified that all of your actual character variables are correct? Import does this when reading spreadsheets because it only uses a default of the first 20 rows to determine variable type and length. If there are more than one row of headers or blank values in the column you will end up with character values. Or if the values sometimes have non-numeric characters.

Better is to learn to control reading the data sets to begin with so you don't have to spend time fixing this. Especially if you will be reading multiple sheets with the same structure. You might get different columns in the next file or sheet to be of the wrong type or length.

Either save the sheets as CSV files and write a proper data step to read them (Hint: Proc import with the guessingrows=max option will generate a basic data step to read the file that you can copy from the log to the editor, clean up and reuse by changing infile and data set).

Or use Libname Excel and read the data more directly.

Re: Converting Character columns to numbers

ubshams — Mon, 16 Sep 2019 20:30:52 GMT

Thanks!

I have a bunch of ##Div0 in excel in the first 30-40 rows across multiple columns across multiple spreadsheets so the input() option is quicker this time.

But I have noted the guessingrows=max option for future use with csv files.