topic Re: WARNING: Character expression will be truncated when assigned to character column in New SAS User

WARNING: Character expression will be truncated when assigned to character column

SaschaD — Mon, 12 Aug 2019 13:11:32 GMT

Hi SAS-Community,

I got always the WARNING - Character expression will be truncated when assigned to character column and I don't know why.

I tried it with PUT, INPUT... but always a Problem. Could you help me please.

PROC SQL;
	CREATE TABLE GRV_Comeback_Dummy AS
	SELECT V.VTGONR
	FROM APPG.KOPIE_V_APP_GRV V
	WHERE V.VTGONR in (SELECT T.Gruppenvertrag FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Abgangsdatum not is null) AND V.STOV = '000000';
QUIT;

PROC SQL;
	ALTER TABLE GRV_Comeback_Dummy ADD Historie_OLD CHAR (100);
	ALTER TABLE GRV_Comeback_Dummy ADD Historie_NEW CHAR (100);
QUIT; 

PROC SQL;
	UPDATE GRV_Comeback_Dummy D
	SET 
	'Historie_OLD'n = (SELECT COMPRESS(T.Historie) FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR),	
	'Historie_NEW'n = 
		'E:' || PUT((SELECT T.'erstes Erscheinungsdatum'n FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR), MMYYN4.) 
		||
		'A:' || PUT((SELECT T.Abgangsdatum FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR), MMYYN4.)
	WHERE D.VTGONR in (SELECT T.Gruppenvertrag FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR);
QUIT;

PROC SQL;
	UPDATE APPG.TEST_AZL_GRV_Bestand T
	SET 'Historie'n = 
		(SELECT COMPRESS(D.Historie_OLD) FROM GRV_Comeback_Dummy D WHERE D.VTGONR = T.Gruppenvertrag)
		||
		(SELECT COMPRESS(D.Historie_NEW) FROM GRV_Comeback_Dummy D WHERE D.VTGONR = T.Gruppenvertrag)
	WHERE T.Gruppenvertrag in (SELECT D.VTGONR FROM GRV_Comeback_Dummy D WHERE T.Gruppenvertrag = D.VTGONR);
QUIT;

Re: WARNING: Character expression will be truncated when assigned to character column

Tom — Mon, 12 Aug 2019 13:17:23 GMT

If you want to limit the length of a string value then use SUBSTR() or perhaps better SUBSTRN(). Since you create length $100 variables probably something like:

Historie_OLD = substrn(.......,1,100)

Re: WARNING: Character expression will be truncated when assigned to character column

SaschaD — Mon, 12 Aug 2019 13:37:48 GMT

Thanks Tom, I changed my Code (see below), but now I get the error Concatenation (||) requires character operands.

PROC SQL;
	UPDATE GRV_Comeback_Dummy D
	SET 
	'Historie_OLD'n = (SELECT COMPRESS(T.Historie) FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR),	
	'Historie_NEW'n = 
		'E:' || SUBSTRN((SELECT T.'erstes Erscheinungsdatum'n FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR), 1, 100) 
		||
		'A:' || SUBSTRN((SELECT T.Abgangsdatum FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR), 1, 100)
	WHERE D.VTGONR in (SELECT T.Gruppenvertrag FROM APPG.TEST_AZL_GRV_Bestand T WHERE T.Gruppenvertrag = D.VTGONR);
QUIT;

Re: WARNING: Character expression will be truncated when assigned to character column

Tom — Mon, 12 Aug 2019 13:58:49 GMT

Put the concatenation where it belongs.

(SELECT SUBSTRN('E:' ||  T.'erstes Erscheinungsdatum'n,1,100)  ...

Why are you using such convoluted code? Create a table. Change the table. Update the table.

Why not just build the table directly from joining the sources?

Re: WARNING: Character expression will be truncated when assigned to character column

SaschaD — Mon, 12 Aug 2019 14:13:25 GMT

Same error.

I try to describe my case.

I compare tables (V-new data and T-current data) and I look for records which are not appears in the V anymore.

I have for each record the Information when the record come the first time (erstes Erscheinungsdatum) and when the data was found the last time (Abgangsdatum).

If I found a record which is not in the V I want to save the first time and last time information in a separate column (Historie). How can I do that directly?

Text for the Historie fild E:firstdate Format MMYY and A:lastdate Format MMYY.

Thanks,

Sascha

Re: WARNING: Character expression will be truncated when assigned to character column

Tom — Mon, 12 Aug 2019 14:15:54 GMT

So is the type issue that you are trying to concat a DATE value? Convert it to a string first. For example using PUT() function.