summary data set by groups SQL

[Dublin187]

Good afternoon,

This is probably a very simple question but I cannot find how to get the final table that I need.

Proc SQL;
Create table work.sales_sales1 AS 
SELECT report_month, Sales_Channel,
Count(distinct CustomerID) AS CountCustomers, 
Count(distinct SalesRepID) AS CountSalesRep,
Sum(Cost) AS TotalCost,
from work.sales_details
group by sales_channel, report_month;
quit;

proc print data=sales_sales1; run;

The final output gives me a display of all the different observations listed in order of sales_channel and report_month, but what I really need is a data set that summarizes these new variables by each category of the variables:  sales_channel and Report_month.

thank you in advance for your help

[novinosrin]

Can you post a sample of what you HAVE and what you WANT as output plz?

[utrocketeng]

here is something that might work.  good luck

Proc SQL;
Create table work.sales_sales1 AS 
SELECT 
	a.report_month
	, a.Sales_Channel
	, Count(b.CustomerID) AS CountCustomers
	, Count(c.SalesRepID) AS CountSalesRep
	, Sum(Cost) AS TotalCost
from 
	work.sales_details a
	LEFT JOIN /*Derived table below to get distinct CustomerID's for each SalesChannel/ReportMonth*/
		(SELECT DISTINCT CustomerID, sales_channel, report_month FROM work.sales_details) b ON a.sales_channel = b.sales_channel and a.report_month = b.report_month 
	LEFT JOIN /*Derived table below to get distinct SalesRepID's for each SalesChannel/ReportMonth*/	
		(SELECT DISTINCT SalesRepID, sales_channel, report_month FROM work.sales_details) c ON a.sales_channel = c.sales_channel and a.report_month = c.report_month 
group by sales_channel, report_month

;
quit;

[utrocketeng]

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;

[Shivam]

could you please show what exactly you have in your hand and what you want as an output dataset.

[Tom]

It sounds like you are asking for exactly what that code is giving you.

Perhaps you meant that you want separate reports summarized by the two variables independently?