Modify Column data types and format on a table based on another table

[Sudhan]

Hi,

I am quite new to SAS, pardon me if my question is naive.

I want to change my target table definition (TARGET) based on my Source table (SOURCE) column data type 

As part of my programming first I create a table TARGET with the default data type and column length as char(30).

"AS-IS" Table definition for TARGET:

| Name  | Type | Length

| ID                 | Char | 30

| Name           | Char | 30

"AS-IS" Table definition for SOURCE:

| Name  | Type | Length

| ID                  | Char      | 30

| Name            | Numeric | 8

TARGET table is a flat/empty table, where I need to update the values from the Source table. So first I need to convert the data type of the TARGET table based on the SOURCE table.

"To-Be" Table definition for TARGET:

| Name  | Type | Length

| ID                  | Char      | 30

| Name            | Numeric | 8

Following is the code I have written

proc sql;

Create table Test( name char(14)) ;

quit;



proc sql;

select cat(type,'(',length,')'),format into :Dtype,:format from dictionary.columns where libname = 'WORK' and memname = 'SOURCE' and upcase(name)='VALUE' ;

quit;



proc sql;

alter table WORK.Test modify name &Dtype. format=&format.;

quit;

I have an error message: 

ERROR: You cannot alter 'name' to be a numeric column.
ERROR: Character column name requires a character format specification.

[RW9 · Accepted solution]

The error quite clearly tells you the problem, you cannot change the type of a column.  How can name be both character and numeric?  If it contains non-numeric information, then it is not a numeric field.  The general rule would be, create the structure, then add the data into the given structure, so an example:

proc sql;
  create table target (id char(30),name char(30));
quit;

data target (drop=_name);
  set target
        /* Here I move the data to a new column so as not to confuse the 
           compiler with a numeric and character field of the same name */
        source (rename=(name=_name));
  /* Here I take the numeric data and put it into text in the character field */
  name=strip(put(_name,best.));
run;

What this does is create a temporary variable _name which is dropped at the end, which contains the numeric data, I put() this into the character variable.  No alteration of the base table is done.  You can of course do it the other way and convert character to numeric - however be aware that will fail if there is non-numeric data:

proc sql;
  create table target (id char(30),name num);
quit;

data target (drop=_name);
  set target
        source (rename=(name=_name));
  name=input(_name,best.);
run;

[Patrick]

@Sudhan 

Changing a column type from Character to Numeric is not possible because type Numeric wouldn't be able to store data as alphanumeric strings. The SQL doesn't "care" that your table is empty.

Why do you first create the target table structure if you actually don't want to use it? That's not necessary when using SAS as SAS will in most cases just (re-) create the target table and it will use the column definitions from source to do so.

[Astounding]

Since you will need a data step anyway, why use SQL at all? Why not:

data want;
set have;
newvar = put ( name, 30. );
drop name;
rename newvar = name;
run;

The new version is right-hand justified, although you could easily add to the program to change that.