Proc SQL: insert into... select... trim char?

[JGA1]

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

[Kurt_Bremser · Accepted solution]

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;

[JGA1]

Thank you! That works perfectly!

[Tom]

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;

[ballardw]

---

@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