please help me

Reply
Contributor
Posts: 40

please help me

I have a dataset like this. Contract_type  A,B,C consider as one group
Emp_idcontract_typeyearno_of_policies
101A201010
101B201015
101C20102045
101D2010545>=18
101E201012
101F201010
72DEVIDED/4=18
102A201030
102B201018
102C20102674
102D201012
102E2010974>23.2
102F20107
102G201014116DEVIDED/5=23.2
102A201110
102B201120
102C20113060
102D201135
102E20118060 IS NOT>63.5
102F201195
102G201160
102H201175
102I201140420DEVIDED /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.

Super User
Super User
Posts: 7,720

Re: please help me

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

?

Contributor
Posts: 40

Re: please help me

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.

Contributor
Posts: 40

Re: please help me

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

Contributor
Posts: 40

Re: please help me

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.

Super User
Posts: 5,388

Re: please help me

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.

Data never sleeps
Super User
Posts: 19,167

Re: please help me

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

Super User
Posts: 9,875

Re: please help me

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

Ask a Question
Discussion stats
  • 7 replies
  • 449 views
  • 0 likes
  • 5 in conversation