change a column type when selecting * in proc sql

[Jens89]

I'm trying to select all the columns from a table and one of those columns I need to change to a numeric type. When I try the following it doesn't work:

proc sql;
create table grand_index as
select
*
,input(MGI_ID, 11.) as MGI_ID format numeric11.
from
LIP.MOSAIC_GRAND_INDEX ; 
quit;

I'm getting the following warning: WARNING: Variable MGI_ID already exists on file WORK.GRAND_INDEX.

I could simply do the following but then I'm creating a new column which I'm trying to avoid. I would like to get it done in one go

proc sql;
create table grand_index as
	select
		*
		,input(MGI_ID, 11.) as numeric_var format numeric11.
	from
		LIP.MOSAIC_GRAND_INDEX ; 
quit;

thanks

[Tom · Accepted solution]

You can use dataset options to help.   So create the new variable using a new name and use the drop= the old variable.

create table want(drop=height) as
  select *,put(height,7.3) as height_char
  from sashelp.class
;

If you want the new variable to use the original name then add a RENAME= option.

create table want(drop=height rename=(height_char=height)) as
  select *,put(height,7.3) as height_char
  from sashelp.class
;

[Jens89]

thanks, the second option is exactly what I was looking for.