what is the efficient query to solve this

[Ravikumarkummari]

Hii all i have to data like this

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>=36
101	E	2010	12
101	F	2010	10
	72	DEVIDED/2=36
102	A	2010	30
102	B	2010	18
102	C	2010	26	74
102	D	2010	12
102	E	2010	9	74>=51
102	F	2010	7
	102	DEVIDED/2=51
102	A	2011	78
102	B	2011	45
102	C	2011	65	188
102	D	2011	35
102	E	2011	80	188 IS NOT>199
102	F	2011	95
	398	DEVIDED /2=199

the contract_type A,B,C are consider as one group i.e. REG_POL.

what my requirement is that in 2010 year the emp 101 is done 72 policies. In that the group of REG_POL share is 45 .

then may find that the total no of policies in that perticular year devided by 2.

the REG_POL value should be greater of than of this (no_of_policies/2) .

The final output we want only that employees who done their maximum policies in REG_GROUP. we need only that type of employees. if not ignore that employee.

in above example the first two employees are selected. and the third employee is rejected.

please give your valid comments to solve this type of query. using the what ever procedure i trying to by using proc sql.

Message was edited by: ravikumar kummari

[LinusH]

This is not a place where you can expect someone else to do your job - just help you with specific problems.

Try to solve your query first by yourself, then get back with a more specific question.

[Karthikeyan · Accepted solution]

Hi

Yes, please do try something out before posting . Here's how you can approach your problem if you're not really sure where/how to start. Come up with a design on how to solve this, split the problem into multiple steps and  create a simple program for each step, then you can work on optimizing your program.

Your Data

SAMPLE

Obs	emp_id	contract_type	year	no_of_policies
1	101	A	2010	10
2	101	B	2010	15
3	101	C	2010	20
4	101	D	2010	5
5	101	E	2010	12
6	101	F	2010	10
7	102	A	2010	30
8	102	B	2010	18
9	102	C	2010	26
10	102	D	2010	12
11	102	E	2010	9
12	102	F	2010	7
13	102	A	2011	78
14	102	B	2011	45
15	102	C	2011	65
16	102	D	2011	35
17	102	E	2011	80
18	102	F	2011	95

proc sql;

create table data1 as

select emp_id ,year , sum(no_of_policies) as reg_pol

from sample

where contract_type in('A','B','C')

group by emp_id, year

;

create table data2 as

select emp_id , year , sum(no_of_policies)/2 as divby_2

from sample

group by emp_id, year

;

quit;

proc sort data = data1 ; by emp_id year;run;

proc sort data = data2 ; by emp_id year;run;

DATA1:        

Obs	emp_id	year	reg_pol
1	101	2010	45
2	102	2010	74
3	102	2011	188

DATA2;

Obs	emp_id	year	divby_2
1	101	2010	36
2	102	2010	51
3	102	2011	199

data final

;

merge

data1

data2

;

by emp_id year;

if reg_pol >= divby_2 then output;

run;

FINAL:

Obs	emp_id	year	reg_pol	divby_2
1	101	2010	45	36
2	102	2010	74	51

Thanks

Karthik

[RW9]

You have actual pretty much written it yourself in your logic there.  Just need to code it.  Check out SUM() function with group by emp_id/year, then left join that back to main table.

[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     E     2010     12          
101     F     2010     10                    
102     A     2010     30          
102     B     2010     18          
102     C     2010     26     
102     D     2010     12          
102     E     2010     9     
102     F     2010     7                    
103     A     2011     78          
103     B     2011     45          
103     C     2011     65          
103     D     2011     35          
103     E     2011     80     
103     F     2011     80     
103     G     2011     80     
;
run;
proc sql;
 create table want as
  select * from have 
   group by Emp_id
    having sum(contract_type in ('A' 'B' 'C')) ge count(*)/2;
quit;

Xia Keshan

[Reeza]

Break it down into simpler problems, here's one way:

1. Find the total of all policies by employee_id and divide by 2

2. Merge result back into table

3. Calculate running total

4. Flag if greater than number found in part #1

[Allaluiah]

Hi, Feel free to post any number of queries. I think that's what the forum is for. If somebody wants to help, they will and if they don't, well they don't. If somebody is unhappy that you asked such a question, well it's their problem. It is one of these kind of queries on Feb16th,2000 that made the greatest SAS programmer ever produced Mr. Ian whitlock popular or known in the wider SAS world. As a matter of fact, the different kind of solutions that people attempt eventually become research papers for other SAS greats like Paul Dorfman, Toby Dunn et al. The ones who have become experts were all learners once. So Don't worry and just learn from other's solutions so that one day sooner or later you will be happy to provide solutions for other newbies.

All the best!

[Reeza]

Its one thing to post multiple queries, its another to post multiple queries, multiple times and under different names.

[Allaluiah]

I agree, that perhaps does happen and I noticed that too. That's of course sad and aweful.Well, I guess some are so desperate to be in SAS based careers as a means to make ends meet I suppose:smileyconfused:, and to make sure they stay in the job and get paid  they resort to other avenues such as the communities or SAS L. Weird eh?lol that's how the world wide web works, isn't it?

I have asked many many times too,many times I got the help and other times I have not received any at all. That's normal. I don't think there are any rules by the owner of SAS communities or SAS Institute that forbids people from asking questions of this type in particular as I believe forum is free and open to all and whether to respond or not is the individual's choice.

[Ravikumarkummari]

thank u Allaluiah. I want cooperation like you sir. If we are beginners and asking some of doubts and queries how to solve it.

I appreciate what you said. But what is the main problem in this sas community some of persons are feeling over enthusiasm and replying why you are posting  unnecessary. and saying why you are doing your work with others.

my sincere advise is if you know answer and if you like to do post answer those people are welcome. But if you know answer and not even like to post the answer then keep silence don't questioned other one. and judge them. its my humble advise. No one knows all in this world. We know little bit only. SAS its a ocean there is a lot of concepts and procedures in this. So No one knows all.

And once again thank u   Allaluiah sir for encouraging every one to post in sas community.