topic Re: Changing a column from character to numeric in SAS Programming

Changing a column from character to numeric

MicheleB0108 — Mon, 18 Nov 2019 16:20:43 GMT

I am trying to change a column (MarkDown1) from a character type to numeric type. In a previous post, I asked how to change NA to 0, which I've done successfully. I didn't know if there's a way to change the type that the column is as well. I'm trying to use the numbers in the field for PROC MEANS and then use that to create a line graph. I'm using University Edition Basic 3.8. I attached a picture to show what I'm talking about in case I'm explaining it wrong. Little to no code experience. Thank you!

Re: Changing a column from character to numeric

PeterClemmensen — Mon, 18 Nov 2019 16:23:30 GMT

See this very small example

data test;
    markdown1='439.80';
    num_markdown1=input(markdown1, best8.);
run;

Re: Changing a column from character to numeric

MicheleB0108 — Mon, 18 Nov 2019 16:48:39 GMT

How do I do that on a large scale for every value in the field?

Re: Changing a column from character to numeric

Kurt_Bremser — Mon, 18 Nov 2019 17:03:35 GMT

Start with a simple step that does one variable:

data want;
set have;
num_markdown1 = input(markdown1, best8.);
run;

Now, you want to create a dynamic version of that.

We make a dataset containing variable names:

data varnames;
input name $32.;
datalines;
markdown1
;

Then, we create the first step, using our name list:

data _null_;
set varnames end=done;
if _n_ = 1 then call execute('data want; set have;'):
call execute(cats('num_',name,' = input(',name,', best8.);'));
if done then call execute('run;');
run;

You can create the varnames dataset from dictionary.columns (sashelp.vcolumn), if you can find a suitable where condition to extract the wanted names automatically; otherwise, just expand the step with the datalines.

Re: Changing a column from character to numeric

ballardw — Mon, 18 Nov 2019 21:39:55 GMT

@MicheleB0108 wrote:

I am trying to change a column (MarkDown1) from a character type to numeric type. In a previous post, I asked how to change NA to 0, which I've done successfully. I didn't know if there's a way to change the type that the column is as well. I'm trying to use the numbers in the field for PROC MEANS and then use that to create a line graph. I'm using University Edition Basic 3.8. I attached a picture to show what I'm talking about in case I'm explaining it wrong. Little to no code experience. Thank you!

It sounds like you used proc import to read the data.

You can avoid this particular issue by not using proc import and read data in a data step as needed in the first place. A custom INFORMAT can be a very powerful tool for reading such data.

proc format library = work;
invalue myna (upcase)
'NA' = 0
other = [f16];
run;

data example;
   input x myna.;
datalines;
1
0
na
234.567
NA
0.000045
;

The (UPCASE) option will convert the text encountered to upper case before comparing the value, which may be of use if you have manual data entry and sometimes get "na" "Na" or "nA" actually entered.

Or you could such an informat in creating a new variable:

data want;
   set have;
   numericmarkdown = input(markdown,myna.);
run;

use

Re: Changing a column from character to numeric

MicheleB0108 — Mon, 18 Nov 2019 19:01:22 GMT

Thank you so much! One last question, how do you save your current code in the University Edition?

Re: Changing a column from character to numeric

Kurt_Bremser — Mon, 18 Nov 2019 19:07:25 GMT

Use the Save icon atop the program editor. Save to your folder, so that the code is stored outside the virtual machine.