Proc Rank - excluding zeros

[hamhocks]

Hi all,

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;

by segment;

run;

any guidance would be much appreciated!

thanks!

[ballardw]

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.

[PGStats]

Try using a view:

data v / view=v;

set &reportname;

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;

run;

proc rank data=v out=&reportname  groups=10 ties=high;

var r_txns r_ol_txns r_st_txns;

by segment;

run;

(untested)

PG

[hamhocks]

thanks! this worked perfectly