Hi,
when calculating the ABS_VALUE you can not call it "B.ABS_VALUE"
ABS(SUM(ActualValues) - SUM(FORECAST)) AS B.ABS_VALUE
should be
ABS(SUM(ActualValues) - SUM(FORECAST)) AS ABS_VALUE
PROC SQL;
CREATE TABLE PIVOT AS
SELECT A.RETAILER,
A.PRODUCT,
A.CATEGORY,
A.BRAND,
A.DATE,
SUM(A.ActualValues) AS Actuals,
SUM(A.Forecast) AS Forecast,
B.ABS_VALUE
FROM WORK.DATA AS A
LEFT JOIN
(SELECT RETAILER,
PRODUCT,
CATEGORY,
BRAND, DATE, ABS(SUM(ActualValues) - SUM(FORECAST)) AS ABS_VALUE
FROM WORK.DATA
GROUP BY 1,
2,
3,
4,
5) AS B ON A.RETAILER = B.RETAILER
AND A.PRODUCT = B.PRODUCT
AND A.CATEGORY = B.CATEGORY
AND A.BRAND = B.BRAND
AND A.DATE = B.DATE
GROUP BY 1,
2,
3,
4,
5,
8
;
QUIT;
... View more