topic Re: Proc SQL: insert into... select... trim char? in New SAS User

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

JGA1 — Thu, 11 Jul 2019 08:26:55 GMT

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

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

Kurt_Bremser — Thu, 11 Jul 2019 08:37:14 GMT

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;

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

JGA1 — Thu, 11 Jul 2019 09:07:58 GMT

Thank you! That works perfectly!

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

ballardw — Thu, 11 Jul 2019 17:09:17 GMT

@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

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

Tom — Thu, 11 Jul 2019 17:17:55 GMT

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;