03-09-2017 06:45 AM
Hopefully an easy question to solve, but I'm back to using Base SAS after 8 years of using SAS Enterprise Guide and I'm getting a mental block!!
Below is my proc sql code
create table mktg_count
count(distinct DM_CUST_KEY) as Total_Customers,
sum( case when valid_email = 'Y' and SUPRS_EMAIL_FLAG = 'N' and OPT_OUT_FLAG = 'N' then 1 else 0 end) as Emailable,
sum( case when SUPRS_MAIL_FLAG = 'N' and OPT_OUT_FLAG = 'N' then 1 else 0 end) as Mailable
where PROD_DETAIL not in ('Home','Motor')
group by PROD_DETAIL;
What I'm trying to do is create the Emailable and Mailable columns for the distinct values of Total Customers, but my sum function is bringing back the sum for the whole of my table instead.
Can anybody please help me with this so that the sum function is only on distinct customers. I've tried putting the calculated columns in the group by statement, but that doesn't seem to work.
03-09-2017 06:59 AM
If you provide some example data to play around with (in a data step), I could try to write some code for this.
Basically I would sum up first on DM_CUST_KEY (I guess that if a DM_CUST_KEY has at least one valid_email = 'Y', you want to count that as 1 for that particular DM_CUST_KEY), and then sum on the PROD_DETAIL in a second step. But to get a feel for your data, I'd need that first.
03-09-2017 07:04 AM
Can you supply some test data - in the form of a datastep - and what the output should look like. It is confusing to me that you have count(distinct DM_CUST_KEY), but want this to be unique keys? Its either a count of that, or a unique list of but not both. The where should be moved to a subquery or having clause. And its likely a simple datastep or proc would do this better anyways. But without some data its hard to say.
03-09-2017 09:38 AM
Not sure what you mean by unique customer in the sum functions. You have a nested case using three columns. How would you expect that to be evaluated across multiple rows for a single customer?
Perhaps it's easier to do a preparation step to first give you unique rows for a customer, then do the count?
03-09-2017 10:15 AM
I've solved this for myself now. I had an extra proc sort de-dupe missing originally so I've added that in now and got the results I wanted. Schoolboy error :-).
Thanks for all your help and comments, though.