BookmarkSubscribeRSS Feed
soham_sas
Quartz | Level 8

hi

 

i have an employee table with emp_ID , salary and dept ,
 i want the dept where there are more employee having the salary of more than Rs 2450

 

below is that table

empno

ename

sal

deptno

7499

ALLEN

1600

30

7521

WARD

1250

30

7566

JONES

2975

20

7654

MARTIN

1250

30

7698

BLAKE

2850

30

7782

CLARK

2450

10

7788

SCOTT

3000

20

7839

KING

5000

10

7844

TURNER

1500

30

7876

ADAMS

1100

20

7900

JAMES

950

30

7902

FORD

3000

20

7934

MILLER

1300

10

5 REPLIES 5
Reeza
Super User

Your question doesn't appear to match your subject line ...can you explain your question more? Is it total amount per dept greater than the amount?

 

Are you using a data step or proc SQL?  

 

 

soham_sas
Quartz | Level 8
Hi Reeza

i am using a PROC SQL ..

The requirement is , we want the deptno in which the employee count is more
having salary of more than 2450 ..


##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

I think you mean sum, not count? Either way it's a similar idea, group by department and filter using a having clause. 

 

Proc SQL;
Create table want as
Select dept
From have
Group by dept
Having sum(amt)> 2450;
Quit;
Tom
Super User Tom
Super User

Get the count first and then find the max.

select deptno
from 
  (select deptno,count(*) as count
   from HAVE 
   where sal > 2450
   group by 1
  )
having count=max(count)
;
KachiM
Rhodochrosite | Level 12

You have got SQL solutions.

 

Here is data step approach. This could be the fast way for a data set having millions of employees.

An array is used for Sorting, Counting and Lookups - all in one data step !

You may test and disprove me if I am wrong.

data _null_;
array k[10:30] _temporary_;
   set have end = last;
   if sal > 2450 then k[deptno] + 1;
   if last then do;
      max = 0;
      do i = 10 to 30;
         if not missing(k[i]) and k[i] > max then do;
            max = k[i];
            deptno = i;
         end;
      end;
      put max= deptno =;
   end; 
run;

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
  • 5 replies
  • 1544 views
  • 0 likes
  • 4 in conversation