topic Re: Proc Rank - excluding zeros in SAS Programming

Proc Rank - excluding zeros

hamhocks — Tue, 28 Oct 2014 22:25:39 GMT

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!

Re: Proc Rank - excluding zeros

ballardw — Tue, 28 Oct 2014 23:29:35 GMT

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.

Re: Proc Rank - excluding zeros

PGStats — Wed, 29 Oct 2014 00:28:31 GMT

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)

Re: Proc Rank - excluding zeros

hamhocks — Wed, 12 Nov 2014 06:01:37 GMT

thanks! this worked perfectly