variable type change from Character to numeric

[Jonison]

Hello, I have a sas table (cannot be modified) , and one variable in this table whose values are all numbers are defined as 'character' and level is 'Nominal' (the value types in this variable is 0 or 1, and threshold of 'Nominal' and 'Level' is 2 (can not be 1). therefore this variable cannot be processed by any algorithm.

I tried use PROC SQL with INPUT to change the variable to numeric, but still in output, this variable still shows 'Nominal'.

I am working in SAS miner environment. 

Any helps will be highly appreciated.

[ballardw]

You actually contradict yourself here:" I have a sas table (cannot be modified)" and then attempt to change the data set.

Once a variable is created in a SAS data set it cannot have the type changed.

The typical approach is to create an entirely new data set as needed by renaming the old variable, create a new variable using an appropriate INPUT statement reading the character value into a numeric variable and dropping the character version.

data want; 
    set have (rename=(var=charvar));
    var = input(charvar,f8.);
    drop charvar;
run;

You did not specify any details of the variable name or range of values so the above is very generic.

Assign any Label or format to the new Var as needed. Be aware that this creates a new data set that after verifying things worked as needed you would use to replace your existing data set.

Better would be to determine why a value that should be numeric was created as character to begin with and change the process to avoid this in the future. One common source of this is reliance on Proc Import or wizards that use Proc Import without paying attention to results soon enough and possibly adjusting options. If your source data often uses something like NULL or NA in numeric fields to indicate missing values then the wizards often get the wrong result.

I would also suggest that you examine all of your actual character values to make sure that they have the correct length to contain the entire value as the processes that typically get the variable type incorrect often have issues with setting correct lengths.

[Jonison]

Many thanks for your reply.
I will provide more details.
The codes
PROC SQL;
CREATE table inventory as
Select * from DATASOURCE.

(one column 'FPIN' in 'inventory' is marked as 'character', but should be 'numeric')

I can understand the 'drop' function, should I use 'update' function to replace all values in table 'inventory' ( I would like the 'inventory' table with numeric 'FPIN')?

Many thanks

[ballardw · Accepted solution]

---

@Jonison wrote:
Many thanks for your reply.
I will provide more details.
The codes
PROC SQL;
CREATE table inventory as
Select * from DATASOURCE.

(one column 'FPIN' in 'inventory' is marked as 'character', but should be 'numeric')

I can understand the 'drop' function, should I use 'update' function to replace all values in table 'inventory' ( I would like the 'inventory' table with numeric 'FPIN')?

Many thanks

---

Your actual problem is still in the contents of Datasource but you can create your Inventory data set with something like

proc sql;
   create table inventory as
   select input(var,f8.) as var
          ,datasource.somevar
          ,datasource.othervar
          <repeat until you have selected all of the other variables
          in Datasource EXCEPT var>
   from datasource
   ;
quit;

List the variables I the order you want them.

Or if you don't mind having warning messages in your log you could try

proc sql;
   create table inventory as
   select input(var,f8.) as var, *
   from datasource
   ;
quit;

which will generate warnings about VAR is already  on data set Inventory (or some such.)

[Jonison]

Thanks.