02-02-2015 05:42 AM
|I have a dataset like this. Contract_type A,B,C consider as one group|
|102||E||2011||80||60 IS NOT>63.5|
if the total no of policies in the year 2010 is 72. then in that year contract types are 4. (A,B,C) is one group. remaining D, E so that contract types are 4 in this case.
the total of policies 72 is devided by the contract types in the year 2010 in pertiular emp_id 101.
let this value will be 72/4=18.
the total number of policies in the (A,B,C) one group is 45.
the final output is that the total number of policies in that (A,B,C) 45>=18. in this case select that emp. other wise ignore that person.
note: the contract type is changing year to year. in second case it will be 5. and third case it is 7. observe the data.
in above the first two employees are selected. but the last emp as rejected.
please help me to find the desired result.
02-02-2015 05:51 AM
sir u right it was the before i shared.but the output in this different.
in the first output the result is same but condition is different. If you solve this query it is very helpful for me.
02-02-2015 05:55 AM
i think it is possible only for you to solve this query. i saw your output its working and once again i am saying thanks for your support.
now i am a learner in sas. so thats why it is some how difficult for me solve. please sir help me.
02-02-2015 06:07 AM
If you lack of competence to even start this task, you need either get yourself some training, or hire someone to help you in your project. This is not a place where you can get customized solutions for free.
02-03-2015 02:50 AM
If I understood what you mean.
data have; input Emp_id contract_type $ year no_of_policies ; cards; 101 A 2010 10 101 B 2010 15 101 C 2010 20 101 D 2010 5 101 D 2010 12 101 D 2010 10 102 A 2010 30 102 B 2010 18 102 C 2010 26 102 D 2010 12 102 D 2010 9 102 D 2010 7 103 A 2011 78 103 B 2011 45 103 C 2011 65 103 D 2011 35 103 D 2011 80 103 D 2011 80 103 D 2011 80 ; run; proc sql; create table want as select * from have as a group by Emp_id having sum(contract_type in ('A' 'B' 'C')) ge count(*)/((select count(distinct contract_type) from have where Emp_id=a.Emp_id)-(select count(distinct contract_type)-1 from have where Emp_id=a.Emp_id and contract_type in ('A' 'B' 'C'))); quit;