Changing a column from character to numeric

[MicheleB0108]

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!

[PeterClemmensen]

See this very small example

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

[MicheleB0108]

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

[Kurt_Bremser]

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.

[MicheleB0108]

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

[Kurt_Bremser]

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

[ballardw]

---

@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