BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sudhan
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

@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
PROC Star
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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 25230 views
  • 2 likes
  • 4 in conversation