## what is the efficient query to solve this

Solved
Frequent Contributor
Posts: 111

# what is the efficient query to solve this

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

Accepted Solutions
Solution
‎02-02-2015 04:36 AM
Contributor
Posts: 37

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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

All Replies
Super User
Posts: 5,876

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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.

Data never sleeps
Solution
‎02-02-2015 04:36 AM
Contributor
Posts: 37

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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

Super User
Posts: 9,599

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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.

Super User
Posts: 10,766

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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

Super User
Posts: 23,663

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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

Contributor
Posts: 47

## Re: what is the efficient query to solve this

Posted in reply to Ravikumarkummari

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!

Super User
Posts: 23,663

## Re: what is the efficient query to solve this

Posted in reply to Allaluiah

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

Contributor
Posts: 47

## Re: what is the efficient query to solve this

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.

Frequent Contributor
Posts: 111

## Re: what is the efficient query to solve this

Posted in reply to Allaluiah

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 9 replies
• 375 views
• 0 likes
• 7 in conversation