i do not feel the previous solution i provided will work. this might be a better approach. Proc SQL;
Create view work.NCustomerID AS
SELECT COUNT(CustomerID) as NCustomerID, sales_channel, report_month
FROM
(
SELECT DISTINCT CustomerID, sales_channel, report_month FROM work.sales_details
) a
group by sales_channel, report_month
;
Create view work.NSalesRepID AS
SELECT COUNT(SalesRepID) as NSalesRepID, sales_channel, report_month
FROM
(
SELECT DISTINCT SalesRepID, sales_channel, report_month FROM work.sales_details
) a
group by sales_channel, report_month
;
Create view work.TotalCost AS
SELECT
a.report_month
, a.Sales_Channel
, Sum(Cost) AS TotalCost
from
work.sales_details a
group by sales_channel, report_month
;
Create table work.sales_sales1 AS
SELECT
a.report_month
, a.Sales_Channel
, TotalCost
, NCustomerID
, NSalesRepID
from
work.TotalCost a
LEFT JOIN work.NCustomerID b ON a.sales_channel = b.sales_channel and a.report_month = b.report_month
LEFT JOIN work.NSalesRepID c ON a.sales_channel = c.sales_channel and a.report_month = c.report_month
;
quit;
... View more