Quick way to change the data type of registered table in SAS DI 4.9

[David_Billa]

I want to change the data type of one of the variable from character to numeric in one of the table in SAS DI 4.9. While I creating the new table, I had mistakenly created one variable with character data type instead of numeric.

So when I tried to change the datatype by going to respective table via inventory and changed the data type and did right click and choose 'Update Metadata' but still I see the datatype has not changed.

I ensured that I've closed the table before doing this operation. Am I doing something wrong here or is there any other way to change data type in a step or two?

[Patrick]

"So when I tried to change the datatype by going to respective table via inventory and changed the data type..."

Here you changed the table metadata but not the underlying physical table. 

"...and did right click and choose 'Update Metadata' "

This updates the table metadata using the underlying physical table - which you haven't changed so now metadata for this column will be character again.

Easiest way: Change table metadata, delete the physical table, run the job which creates the physical table.

If that's not possible: Use Proc SQL/Alter Table; Modify... and change the physical table. Then execute an update metadata

[David_Billa]

From your reply 'Easiest Way' is not possible. Document says

'You cannot use the MODIFY clause to

• change a character column to numeric or vice versa. To change a column's data type, drop the column and then add it (and its data) again, or use the DATA step.'

Is there a way that I can change the datatype, without dropping the variable?

[Patrick]

'Easiest Way' is to delete the physical table, change your metadata definition, run the DI job that creates the table. 

You are right, you can't change the data type with an alter table. Are you dealing with a SAS table or a database table?

[David_Billa]

It's a SAS table.

[Patrick · Accepted solution]

OK - if you really can't re-create the table then something as painful as below should do the job. Once you've modified the physical table you then can execute an update metadata and re-align the column throughout your jobs (eventually use the standardization wizard if there are too many impacted mappings).

data have;
  var='123';
  output;
  stop;
run;

proc datasets lib=work nolist;
  modify have;
    rename var=_var;
    run;
quit;

proc sql;
  alter table have
    add var float;
  update have
    set var=input(_var,best32.);
  alter table have
    drop _var;
quit;

proc print data=have;
run;
proc contents data=have;
run;