proc surveyselect and grouping

[yukki]

Hi,

I am using Sas Enterprice Guide 7.1 and I have a question about using proc survey and grouping.

I have 21 customer with different amount ,score and state.I want to assign customers to the sellers with the rates below.

%35 number and amount of customers will be in jane portfolio,% 50 and amount of customers will be in mark's portfolio and %15 number and amount of customers will be in Sandy's portfolio . Also I want to treat the sellers equally so I also want that if jane taking %35 of customers , these customers must have approximately same score and state with Mark and Jane's customers. 

I have a data like that ;

Data assignment;
input customer amount score $ state $ ;
cards;
1 1000 good paris
2 4000 good london
3 3000 good paris
4 1000 bad berlin
5 2000 middle paris
6 5000 good milan
7 7000 middle rome
8 4000 good london
9 2000 middle berlin
10 6000 bad madrid
11 2000 good paris
12 1000 good london
13 3000 good milan
14 5000 bad berlin
15 7000 middle paris
16 4000 good milan
17 8000 bad milan
18 3000 good berlin
19 2000 middle berlin
20 4000 bad madrid
21 4000 bad paris

;
run;

Data rate;
input seller $ rate ;
cards;
Jane 0.35
Mark 0.50
Sandy 0.15
;
run;

Thank you ...

[PGStats]

Proc survey can generate some random group assignments, lots of them, but it cannot optimize or balance the groups as you describe. So what you can try is to generate many random assignments, say 1000, and pick the best one, according to some measure of variation. Here is an example where I select the assignment with the lowest total relative variation (relative frequency variation for score and state, mean variation for amount) :

Data assignment;
input customer amount score $ state $ ;
cards;
1 1000 good paris
2 4000 good london
3 3000 good paris
4 1000 bad berlin
5 2000 middle paris
6 5000 good milan
7 7000 middle rome
8 4000 good london
9 2000 middle berlin
10 6000 bad madrid
11 2000 good paris
12 1000 good london
13 3000 good milan
14 5000 bad berlin
15 7000 middle paris
16 4000 good milan
17 8000 bad milan
18 3000 good berlin
19 2000 middle berlin
20 4000 bad madrid
21 4000 bad paris
;

/* Proportions of customers to be assigned to each seller.
   ASSUMED TO SUM TO ONE */
Data rate;
input seller $ rate ;
/* Add an ID value, because surveyselect will identify groups with numbers */
sellerID + 1;
cards;
Jane 0.35
Mark 0.50
Sandy 0.15
;

/* Calculate the customer group size for each seller */
proc sql;
select round(rate*(select count(customer) from assignment))
into : groupSizes separated by " "
from rate
order by sellerID;
quit;

/* Generate 1000 random assignments of customers to sellers */
proc surveyselect data=assignment groups=(&groupSizes) 
    out=groups reps=1000 seed=98685;
run;

/* get the relative frequency of score and group levels for each group within 
   each random assignment */
proc freq data=groups;
by replicate;
tables groupID*score / sparse 
    out=groupScoreFreqs(drop=count pct_col percent) outpct noprint;
tables groupID*state / sparse 
    out=groupStateFreqs(drop=count pct_col percent) outpct noprint;
run;

/* Calculate the mean amounts for each seller for each random assignment */
proc summary data=groups noprint nway;
by replicate;
class groupID;
var amount;
output out=groupAmountMeans(drop=_type_ _freq_) mean=meanAmount;
run;

proc sql;
/* Sum the inter seller coefficients of variation (CV) for all criteria: 
   score, state and amounts */
create table CVs as
select 
    replicate,
    sum(cv) as totalCV
from (
    (select replicate, score as criteria, CV(PCT_ROW) as cv 
     from groupScoreFreqs group by replicate, score)
    union all
    (select replicate, state as criteria, CV(PCT_ROW) as cv 
     from groupStateFreqs group by replicate, state)
    union all
    (select replicate, "Amount" as criteria, CV(meanAmount) as cv 
     from groupAmountMeans group by replicate)
)
group by replicate;

/* Find the replicate with the lowest total CV */
create table bestReplicate as
select
    replicate,
    totalCV
from CVs
having totalCV = min(totalCV);

/* Extract the best random assignment, corresponding to the 
   lowest total variation */
create table bestGroups as
select sellerID, customer, amount, score, state 
from groups left join rate on groupID=sellerID
where replicate in (select replicate from bestReplicate)
order by sellerID, customer;
select * from bestGroups;
quit;

[yukki]

Hi,

Thank you for the solution . Is it possible to write it in sql developer?

[yukki]

Hi,
Thank you for the solution . I have one more question.
I also want the group's total amount distirubition close to the rate (%35 %50 %15 ). Is it possible?

[PGStats]

Well, that's why I tried balancing the mean amount in my code. It is mathematically equivalent to sharing the total amount according to the rates (frequencies).

[yukki]

Hi again,

I have one more problem also.This is harder i think

I want to make groups with the group sizes given but want this sizes from the given states.(table 1)

for exampe : I want to assign Jane 2 customers from Berlin,3 customers from paris, 1 customer from madrid 

Also want to get the relative frequency of score provence and group levels for each group within each random assignment

I wrote the begining code but last part doesnt give the result i want

table 1;

Obs	seller	rate	state	sellerID	state_sum	rate_state	groupSizes
1	Jane	0.35	berlin	1	5	1.75	2
2	Mark	0.65	berlin	2	5	3.25	3
3	Jane	0.5	paris	3	6	3	3
4	Lucy	0.5	paris	4	6	3	3
5	Linda	0.15	milan	5	4	0.6	1
6	Mark	0.85	milan	6	4	3.4	3
7	Linda	1	london	7	2	2	2
8	Mark	0.8	tokyo	9	1	0.8	1
9	Jane	0.4	madrid	12	2	0.8	1
10	Linda	0.6	madrid	13	2	1.2	1

Data assignment;
input customer amount score $ state $ provence $ ;
cards;
1 1000 good paris aaaa
2 3000 good paris aaaa
3 1000 bad berlin bbbb
4 2000 middle paris cccc
5 5000 good milan dddd
6 2000 middle berlin bbbb
7 2000 good paris cccc
8 3000 good milan eeee
9 5000 bad berlin bbbb
10 7000 middle paris aaaa
11 4000 good milan eeee
12 8000 bad milan eeee
13 3000 good berlin ffff
14 2000 middle berlin ffff
15 4000 bad paris cccc
16 2000 bad london pppp
17 6000 middle london pppp
18 4000 good madrid kkkk
19 7000 bad tokyo nnnn
20 9000 bad madrid mmmm
;

proc print;
run;

proc sql;

create table Names as select distinct state from assignment;

quit;

proc print;
run;

/* Proportions of customers to be assigned to each seller.
ASSUMED TO SUM TO ONE */
Data rate;
input seller $ rate state $ ;
/* Add an ID value, because surveyselect will identify groups with numbers */
sellerID + 1;
cards;
Jane 0.35 berlin
Mark 0.65 berlin
Jane 0.50 paris
Lucy 0.50 paris
Linda 0.15 milan
Mark 0.85 milan
Linda 1 london
Lucy 0.20 tokyo
Mark 0.80 tokyo
Lucy 0.20 budapest
Mark 0.80 budapest
Jane 0.40 madrid
Linda 0.60 madrid

;

proc print;
run;

proc sql;

create table table1 as
select * from
(select a.*,b.state_sum,a.rate*b.state_sum as rate_state,
case when (round(a.rate*b.state_sum)-a.rate*b.state_sum)=0.5 THEN FLOOR(a.rate*b.state_sum)
WHEN round(a.rate*b.state_sum)>0.50 AND round(a.rate*b.state_sum)<1 THEN 1
ELSE round(a.rate*b.state_sum) END AS rate_state_r
from rate a,
(select state,count(*) as state_sum from assignment group by state) b
where
a.state=b.state
)
where rate_state_r>0 order by sellerID
;
run;

proc print;
run;

/* Calculate the customer group size for each seller */
proc sql;
select table1.rate_state_r
into : groupSizes separated by " "
from table1
order by sellerID;
quit;

/* Generate 1000 random assignments of customers to sellers */
proc surveyselect data=assignment groups=(&groupSizes)
out=groups reps=100000 seed=98685
;
run;

proc print data=groups (obs = 20) ;
run;