Help using Base SAS procedures

what is the efficient query to solve this

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

what is the efficient query to solve this

Hii all i have to data like this

Emp_idcontract_typeyearno_of_policies
101A201010
101B201015
101C20102045
101D2010545>=36
101E201012
101F201010
72DEVIDED/2=36
102A201030
102B201018
102C20102674
102D201012
102E2010974>=51
102F20107
102DEVIDED/2=51
102A201178
102B201145
102C201165188
102D201135
102E201180188 IS NOT>199
102F201195
398DEVIDED /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

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

Obsemp_idcontract_typeyearno_of_policies
1101A201010
2101B201015
3101C201020
4101D20105
5101E201012
6101F201010
7102A201030
8102B201018
9102C201026
10102D201012
11102E20109
12102F20107
13102A201178
14102B201145
15102C201165
16102D201135
17102E201180
18102F201195

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:        

Obsemp_idyearreg_pol
1101201045
2102201074
31022011188

DATA2;

Obsemp_idyeardivby_2
1101201036
2102201051
31022011199

data final

;

merge

data1

data2

;

by emp_id year;

if reg_pol >= divby_2 then output;

run;

FINAL:

Obsemp_idyearreg_poldivby_2
110120104536
210220107451

Thanks

Karthik

View solution in original post


All Replies
Super User
Posts: 5,255

Re: what is the efficient query to solve this

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

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

Obsemp_idcontract_typeyearno_of_policies
1101A201010
2101B201015
3101C201020
4101D20105
5101E201012
6101F201010
7102A201030
8102B201018
9102C201026
10102D201012
11102E20109
12102F20107
13102A201178
14102B201145
15102C201165
16102D201135
17102E201180
18102F201195

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:        

Obsemp_idyearreg_pol
1101201045
2102201074
31022011188

DATA2;

Obsemp_idyeardivby_2
1101201036
2102201051
31022011199

data final

;

merge

data1

data2

;

by emp_id year;

if reg_pol >= divby_2 then output;

run;

FINAL:

Obsemp_idyearreg_poldivby_2
110120104536
210220107451

Thanks

Karthik

Super User
Super User
Posts: 7,392

Re: what is the efficient query to solve this

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: 9,671

Re: what is the efficient query to solve this

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: 17,760

Re: what is the efficient query to solve this

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: 46

Re: what is the efficient query to solve this

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: 17,760

Re: what is the efficient query to solve this

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

Contributor
Posts: 46

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

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.

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

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