BookmarkSubscribeRSS Feed
hamhocks
Calcite | Level 5

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!

3 REPLIES 3
ballardw
Super User

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
Opal | Level 21

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

PG
hamhocks
Calcite | Level 5

thanks! this worked perfectly

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2073 views
  • 3 likes
  • 3 in conversation