BookmarkSubscribeRSS Feed
tlnarayana26
Calcite | Level 5
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.

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

?

tlnarayana26
Calcite | Level 5

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
Calcite | Level 5

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

tlnarayana26
Calcite | Level 5

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
Tourmaline | Level 20

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
Reeza
Super User

@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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 910 views
  • 0 likes
  • 5 in conversation