CODEWITHSUNDEEP
sqlcountsas
shep123
shep123

Reputation: 41

SQL Count Expressions

I am trying to create a table to will count the occurrences of each position for various offices.

So if my data is as follows:

Office Position
A      Manager
A      Supervisor
A      Entry Level
A      Entry Level
B      Manager
B      Entry Level

I would want my code to return:

Office    Managers    Supervisors     EntryLevel
A            1             1              2
B            1             0              1

I have my code below. The issue is that this code counts the total amount of occurrences, not the unique count to each office. The results are as follows

A   2   1   3
B   2   1   3

CREATE TABLE OfficeTest AS
SELECT DISTINCT Office,
(Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Manager') as Managers, 
(Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Supervisor') as Supervisors,
(Select COUNT(Position) FROM OfficeData  WHERE Make_Name = 'Entry Level') as EntryLevel

FROM OfficeData 
GROUP BY Office;

Any ideas on how to fix this?

Upvotes: 3

Views: 247

Answers (3)

Tim Sands
Tim Sands

Reputation: 1068

Just to flush out Danny's comment a bit, the SUM code would look like:

proc sql;
    CREATE TABLE want AS
    SELECT office,
    SUM( (position='Manager') ) as Managers,
    SUM( (position='Supervisor') ) as Supervisors,
    SUM( (position='Entry Level') ) as EntryLevel
FROM OfficeData
GROUP BY office
;quit;

The (position='Manager') bit resolves to 0 or 1, depending on if its true for the current record. I find the SUM version a lot more concise and legible, but both should work for your situation. Plus, its easily extensible to more than one criteria, like (postion='Manager')*(sex='F') to count only female managers.

Upvotes: 1

sushil
sushil

Reputation: 1576

SUM with CASE statement should resolve the issue. Below is a reference code

proc sql;
create table result as 
select age
      , sum(case sex when 'F' then 1 else 0 end) as Female
      , sum(case sex when 'M' then 1 else 0 end) as Male
from sashelp.class
group by age;
quit;
proc print data=result;run;

Upvotes: 0

Josh Part
Josh Part

Reputation: 2164

The easiest way I can think of doing this is like this:

SELECT Office,
       COUNT(CASE WHEN Make_Name = 'Manager' THEN Position END) AS Managers,
       COUNT(CASE WHEN Make_Name = 'Supervisor' THEN Position END) AS Supervisors,
       COUNT(CASE WHEN Make_Name = 'Entry Level' THEN Position END) AS EntryLevel
FROM OfficeData
GROUP BY Office

COUNT ignores MISSING values; if the Position is not the one specified in the CASE clause, it will return a MISSING value and won't be counted. This way each case considers only the value of Position you compare.

Another option, as stated in the comments, would be pivoting the table. The SAS equivalent is the TRANSPOSE procedure. I don't have a SAS system to create and test a query using it, but here's the documentation in case you want to check it out.

Upvotes: 2

Related Questions