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
Can you post a sample of what you HAVE and what you WANT as output plz?
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;
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;
could you please show what exactly you have in your hand and what you want as an output dataset.
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.