topic Re: Creating a new variable based on Count of 2 other variables in SAS Programming

Creating a new variable based on Count of 2 other variables

alexanthonymaj0 — Thu, 18 Apr 2019 01:50:35 GMT

Hi Everyone,

I am looking to create a new variable that counts unique customers, based on the month - represented by Custer_ID and Report_Month respectively. Any help would be greatly appreciated!

Here is what I have so far - thanks in advance

data sales_summary1;
set Sales_Details;
Distinct_Customer = count(Customer_Id,Report_Month);
keep Distinct_Customer report_month sales_channel;
run;

Re: Creating a new variable based on Count of 2 other variables

ChrisNZ — Thu, 18 Apr 2019 03:49:19 GMT

Like this?

proc sql;
  select Report_Month, count(unique Custer_ID) as Count
  from Sales_Details
  group by Report_Month;
quit;

Re: Creating a new variable based on Count of 2 other variables

alexanthonymaj0 — Thu, 18 Apr 2019 06:52:22 GMT

Yes, thank you so much for your reply, I am really appreciative! I have a couple of follow-up questions, if you don't mind.

First off, I am trying to group the variables (1) Count_Customer (2) Count_Sales_Rep (3) Sales_Volume based on

(1) the Channel 'Sales_Channel' and (2) the Month 'Report_Month' -- I know the results I am getting are not correct (I have attached to this post). How would I go about achieving this?

proc sql;
create table work.sales_summary1 AS
select report_month, count(unique Customer_ID) as Count_Customer, count(unique Sales_Rep_ID) as Count_Sales_Rep, Sales_Volume, Sales_Channel
from sales_details
group by Report_Month and Sales_Channel;

quit;