10-28-2014 06:25 PM
I am trying to create deciles for sales data - by channel
So I have a list of customers with their total sales, online sales, and store sales. I want to create ranked deciles for each one of these.
seems easy, but here's the catch. I want to ignore zeros. In many cases their sales will be in stores onlty so there will be a zero for online sales. I don't want to include that record in the ranking of online.
Will i need to split them up into separate tables to do this and then put them back together?
here;s the current code:
proc rank data=&reportname out=&reportname groups=10 ties=high;
ranks r_txns r_ol_txns r_st_txns;
var txns ol_txns st_txns;
any guidance would be much appreciated!
10-28-2014 07:29 PM
Probably need a variable with missing instead of 0 for the online. If you might need the original value then take a pass through a data step to add a new variable.
10-28-2014 08:28 PM
Try using a view:
data v / view=v;
if txns > 0 then r_txns = txns;
if ol_txns > 0 then r_ol_txns = ol_txns;
if st_txns > 0 then r_st_txns = st_txns;
proc rank data=v out=&reportname groups=10 ties=high;
var r_txns r_ol_txns r_st_txns;