{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Solution" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Importez les données les données se trouvant dans le répertoire `data/hr/`. Ensuite vous pouvez répondre aux questions ci-dessous." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "filename mesDos 'data/hr/departments.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=departments\n", " replace\n", " ;\n", "run;\n", "\n", "filename mesDos 'data/hr/employees.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=employees\n", " replace\n", " ;\n", "run;\n", "\n", "filename mesDos 'data/hr/jobs.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=jobs\n", " replace\n", " ;\n", "run;\n", "\n", "filename mesDos 'data/hr/job_history.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=job_history\n", " replace\n", " ;\n", "run;\n", "\n", "filename mesDos 'data/hr/locations.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=locations\n", " replace\n", " ;\n", "run;\n", "\n", "filename mesDos 'data/hr/regions.csv';\n", "proc import datafile=mesDos\n", " DBMS=csv\n", " out=regions\n", " replace\n", " ;\n", "run;\n", "title \"Importation des données\";\n", "title;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrivez une procédure `proc sql` qui permet d'obtenir un tableau contenant les informations suivantes;\n", "* first_name\n", "* last_name\n", "* department_name,\n", "* city, \n", "* state_province" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_NAMECITYSTATE_PROVINCE
KimberelyGrant   
BruceErnstITSouthlakeTexas
AlexanderHunoldITSouthlakeTexas
DavidAustinITSouthlakeTexas
DianaLorentzITSouthlakeTexas
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", "\tselect \tE.first_name, \n", "\t\t\tE.last_name,\n", "\t\t\tD.department_name,\n", "\t\t\tL.city, \n", "\t\t\tL.state_province\n", "\tfrom work.employees as E\n", "\tleft join departments D \n", " ON E.department_id = D.department_id\n", " \tleft join locations L\n", " ON D.location_id = L.location_id;\n", "\t;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_IDDEPARTMENT_NAME
EllenAbel80Sales
SundarAnde80Sales
MozheAtkinson50 
DavidAustin60 
HermannBaer70 
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", "SELECT E.first_name , E.last_name , \n", " E.department_id , D.department_name \n", " FROM employees E \n", " left JOIN departments D \n", " ON E.department_id = D.department_id \n", " AND E.department_id IN (80 , 40)\n", " ORDER BY E.last_name;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "É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." ] }, { "cell_type": "code", "execution_count": 30, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMEDEPARTMENT_NAMECITYSTATE_PROVINCE
MozheAtkinsonShippingSouth San FranciscoCalifornia
HazelPhiltankeShippingSouth San FranciscoCalifornia
ElizabethBatesSalesOX9 9ZBOxford
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", "SELECT E.first_name,E.last_name,\n", " D.department_name, L.city, L.state_province\n", " FROM employees E \n", " left JOIN departments D \n", " ON E.department_id = D.department_id \n", " left JOIN locations L \n", " ON D.location_id = L.location_id \n", " WHERE E.first_name LIKE '%z%';\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "É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." ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
FIRST_NAMELAST_NAMESALARY
JamesLandry2400
StevenMarkle2200
TJOlson2100
KiGee2400
HazelPhiltanke2200
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", "SELECT E.first_name, E.last_name, E.salary \n", " FROM employees E \n", " left JOIN employees S\n", " ON E.salary < S.salary \n", " where E.DEPARTMENT_ID between 40 and 80\n", "/* AND S.employee_id = 182; */ \n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Trouvez les employés qui gagnent moins que le salaire moyen de tous les employés" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
EMPLOYEE_IDFIRST_NAMELAST_NAMEEMAILPHONE_NUMBERHIRE_DATEJOB_IDSALARYCOMMISSION_PCTMANAGER_IDDEPARTMENT_ID
104BruceErnstBERNST590.423.45681987-06-21IT_PROG6000010360
105DavidAustinDAUSTIN590.423.45691987-06-22IT_PROG4800010360
106ValliPataballaVPATABAL590.423.45601987-06-23IT_PROG4800010360
107DianaLorentzDLORENTZ590.423.55671987-06-24IT_PROG4200010360
115AlexanderKhooAKHOO515.127.45621987-07-02PU_CLERK3100011430
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", "select * from employees where salary < (select avg(salary) from employees);\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "É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`" ] }, { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
JOB_TITLEEmployee_namesalary_difference
PresidentSteven King$16,000.00
Administration Vice PresidentNeena Kochhar$13,000.00
Administration Vice PresidentLex De Haan$13,000.00
ProgrammerAlexander Hunold$1,000.00
ProgrammerBruce Ernst$4,000.00
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 33, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", "SELECT job_title, first_name || ' ' || last_name AS Employee_name, \n", "\tmax_salary-salary AS salary_difference format=dollar15.2 \n", "\tFROM employees \n", "\t\tNATURAL JOIN jobs;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "É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." ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
DEPARTMENT_NAMESalaireMoyennombre
Shipping$3,475.5645
Sales$8,955.8834
Finance$8,600.006
Purchasing$4,150.006
IT$5,760.005
Executive$19,333.333
Accounting$10,150.002
Marketing$9,500.002
Human Resources$6,500.001
Public Relations$10,000.001
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 34, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=10;\n", "SELECT \tdepartment_name, \n", "\t\tAVG(salary) as SalaireMoyen format=dollar15.2, \n", "\t\tCOUNT(commission_pct) as nombre\n", "\tFROM departments D\n", "\t\tleft JOIN employees E\n", "\t\ton E.department_id=D.department_id\n", "GROUP BY department_name\n", "order by nombre desc\n", ";\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Écrire une procédure `proc sql` pour afficher le titre du poste et le salaire moyen des employés." ] }, { "cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
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
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 35, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", "SELECT job_title, AVG(salary) format=dollar15.2 \n", "\tFROM employees as E, jobs as J\n", "\twhere E.job_id=J.job_id\n", "\t\t\tGROUP BY job_title;\n", "quit;" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.13" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 4 }