{ "cells": [ { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Les jointures de tables partie_1" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "Lorsque nous voulons avoir une vue d'ensemble des informations contenues dans plusieurs tables, nous ferons alors **joindre** ces tables afin d'assembler toute l'information dans une table ou une vue (_view_)." ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "\"les" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Afin de mieux comprendre la jointure de table, créons deux tables `A` et `B`." ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "

\n", "\n", "
111  ods listing close;ods html5 file=stdout options(bitmap_mode='inline') device=png; ods graphics on / outputfmt=png;
NOTE: Writing HTML5 Body file: STDOUT
112
113 proc sql;
114 create table sql.A
115 (X num,
116 Y num);
NOTE: Table SQL.A created, with 0 rows and 2 columns.
117 insert into sql.A
118 values(1,2)
119 values(2,3);
NOTE: 2 rows were inserted into SQL.A.

120 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds

121 ods html5 close;ods listing;

122
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " create table sql.A \n", " (X num,\n", " Y num);\n", " insert into sql.A\n", " values(1,2)\n", " values(2,3);\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Aperçu de la table `A`" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

produit cartésien de la table A et de la table B

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
XY
12
23
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", "select *\n", " from sql.A;\n", "quit;" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

The SAS System

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
XY
25
36
49
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " create table sql.B \n", " (X num,\n", " Y num);\n", " insert into sql.B\n", " values(2,5)\n", " values(3,6)\n", " values(4,9);\n", " select *\n", " from sql.B;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Aperçu de la table `B`" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

The SAS System

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
XY
25
36
49
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", "select *\n", " from sql.B;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Produit cartésien" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "La manière la plus simple de joindre deux tables et de relier chaque ligne de la table A avec les lignes de la table B avec la clause `from sql.A, sql.B`. Ceci donne un produit cartésien tel qu'illustré ci-dessous." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

produit cartésien de la table A et de la table B

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
XYXY
1225
1236
1249
2325
2336
2349
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " title 'produit cartésien de la table A et de la table B';\n", " select *\n", " from sql.A, sql.B;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## `Inner join` avec `Where`\n", "\n", "Lorsque nous voulons le sous-ensemble de lignes de la première table qui correspond aux lignes de la deuxième table, nous appliquons alors un `inner join` où (jointure interne). Nous pouvons spécifier les colonnes que nous souhaitons comparer pour les valeurs correspondantes dans une clause WHERE." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

Inner Join

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
XYXY
2325
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " title 'Inner Join';\n", " select * from sql.A, sql.B\n", " where A.x=B.x;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## Utilisation d'alias de table\n", "\n", "Un alias de table est un nom temporaire et alternatif pour une table. Nous spécifions les alias de table dans la clause `FROM`. Les alias de table sont utilisés dans les jointures pour qualifier les noms de colonnes et peuvent rendre une requête plus facile à lire en abréviation des noms de table.\n", "\n", "Soit la table `oilprod` qui provient du livre de référence [Ronald P. Cody.](https://books.google.ca/books/about/Learning_SAS_by_Example.html?id=S49APgAACAAJ&redir_esc=y)" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

Coûts moyens des sinistres

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
nameprovincecompanylanguedate_naissanceageeage_permisnumeropol
Shane RobinsonNova ScotiaMay Ltdfr1944-10-2072241
Courtney NguyenSaskatchewanFoley, Moore and Mitchellen1985-12-0931245
Lori WashingtonYukon TerritoryRobinson-Reyesfr1970-01-27472813
Sarah CastilloAlbertaWood, Brady and Englishfr2000-08-23161616
Jeffrey GarciaNunavutBerger-Thompsonen1969-10-25472022
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", " select * from libSql.donnes_demo\n", "quit;" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

apperçu de la table oilprod

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
CountryBarrelsPerDay
Algeria1,400,000
Canada2,500,000
China3,000,000
Egypt900,000
Indonesia1,500,000
Iran4,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=6;\n", " title ' apperçu de la table oilprod';\n", " select * from sql.oilprod \n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Soit la table `oilrsrvs`" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "
\n", "
\n", "

apperçu de la table oilrsrvs

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
CountryBarrels
Algeria9,200,000,000
Canada7,000,000,000
China25,000,000,000
Egypt4,000,000,000
Gabon1,000,000,000
Indonesia5,000,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=6;\n", " title ' apperçu de la table oilrsrvs';\n", " select * from sql.oilrsrvs \n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "On voudrait maintenant avoir un aperçu des deux tables dans une seule table (ou en créer une toute nouvelle avec une jointure). \n", "\n", "Nous donnons un alias `o` à la table `oilprod `. Nous écrivons alors `sql.oilprod` **`as`** `o`\n", "\n", "Lorsque nous écrivons la clause `where o.country = r.country;`, il devient plus facile de comprendre que nous voulons extraire les données où valeurs de la colonne `country` de la table `oilprod` alias `o` sont égaux aux valeurs de la colonne `country` de la table `oilrsrvs` alias `r`" ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

Production et réserves de pétrole par pays

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
CountryBarrelsPerDayCountryBarrels
Algeria1,400,000Algeria9,200,000,000
Canada2,500,000Canada7,000,000,000
China3,000,000China25,000,000,000
Egypt900,000Egypt4,000,000,000
Indonesia1,500,000Indonesia5,000,000,000
Iran4,000,000Iran90,000,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=6;\n", " title 'Production et réserves de pétrole par pays';\n", " select * from sql.oilprod as o, sql.oilrsrvs as r \n", " where o.country = r.country;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Affichage\n", "Dans l'exemple précédent, il s'affiche deux colonnes `Country`, nous pouvons spécifier ce qui s'affiche. Nous pouvons préciser quelles colonnes afficher et dans quel format ou titre.\n", "\n", "Dans l'exemple ci-dessous, nous voulons seulement trois colonnes: \n", "1. p.country affiché avec sous le label pays\n", "2. p.barrelsperday affiché avec sous le label Production\n", "3. r.barrels affiché avec sous le label 'Réserves'" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

La production et les réserves du pétrole par pays

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
PaysProductionRéserves
Saudi Arabia9,000,000260,000,000,000
United States of America8,000,00030,000,000,000
Iran4,000,00090,000,000,000
Norway3,500,00011,000,000,000
Mexico3,400,00050,000,000,000
China3,000,00025,000,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=6;\n", " title 'La production et les réserves du pétrole par pays';\n", " select p.country 'Pays', p.barrelsperday 'Production', r.barrels 'Réserves' /* ici nous affichons seulement la co*/\n", " from sql.oilprod p, sql.oilrsrvs r\n", " where p.country = r.country\n", " order by barrelsperday desc;\n", "quit; \n", "title;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## `Inner join` avec `On`" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "Les mots-clés INNER JOIN peuvent être utilisés pour rejoindre des tables. La clause ON remplace la clause WHERE pour spécifier les colonnes à joindre. PROC SQL fournit ces mots-clés principalement pour la compatibilité avec les autres jointures (OUTER, DROITE et GAUCHE JOIN). L'utilisation de INNER JOIN avec une clause ON fournit la même fonctionnalité que la liste des tableaux dans la clause FROM et la spécification des colonnes de jointure avec une clause WHERE." ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "slideshow": { "slide_type": "subslide" } }, "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", "
PaysProductionRéserves
Saudi Arabia9,000,000260,000,000,000
United States of America8,000,00030,000,000,000
Iran4,000,00090,000,000,000
Norway3,500,00011,000,000,000
Mexico3,400,00050,000,000,000
China3,000,00025,000,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=6;\n", " select p.country 'Pays', barrelsperday 'Production', barrels 'Réserves'\n", " from sql.oilprod p inner join sql.oilrsrvs r\n", " on p.country = r.country\n", " order by barrelsperday desc;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "## Création des tables;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "Comme nous venons de le faire dans les exemples précédents, nous pouvons créer une table avec `CREATE TABLE`. Nous nous pouvons spécifier le nom de la colonne, le type, le nombre maximal de caractères à y insérer, le format et le l'étiquette (_label_)" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "slideshow": { "slide_type": "subslide" } }, "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", "
KeyVeggies
MonBroccoli
TueCelery
ThuLettuce
FriSpinach
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " create table sql.l_data \n", " (Key char(12), Veggies char(12));\n", " insert into sql.l_data\n", " values(\"Mon\",\"Broccoli\")\n", " values(\"Tue\",\"Celery\")\n", " values(\"Thu\",\"Lettuce\")\n", " values(\"Fri\",\"Spinach\");\n", " select *\n", " from sql.l_data;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "Regardez bien votre répertoire de librairies !, vous allez trouver un fichier sous le nom `l_data.sas7bdat`\n", "\n", "La colonne `Key` peut avoir le _label_ qu'on voudrait." ] }, { "cell_type": "code", "execution_count": 26, "metadata": { "slideshow": { "slide_type": "fragment" } }, "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", "
étiquette de clonne détailléVeggies
MonBroccoli
TueCelery
ThuLettuce
FriSpinach
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " create table sql.l_data2 \n", " (Key char(12) label='étiquette de clonne détaillé', Veggies char(12));\n", " insert into sql.l_data2\n", " values(\"Mon\",\"Broccoli\")\n", " values(\"Tue\",\"Celery\")\n", " values(\"Thu\",\"Lettuce\")\n", " values(\"Fri\",\"Spinach\");\n", " select *\n", " from sql.l_data2;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Toutefois, remarquez que ce n'est qu'un _lable_. le nom de la première colonne de la table l_data2 reste toujours `Key`. Donc si nous voudrions faire des jointures avec d'autres tables, il faut faire attention de bien spécifier le nom de la colonne et nom le _label_" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "voici la preuve:" ] }, { "cell_type": "code", "execution_count": 28, "metadata": { "slideshow": { "slide_type": "fragment" } }, "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", "
étiquette de clonne détaillé
Mon
Tue
Thu
Fri
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " select Key \n", " from sql.l_data2;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Un autre exemple de création de la table `r_data` que nous utiliserons plus tard." ] }, { "cell_type": "code", "execution_count": 67, "metadata": { "slideshow": { "slide_type": "fragment" } }, "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", "
KeyFruits
MonApples
WedDates
ThuCherries
SatBananas
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 67, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " create table sql.r_data \n", " (Key char(12), Fruits char(12));\n", " insert into sql.r_data\n", " values(\"Mon\",\"Apples\")\n", " values(\"Wed\",\"Dates\")\n", " values(\"Thu\",\"Cherries\")\n", " values(\"Sat\",\"Bananas\");\n", " select *\n", " from sql.r_data;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Modification des tables" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Ajouter des nouvelles observations (lignes) dans une table\n", "\n", "### avec `SET`\n", "\n", "Des fois, il est très commode de créer une copie vide d'une table que nous avons déjà afin de la modifier à sa guise ou pour d'autres besoins particuliers.\n", "\n", "Créons une table semblable à la table `sql.countries`. Mais d'abord, regardons son contenu." ] }, { "cell_type": "code", "execution_count": 37, "metadata": { "slideshow": { "slide_type": "fragment" } }, "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", "
NameCapitalPopulationAreaContinentUNDate
AfghanistanKabul17070323251825Asia1946
AlbaniaTirane340740011100Europe1955
AlgeriaAlgiers28171132919595Africa1962
AndorraAndorra la Vella64634200Europe1993
AngolaLuanda9901050481300Africa1976
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", " select *\n", " from sql.countries;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Nous créons une table avec la clause `create table`" ] }, { "cell_type": "code", "execution_count": 40, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "

\n", "\n", "
415  ods listing close;ods html5 file=stdout options(bitmap_mode='inline') device=png; ods graphics on / outputfmt=png;
NOTE: Writing HTML5 Body file: STDOUT
416
417 proc sql outobs=5;
418 create table sql.copie_countries
419 like sql.countries;
NOTE: Table SQL.COPIE_COUNTRIES created, with 0 rows and 6 columns.
420 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

421 ods html5 close;ods listing;

422
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 40, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql outobs=5;\n", " create table sql.copie_countries\n", " like sql.countries;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Regardons ce que cette table contient;" ] }, { "cell_type": "code", "execution_count": 41, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 41, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " select Name, Population from sql.copie_countries;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Évidemment, elle est vide, car nous avons utilisé la clause `like` à la ligne 4 qui veut seulement dire de prendre le format" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "\n", "## Inserer des nouvelles lignes avec la clause `Select`" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "\n", "\n", "\n", "Maintenant supposons que nous voulons insérer dans notre nouvelle table `copie_countries` les données des pays qui ont une population plus grande ou égale à 130000000 provenant de la table `countries`" ] }, { "cell_type": "code", "execution_count": 45, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "

\n", "\n", "
460  ods listing close;ods html5 file=stdout options(bitmap_mode='inline') device=png; ods graphics on / outputfmt=png;
NOTE: Writing HTML5 Body file: STDOUT
461
462 proc sql ;
463 insert into sql.copie_countries
464 select * from sql.countries
465 where population ge 130000000;
NOTE: 6 rows were inserted into SQL.COPIE_COUNTRIES.

466 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

467 ods html5 close;ods listing;

468
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 45, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql ;\n", " insert into sql.copie_countries\n", " select * from sql.countries\n", " where population ge 130000000;\n", "quit; " ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Affichons le résultat;" ] }, { "cell_type": "code", "execution_count": 47, "metadata": { "slideshow": { "slide_type": "fragment" } }, "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", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
NameCapitalPopulationAreaContinentUNDate
BrazilBrasilia1.6031E83286500South America1945
ChinaBeijing1.2022E93696100Asia1945
IndiaNew Delhi9.2901E81222600Asia1945
IndonesiaJakarta2.0239E8741100Asia1950
RussiaMoscow1.5109E86592800Europe1945
United StatesWashington2.6329E83787318North America1945
BrazilBrasilia1.6031E83286500South America1945
ChinaBeijing1.2022E93696100Asia1945
IndiaNew Delhi9.2901E81222600Asia1945
IndonesiaJakarta2.0239E8741100Asia1950
RussiaMoscow1.5109E86592800Europe1945
United StatesWashington2.6329E83787318North America1945
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 47, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " select * from sql.copie_countries;\n", "quit; " ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Affichons seulement le nom du pays, sa capitale et la population sous le format souhaité." ] }, { "cell_type": "code", "execution_count": 52, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

World's Largest Countries

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
NameCapitalPopulation
BrazilBrasilia160,310,357
ChinaBeijing1,202,215,077
IndiaNew Delhi929,009,120
IndonesiaJakarta202,393,859
RussiaMoscow151,089,979
United StatesWashington263,294,808
BrazilBrasilia160,310,357
ChinaBeijing1,202,215,077
IndiaNew Delhi929,009,120
IndonesiaJakarta202,393,859
RussiaMoscow151,089,979
United StatesWashington263,294,808
BangladeshDhaka126,391,060
JapanTokyo126,352,003
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 52, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " select name format=$20., \n", " capital format=$15.,\n", " population format=comma15.0\n", " from sql.copie_countries;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "skip" } }, "source": [ "___" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "## Insérer des nouvelles lignes avec la clause `SET`" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "Maintenant, regardons comment insérer de nouvelles observations ou de nouvelles lignes entrées manuellement. On peut faire ceci avec la clause `Set`." ] }, { "cell_type": "code", "execution_count": 48, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", " \n", " \n", " \n", "\n", "\n", "

\n", "\n", "
486  ods listing close;ods html5 file=stdout options(bitmap_mode='inline') device=png; ods graphics on / outputfmt=png;
NOTE: Writing HTML5 Body file: STDOUT
487
488 proc sql;
489 insert into sql.copie_countries
490 set name='Bangladesh',
491 capital='Dhaka',
492 population=126391060
493 set name='Japan',
494 capital='Tokyo',
495 population=126352003;
NOTE: 2 rows were inserted into SQL.COPIE_COUNTRIES.

496 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

497 ods html5 close;ods listing;

498
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 48, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " insert into sql.copie_countries\n", " set name='Bangladesh',\n", " capital='Dhaka',\n", " population=126391060\n", " set name='Japan',\n", " capital='Tokyo',\n", " population=126352003;\n", "quit; " ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "Regardons ce que ça donne:" ] }, { "cell_type": "code", "execution_count": 50, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

World's Largest Countries

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
NameCapitalPopulation
BrazilBrasilia160,310,357
ChinaBeijing1,202,215,077
IndiaNew Delhi929,009,120
IndonesiaJakarta202,393,859
RussiaMoscow151,089,979
United StatesWashington263,294,808
BrazilBrasilia160,310,357
ChinaBeijing1,202,215,077
IndiaNew Delhi929,009,120
IndonesiaJakarta202,393,859
RussiaMoscow151,089,979
United StatesWashington263,294,808
BangladeshDhaka126,391,060
JapanTokyo126,352,003
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 50, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql;\n", " select name format=$20., \n", " capital format=$15.,\n", " population format=comma15.0\n", " from sql.copie_countries;\n", "quit;" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Nous remarquons que les deux nouvelles observations `Bangladesh` et `Japan` sont maintenant dans notre table `copie_countries` \n", "___" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "slide" } }, "source": [ "# Aficchage avec SELECT from (select )" ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "fragment" } }, "source": [ "reprenons l'exemple où nous avons affiché la production et les réserves du pétrole par pays via une jointure de deux tables (oilprod et oilrsrvs)" ] }, { "cell_type": "code", "execution_count": 62, "metadata": { "slideshow": { "slide_type": "subslide" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

World's Largest Countries

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
PaysProductionRéserves
Saudi Arabia9,000,000260,000,000,000
United States of America8,000,00030,000,000,000
Iran4,000,00090,000,000,000
Norway3,500,00011,000,000,000
Mexico3,400,00050,000,000,000
China3,000,00025,000,000,000
United Kingdom3,000,0004,500,000,000
Venezuela3,000,00065,000,000,000
Canada2,500,0007,000,000,000
Kuwait2,500,00095,000,000,000
United Arab Emirates2,000,000100,000,000
Nigeria2,000,00016,000,000,000
Indonesia1,500,0005,000,000,000
Libya1,500,00030,000,000,000
Algeria1,400,0009,200,000,000
Egypt900,0004,000,000,000
Iraq600,000110,000,000,000
\n", "
\n", "
\n", "\n", "\n" ], "text/plain": [ "" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "proc sql ;\n", " select p.country 'Pays', barrelsperday 'Production', barrels 'Réserves'\n", " from sql.oilprod p, sql.oilrsrvs r\n", " where p.country = r.country\n", " order by barrelsperday desc;\n", "quit; " ] }, { "cell_type": "markdown", "metadata": { "slideshow": { "slide_type": "subslide" } }, "source": [ "Supposons qu'à partir du tableau précédent, nous voulons avoir seulement les pays avec une production de 3000000 barils par jour." ] }, { "cell_type": "code", "execution_count": 59, "metadata": { "slideshow": { "slide_type": "fragment" } }, "outputs": [ { "data": { "text/html": [ "\n", "\n", "\n", "\n", "\n", "SAS Output\n", "\n", "\n", "\n", "
\n", "
\n", "

World's Largest Countries

\n", "
\n", "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
PaysProductionRéserves
China3,000,00025,000,000,000
Iran4,000,00090,000,000,000
Mexico3,400,00050,000,000,000
Norway3,500,00011,000,000,000
Saudi Arabia