10 PROC SQL Interview Questions and Answers

PROC SQL is a powerful tool within the SAS programming environment, enabling users to perform complex data manipulation and querying tasks with ease. It combines the functionality of traditional SQL with the flexibility and robustness of SAS, making it an essential skill for data analysts and programmers working with large datasets. Mastery of PROC SQL can significantly enhance your ability to manage and analyze data efficiently.

This article offers a curated selection of PROC SQL interview questions designed to test and expand your knowledge. By working through these questions, you will gain a deeper understanding of key concepts and techniques, preparing you to confidently tackle interview scenarios and demonstrate your proficiency in PROC SQL.

PROC SQL Interview Questions and Answers

1. Write a basic SELECT statement to retrieve all columns from a table named ’employees’.

To retrieve all columns from a table named ’employees’ using PROC SQL in SAS, use the following statement:

proc sql;
    select * 
    from employees;
quit;

This selects all columns from the ’employees’ table. The asterisk (*) is a wildcard character that means “all columns.”

2. Perform an INNER JOIN between two tables, ’employees’ and ‘departments’, on the ‘department_id’ column.

An INNER JOIN combines rows from two tables based on a related column. The result set includes only rows with matches in both tables.

Example:

PROC SQL;
   SELECT e.employee_id, e.employee_name, d.department_name
   FROM employees e
   INNER JOIN departments d
   ON e.department_id = d.department_id;
QUIT;

This query selects employee_id and employee_name from employees and department_name from departments where department_id matches.

3. Write a subquery to find employees who earn more than the average salary in the ’employees’ table.

A subquery is a query nested inside another query. To find employees earning more than the average salary:

proc sql;
    select name, salary
    from employees
    where salary > (select avg(salary) from employees);
quit;

4. Use the GROUP BY and HAVING clauses to find departments with more than 10 employees.

The GROUP BY clause groups rows with the same values, and the HAVING clause filters these groups. To find departments with more than 10 employees:

PROC SQL;
    SELECT Department, COUNT(*) AS EmployeeCount
    FROM Employees
    GROUP BY Department
    HAVING COUNT(*) > 10;
QUIT;

This retrieves the department and employee count, filtering out those with 10 or fewer employees.

5. Create a new table named ‘managers’ with columns ‘manager_id’, ‘name’, and ‘department_id’.

To create a new table named ‘managers’ with specified columns:

proc sql;
    create table managers (
        manager_id num,
        name char(50),
        department_id num
    );
quit;

6. Update the ‘salary’ column in the ’employees’ table by increasing all salaries by 10%.

To increase all salaries by 10% in the ’employees’ table:

proc sql;
   update employees
   set salary = salary * 1.10;
quit;

7. Delete all records from the ’employees’ table where the ‘department_id’ is NULL.

To delete records where ‘department_id’ is NULL:

proc sql;
   delete from employees
   where department_id is null;
quit;

8. Create a view named ‘high_earners’ that includes employees with a salary greater than $100,000.

To create a view named ‘high_earners’ for employees with a salary over $100,000:

proc sql;
    create view high_earners as
    select *
    from employees
    where salary > 100000;
quit;

9. Write a recursive query to display a hierarchy of employees and their managers from the ’employees’ table.

Recursive queries handle hierarchical data. Here’s an example to display a hierarchy of employees and their managers:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, manager_id, employee_name, level
FROM EmployeeHierarchy
ORDER BY level, manager_id, employee_id;

10. Show how to create an index on the ’employees’ table to improve query performance.

Creating an index can improve query performance. To create an index on the employees table:

proc sql;
   create index emp_id
   on employees(emp_id);
quit;

This index helps speed up queries involving the emp_id column.