topic Re: summary data set by groups SQL in SAS Programming

summary data set by groups SQL

Dublin187 — Wed, 03 Apr 2019 18:06:23 GMT

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

Re: summary data set by groups SQL

novinosrin — Wed, 03 Apr 2019 18:11:02 GMT

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

Re: summary data set by groups SQL

utrocketeng — Wed, 03 Apr 2019 19:02:37 GMT

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;

Re: summary data set by groups SQL

Shivam — Thu, 04 Apr 2019 08:38:57 GMT

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

Re: summary data set by groups SQL

utrocketeng — Thu, 04 Apr 2019 12:35:09 GMT

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;

Re: summary data set by groups SQL

Tom — Thu, 04 Apr 2019 12:59:07 GMT

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?