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;
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)
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)
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;
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?
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
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.
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.