I have two datasets;
1. Policy dataset where it has Policy_No and its Branch_code details;
2. Employee dataset where it has Branch_code, Employee_ID and Percentage field;
data Policy;
input Policy_No Branch $;
datalines;
101 A001
102 A001
103 A001
104 A001
105 A001
106 A001
107 A001
108 A001
109 A001
110 A001
111 A001
112 A001
;run;
data Employee;
input Branch $ Emp_ID Percent;
datalines;
A001 1111 30
A001 2222 50
;run;
I would like to create a output where 30% of policy details (any 4 policy_no out of total 12 policies) should be allocated to emp_ID 1111 and 50%(any 6 policy_no out of total 12 polices) of policy allocated to emp_ID 2222
desired output will be like this:
Branch Emp_ID Policy_no
A001 1111 101
A001 1111 102
A001 1111 103
A001 1111 104
A001 2222 105
A001 2222 106
A001 2222 107
A001 2222 108
A001 2222 109
A001 2222 110
Regards
Ash
One way to do this is :
data Policy;
input Policy_No Branch $;
datalines;
101 A001
102 A001
103 A001
104 A001
105 A001
106 A001
107 A001
108 A001
109 A001
110 A001
111 A001
112 A001
;
data Employee;
input Branch $ Emp_ID Percent;
datalines;
A001 1111 30
A001 2222 50
;
proc sort data=policy; by branch policy_no; run;
proc sort data=employee; by branch emp_id; run;
data polOrd;
set policy; by branch;
if first.branch then ord = 0;
ord + 1;
run;
data empCum;
set employee; by branch;
if first.branch then fromPct = 0;
output;
fromPct + percent;
run;
proc sql;
create table want as
select p.branch, emp_id, policy_no
from empCum as e inner join
polOrd as p on e.branch=p.branch
group by p.branch, emp_id
having
ord > round(fromPct/100*count(ord)) and
ord <= round((fromPct+percent)/100*count(ord))
order by branch, emp_id, policy_no;
quit;
PG
Hi PGStats,
Thanks for replay, it worked,
I wonder is any other method or by using macro can we achieve the result.
Ash
I imagine an alternate solution could be based on proc surveyselect. I can't however think of any use for macro processing to solve this problem.
PG
The above solution by PGStats will allocate sequence of policy no. What if it require to allocate the random policy_no instead of a series allocation.
Ash
Simply randomize the policy_no order before the selection:
data Policy;
input Policy_No Branch $;
datalines;
101 A001
102 A001
103 A001
104 A001
105 A001
106 A001
107 A001
108 A001
109 A001
110 A001
111 A001
112 A001
;
data Employee;
input Branch $ Emp_ID Percent;
datalines;
A001 1111 30
A001 2222 50
;
data polOrd;
set policy;
ord = rand("UNIFORM");
run;
proc sort data=polOrd; by branch ord; run;
proc sort data=employee; by branch emp_id; run;
proc rank data=polOrd out=polOrd;
by branch;
var ord;
run;
data empCum;
set employee; by branch;
if first.branch then fromPct = 0;
output;
fromPct + percent;
run;
proc sql;
create table want as
select p.branch, emp_id, policy_no
from empCum as e inner join
polOrd as p on e.branch=p.branch
group by p.branch, emp_id
having
ord > round(fromPct/100*count(ord)) and
ord <= round((fromPct+percent)/100*count(ord))
order by branch, emp_id, policy_no;
quit;
PG
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.