Hey all,
I'm having a SAS programming metal block and could use some help.
I am trying to create a dataset(s) that mimics an excel report that has the following structure:
Raw Data output with string variables like Retailer, Product, Brand, and Category and numeric values like forecast and actuals
A Pivot (#1) table sourcing this raw data taking an ABS value of a difference of two measures (example: actuals - forecast), but it only includes Brand and Category (so it is taking the difference the summation to only that level)
A second pivot (#2) table that contains the sums forecast, actuals, and other Numerics. But the difference between this pivot table is that it contains all variables
A Results tab that contains the output of these pivots via sumifs and contains slicers that are linked to both pivot tables. So that when, for instance, a retailer is selected it takes to total sum of forecast from pivot table #2, but maybe it will take the ABS difference from pivot #1
The issue I'm having with converting into SAS is when using those slicers. Due to pivot #1's live link to the raw data, when retailer is chosen it knows what amounts to sum up even though the variable/column doesn't exist in that pivot.
So I am having trouble summing up at the brand and category level, but still being able to filter-down on other variables.
What I've tried so far:
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 B.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;
^ This didn't work since we have brands with multiple categories, so the output was incorrect when selecting a brand filter with multiple categories. Worked fine for for all others though.
I also tried to just make a mapping table where I could recreate the pivot tables in theory and then just map over pivot #1 so it would contain variables like RETAILER and PRODUCT. This didn't work because there's no one-to-one matching. A retailer may have many brands. and a brand may have many retailers.
Any thoughts?
... View more
