Solution#
Importez les données les données se trouvant dans le répertoire data/hr/
. Ensuite vous pouvez répondre aux questions ci-dessous.
filename mesDos 'data/hr/departments.csv';
proc import datafile=mesDos
DBMS=csv
out=departments
replace
;
run;
filename mesDos 'data/hr/employees.csv';
proc import datafile=mesDos
DBMS=csv
out=employees
replace
;
run;
filename mesDos 'data/hr/jobs.csv';
proc import datafile=mesDos
DBMS=csv
out=jobs
replace
;
run;
filename mesDos 'data/hr/job_history.csv';
proc import datafile=mesDos
DBMS=csv
out=job_history
replace
;
run;
filename mesDos 'data/hr/locations.csv';
proc import datafile=mesDos
DBMS=csv
out=locations
replace
;
run;
filename mesDos 'data/hr/regions.csv';
proc import datafile=mesDos
DBMS=csv
out=regions
replace
;
run;
title "Importation des données";
title;
Écrivez une procédure proc sql
qui permet d’obtenir un tableau contenant les informations suivantes;
first_name
last_name
department_name,
city,
state_province
proc sql outobs=5;
select E.first_name,
E.last_name,
D.department_name,
L.city,
L.state_province
from work.employees as E
left join departments D
ON E.department_id = D.department_id
left join locations L
ON D.location_id = L.location_id;
;
quit;
FIRST_NAME | LAST_NAME | DEPARTMENT_NAME | CITY | STATE_PROVINCE |
---|---|---|---|---|
Kimberely | Grant | |||
Bruce | Ernst | IT | Southlake | Texas |
Alexander | Hunold | IT | Southlake | Texas |
David | Austin | IT | Southlake | Texas |
Diana | Lorentz | IT | Southlake | Texas |
Ecrire une procédure proc sql
pour afficher le prénom, le nom, le numéro de département et le nom du département, pour tous les employés des départements 80 ou 40
proc sql outobs=5;
SELECT E.first_name , E.last_name ,
E.department_id , D.department_name
FROM employees E
left JOIN departments D
ON E.department_id = D.department_id
AND E.department_id IN (80 , 40)
ORDER BY E.last_name;
quit;
FIRST_NAME | LAST_NAME | DEPARTMENT_ID | DEPARTMENT_NAME |
---|---|---|---|
Ellen | Abel | 80 | Sales |
Sundar | Ande | 80 | Sales |
Mozhe | Atkinson | 50 | |
David | Austin | 60 | |
Hermann | Baer | 70 |
Écrire une procédure proc sql
pour afficher les prénoms des employés qui contiennent une lettre z et leur nom de famille, département, ville et état.
proc sql;
SELECT E.first_name,E.last_name,
D.department_name, L.city, L.state_province
FROM employees E
left JOIN departments D
ON E.department_id = D.department_id
left JOIN locations L
ON D.location_id = L.location_id
WHERE E.first_name LIKE '%z%';
quit;
FIRST_NAME | LAST_NAME | DEPARTMENT_NAME | CITY | STATE_PROVINCE |
---|---|---|---|---|
Mozhe | Atkinson | Shipping | South San Francisco | California |
Hazel | Philtanke | Shipping | South San Francisco | California |
Elizabeth | Bates | Sales | OX9 9ZB | Oxford |
Écrire une procédure proc sql
pour afficher le prénom et le nom et le salaire pour les employés qui gagnent moins que l’employé gagnent 182.
proc sql outobs=5;
SELECT E.first_name, E.last_name, E.salary
FROM employees E
left JOIN employees S
ON E.salary < S.salary
where E.DEPARTMENT_ID between 40 and 80
/* AND S.employee_id = 182; */
quit;
FIRST_NAME | LAST_NAME | SALARY |
---|---|---|
James | Landry | 2400 |
Steven | Markle | 2200 |
TJ | Olson | 2100 |
Ki | Gee | 2400 |
Hazel | Philtanke | 2200 |
Trouvez les employés qui gagnent moins que le salaire moyen de tous les employés
proc sql outobs=5;
select * from employees where salary < (select avg(salary) from employees);
quit;
EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | |
---|---|---|---|---|---|---|---|---|---|---|
104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000 | 0 | 103 | 60 |
105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800 | 0 | 103 | 60 |
106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800 | 0 | 103 | 60 |
107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200 | 0 | 103 | 60 |
115 | Alexander | Khoo | AKHOO | 515.127.4562 | 1987-07-02 | PU_CLERK | 3100 | 0 | 114 | 30 |
Écrire une procédure proc sql
pour afficher le titre du poste, le nom complet (prénom et nom) de l’employé, et la différence entre le salaire maximum pour l’emploi et le salaire de l’employé. Assurez-vous que les salaire sont sous format $1,234,456.00
proc sql outobs=5;
SELECT job_title, first_name || ' ' || last_name AS Employee_name,
max_salary-salary AS salary_difference format=dollar15.2
FROM employees
NATURAL JOIN jobs;
quit;
JOB_TITLE | Employee_name | salary_difference |
---|---|---|
President | Steven King | $16,000.00 |
Administration Vice President | Neena Kochhar | $13,000.00 |
Administration Vice President | Lex De Haan | $13,000.00 |
Programmer | Alexander Hunold | $1,000.00 |
Programmer | Bruce Ernst | $4,000.00 |
Écrire une procédure proc sql
pour afficher le nom du département, le salaire moyen et le nombre d’employés travaillant dans ce département qui ont reçu une commission.
proc sql outobs=10;
SELECT department_name,
AVG(salary) as SalaireMoyen format=dollar15.2,
COUNT(commission_pct) as nombre
FROM departments D
left JOIN employees E
on E.department_id=D.department_id
GROUP BY department_name
order by nombre desc
;
quit;
DEPARTMENT_NAME | SalaireMoyen | nombre |
---|---|---|
Shipping | $3,475.56 | 45 |
Sales | $8,955.88 | 34 |
Finance | $8,600.00 | 6 |
Purchasing | $4,150.00 | 6 |
IT | $5,760.00 | 5 |
Executive | $19,333.33 | 3 |
Accounting | $10,150.00 | 2 |
Marketing | $9,500.00 | 2 |
Human Resources | $6,500.00 | 1 |
Public Relations | $10,000.00 | 1 |
Écrire une procédure proc sql
pour afficher le titre du poste et le salaire moyen des employés.
proc sql;
SELECT job_title, AVG(salary) format=dollar15.2
FROM employees as E, jobs as J
where E.job_id=J.job_id
GROUP BY job_title;
quit;
JOB_TITLE | |
---|---|
Accountant | $7,920.00 |
Accounting Manager | $12,000.00 |
Administration Assistant | $4,400.00 |
Administration Vice President | $17,000.00 |
Finance Manager | $12,000.00 |
Human Resources Representative | $6,500.00 |
Marketing Manager | $13,000.00 |
Marketing Representative | $6,000.00 |
President | $24,000.00 |
Programmer | $5,760.00 |
Public Accountant | $8,300.00 |
Public Relations Representative | $10,000.00 |
Purchasing Clerk | $2,780.00 |
Purchasing Manager | $11,000.00 |
Sales Manager | $12,200.00 |
Sales Representative | $8,350.00 |
Shipping Clerk | $3,215.00 |
Stock Clerk | $2,785.00 |
Stock Manager | $7,280.00 |