I am trying to move data from one table to another using proc SQL. A minimal example is given below:
proc sql;
create table table1
(field varchar(20) not null);
create table table2
(field varchar(10) not null);
insert into table1
values ('abc');
quit;
proc sql;
insert into table2 (field)
select strip(field)
from table1;
quit;
Obviously this produces a warning message that the string of length 20 from table1 will be truncated when inserted into table2, where the respective column contains strings of length 10.
In order to remove this warning I tried to remove trailing blanks or consider only the first half of the string - but none of this seems to work:
proc sql;
insert into table2 (field)
select strip(field)
from table1;
quit;
proc sql;
insert into table2 (field)
select substr(field, 1, 10)
from table1;
quit;
proc sql;
insert into table2 (field)
select trim(field)
from table1;
quit;
Does anybody know why trimming does not work in this Situation and whether there is a Workaround?
Best regards,
Jonathan
Create a sub-select where you explicitly set the length:
proc sql;
insert into table2 (field)
select * from
(select substr(field, 1, 10) length=10
from table1);
quit;
Create a sub-select where you explicitly set the length:
proc sql;
insert into table2 (field)
select * from
(select substr(field, 1, 10) length=10
from table1);
quit;
Note you can just let SAS truncate the value without needing to use the SUBSTR() function. Even when the value is longer than the targeted length.
data table1; length field $20; field='abc4567890abc'; run;
data table2; length field $10; stop; run;
proc sql;
insert into table2 (field)
select * from (select field length=10 from table1)
;
quit;
@JGA1 wrote:
I am trying to move data from one table to another using proc SQL. A minimal example is given below:
proc sql; create table table1 (field varchar(20) not null); create table table2 (field varchar(10) not null); insert into table1 values ('abc'); quit; proc sql; insert into table2 (field) select strip(field) from table1; quit;
Obviously this produces a warning message that the string of length 20 from table1 will be truncated when inserted into table2, where the respective column contains strings of length 10.
In order to remove this warning I tried to remove trailing blanks or consider only the first half of the string - but none of this seems to work:
proc sql; insert into table2 (field) select strip(field) from table1; quit; proc sql; insert into table2 (field) select substr(field, 1, 10) from table1; quit; proc sql; insert into table2 (field) select trim(field) from table1; quit;
Does anybody know why trimming does not work in this Situation and whether there is a Workaround?
Best regards,
Jonathan
The "why" is that the error message relates to the defined lengths of the variables, not the content. Trim does not change the length of the variable storage space it would just change (possibly) the charters used for some operation. You can get the truncation waringin without any actual value at all:
9 data example; 10 length x $ 15; 11 run; NOTE: Variable x is uninitialized. NOTE: The data set USER.EXAMPLE has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.00 seconds 12 data example2; 13 length x $10; 14 set example; 15 run; WARNING: Multiple lengths were specified for the variable x by input data set(s). This can cause truncation of data. NOTE: There were 1 observations read from the data set USER.EXAMPLE. NOTE: The data set USER.EXAMPLE2 has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.