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
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
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.
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
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.
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
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
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!
Its one thing to post multiple queries, its another to post multiple queries, multiple times and under different names.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: