Hello! Since few days ago I have a problem with a SQL procedure. Basically I have two identical SQL procedures, which differ only in one dimension - period. The SQL procedures below create two tables: test_A (period 201601-201712) and test_B (period 201712). There is an error produced only for the big table (test_A): ERROR: Invalid value for width specified - width out of range When I remove the NOSIG dimension, the error disappears. Thus I suspect it comes from the NOSIG column, which is a concatenation of two other columns (e.g. it creates B002 out of "B" and 2 or B182 out of "B" and 182). But I do not understand why it only concerns the bigger table. The ENTITE is always one letter (e.g. "B", "W") and t1.NOSIG is always a number from 1 to 400. Why would the error only show up in the bigger table? Could it be a problem of format which I did not specify for the created NOSIG column? I tried to Google a bit on this error but found very little info, hence it is hard to guess where it really comes from. Anyway, after filtering on period=201712 from both tables, the data are not the same. Only the test_B (the smaller table) contains correct and full data. Many thanks in advance for any ideas! PROC SQL /* long period */ ;
CREATE TABLE ISP_lib.test_A (compress=yes) AS
SELECT
CATX("", t1.ENTITE, put(t1.NOSIG, z3.)) AS NOSIG,
CATX(".", CATX("", t1.SERVICE, t1.NOTAB), t1.SSTAB) AS TAB,
t1.PERIODE,
t1.COLONNE,
case /* VALEUR en EUR needs conversion but not VOLUME */
when t1.COLONNE in ("VOLU", "VOTI", "VOCL") then t1.valeur /* volume=valeur */
when t1.COLONNE in ("VALE", "FLOA") then (t1.valeur/t1.taux) /* valeur eur= valeur / taux */
end as val,
t1.RUB as Dim1,
t1.PAYS as Dim2,
t1.DEVISE as Dim3,
t1.SECTEUR as Dim4,
scan(t1.LIGNE, 5, "-") as Dim5,
scan(t1.LIGNE, 6, "-") as Dim6,
scan(t1.LIGNE, 7, "-") as Dim7
FROM OLMBSPRD.V_DATA_ENTITE t1 /* name of the database */
WHERE
t1.LIVRE = 'O'
AND t1.SERVICE = 'V'
AND t1.ETAT = 'En production'
AND t1.NOTAB = "1.5"
AND t1.PERIODE between 201601 and 201712
;
QUIT;
PROC SQL /* short period */;
CREATE TABLE ISP_lib.test_B (compress=yes) AS
SELECT
CATX("", t1.ENTITE, put(t1.NOSIG, z3.)) AS NOSIG,
CATX(".", CATX("", t1.SERVICE, t1.NOTAB), t1.SSTAB) AS TAB,
t1.PERIODE,
t1.COLONNE,
case /* VALEUR en EUR needs conversion but not VOLUME */
when t1.COLONNE in ("VOLU", "VOTI", "VOCL") then t1.valeur /* volume=valeur */
when t1.COLONNE in ("VALE", "FLOA") then (t1.valeur/t1.taux) /* valeur eur= valeur / taux */
end as val,
t1.RUB as Dim1,
t1.PAYS as Dim2,
t1.DEVISE as Dim3,
t1.SECTEUR as Dim4,
scan(t1.LIGNE, 5, "-") as Dim5,
scan(t1.LIGNE, 6, "-") as Dim6,
scan(t1.LIGNE, 7, "-") as Dim7
FROM OLMBSPRD.V_DATA_ENTITE t1 /* name of the database */
WHERE
t1.LIVRE = 'O'
AND t1.SERVICE = 'V'
AND t1.ETAT = 'En production'
AND t1.NOTAB = "1.5"
AND t1.PERIODE = 201712
;
QUIT;
... View more