topic Re: change a column type when selecting * in proc sql in New SAS User

change a column type when selecting * in proc sql

Jens89 — Thu, 19 Dec 2019 14:10:12 GMT

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

Re: change a column type when selecting * in proc sql

Tom — Thu, 19 Dec 2019 14:26:14 GMT

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
;

Re: change a column type when selecting * in proc sql

Jens89 — Thu, 19 Dec 2019 14:39:03 GMT

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