please help me

[tlnarayana26]

I have a dataset like this. Contract_type  A,B,C consider as one group
Emp_id	contract_type	year	no_of_policies
101	A	2010	10
101	B	2010	15
101	C	2010	20	45
101	D	2010	5		45>=18
101	E	2010	12
101	F	2010	10
				72	DEVIDED/4=18
102	A	2010	30
102	B	2010	18
102	C	2010	26	74
102	D	2010	12
102	E	2010	9		74>23.2
102	F	2010	7
102	G	2010	14	116	DEVIDED/5=23.2
102	A	2011	10
102	B	2011	20
102	C	2011	30	60
102	D	2011	35
102	E	2011	80		60 IS NOT>63.5
102	F	2011	95
102	G	2011	60
102	H	2011	75
102	I	2011	40	420	DEVIDED /7=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.

[RW9]

Sorry, is this a duplicate of this post: https://communities.sas.com/message/248856#248856

?

[tlnarayana26]

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.

[tlnarayana26]

in this case how to devide the total number of polices by contract type. here only the main problem happened.

[tlnarayana26]

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.

[LinusH]

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.

[Reeza]

@tinarayana26 You've posted under 3 different names so far, that I've counted. That doesn't really sound like someone who wants help.

[Ksharp]

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;

Xia Keshan