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

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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)

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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)

 

SaschaD
Obsidian | Level 7

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;
Tom
Super User Tom
Super User

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?

SaschaD
Obsidian | Level 7

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

Tom
Super User Tom
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 3821 views
  • 1 like
  • 2 in conversation