Search
PROC SQL Partie_1

Introduction

Nous avons vu qu'il est possible de manipuler les ensembles de données avec la procédure DATA. Toutefois, cette procédure ne peut pas résoudre tous les problèmes rencontrés lors de la manipulation des données.

Il existe une autre procédure fort intéressante appelée PROC SQL qui est inspiré du langage de requête structurée SQL (Structured Query Language). Ce langage informatique normaliser permet de faire une multitude de requêtes sur les bases de données, on peut par exemple, créer, chercher, modifier ou supprimer les bases de données relationnelles.

Cette logique (relationnelle) permet d’établir une séparation plus claire entre la représentation logique des données et leur organisation physique. Le modèle de données relationnel a été défini en 1970 par l'informaticien britannique d'IBM Edgar F. Codd, et publié dans son article A Relational Model of Data for Large Shared Data Banks.

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Le fonctionnement des bases de données relationnelles est basé sur l'Algèbre relationnelle (théorie des ensembles).

Rappel: Soient $R(A)$ et $R(B)$ deux relations ayant pour ensembles d'attributs respectifs $A$ et $B$:

  • Union: $R\cup S=\{t:t\in R\ ou\ t\in S\}\,$
  • Intersection: $R\cap S=\{t:t\in R\ et\ t\in S\}\,$
  • Différence: $R-S=\{t:t\in R\ et\ t\not \in S\}\,$
  • Produit cartésien : $R\times S=\{(r,s):r\in R\ et\ s\in S\}\,$

Quelques définitions

Que peut-on faire avec PROC SQL?

  • Générer des raports
  • Générer des sommaires statistiques
  • Extraires des données à partir d'une ou plusieurs tables/vues
  • Créer des tables/vues
  • Mettre à jours et modifier des tables/vues
  • et plus encore!

Certains termes utilisés en SAS et leur correspondance en SQL:

Avec SASl'équivalent en SQL
Data set Table
Observation Ligne
variableColonne

Pour afficher les donnés avec SQL, il faut toujours commencer par un SELECT afin d'afficher les données désirées.

SELECT column(s)
    FROM table-name | view-name
        WHERE expression
        GROUP BY column(s)
        HAVING expression
        ORDER BY column(s);
QUIT;

Le but de l'instruction SELECT est de nommer la ou les colonnes qui apparaîtront sur le résultat et l'ordre dans lequel il apparaîtra. Cette instruction est similaire à l'instruction VAR dans un PROC PRINT

La clause FROM nomme l'ensemble de données à partir duquel l'information sera extraite de (similaire à l'instruction SET).

Un avantage de SQL est que les nouvelles variables peuvent être créées dynamiquement sur l'instruction SELECT, caractéristique qu'on ne retrouve pas dans une procédure SAS:

libname libSql "data";

proc sql outobs=10; /* on détermine le nombre  d'observation à afficher avec outobs=10*/
title "les données d'assurance";  /* le titre à afficher */
    select * /* avec * nous indiquon que bous voulons afficher toutes les colonnes*/
    from libSql.assu_4 /* (OBS=10)*/; /* on pourrait mettre aussi (OBS=10) afin 
    d'indiquer le nombre d'observations à afficher*/ 
quit;
title; 
SAS Connection established. Subprocess id is 16117

SAS Output

les données d'assurance

numeropol type_prof type_territoire sexe nbsin CoutSinTot
67942 Technicien Semi-urbain M 0 .
52484 Ingénieur Semi-urbain M 0 .
27817 Ingénieur Semi-urbain M 0 .
32986 Technicien Semi-urbain F 4 $15,282.23
45769 Technicien Semi-urbain M 0 .
22914 Technicien Semi-urbain F 0 .
60045 Technicien Semi-urbain M 0 .
13590 Infirmière Urbain M 2 $3,174.52
76422 Infirmière Urbain F 0 .
15847 Technicien Semi-urbain F 0 .

Nous pouvons afficher le contenu d'une quelconque table avec un SELECT *. ou l'* indique qu'on veut sélectionner toutes les variables.

Ou on peut afficher certaines colonnes seulement:

proc sql outobs=5;
    select type_prof, 
            type_territoire
    from libSql.assu_4 
quit;
title; 
SAS Output
type_prof type_territoire
Technicien Semi-urbain
Ingénieur Semi-urbain
Ingénieur Semi-urbain
Technicien Semi-urbain
Technicien Semi-urbain

On peut donner un nouvel alias à une colonne donnée avec l’argument as;

proc sql outobs=5;
    select type_prof as type_profession, 
            type_territoire as territoire
    from libSql.assu_4 
quit;
title; 
SAS Output
type_profession territoire
Technicien Semi-urbain
Ingénieur Semi-urbain
Ingénieur Semi-urbain
Technicien Semi-urbain
Technicien Semi-urbain

Regardons comment il est facile d'avoir les coûts de sinistres totaux par territoire:

proc sql; 
title "Coûts de sinistres groupés par territoire";  
    select type_territoire, 
            sum(CoutSinTot) as SommeParTerri format=DOLLAR15.2
    from libSql.assu_4
    group by type_territoire
    order by type_territoire;
quit;
title; 
SAS Output

Coûts de sinistres groupés par territoire

type_territoire SommeParTerri
Rural $1,824,413.02
Semi-urbain $16,980,081.70
Urbain $16,450,812.96

Maintenant, regardons les coûts de sinistres groupés par territoire et sexe;

proc sql; 
title "Coûts de sinistres groupés par territoire et sexe de l'assuré";  
    select type_territoire, sexe,
            sum(CoutSinTot) as SommeParTerri format=DOLLAR15.2
    from libSql.assu_4
    group by type_territoire, sexe;
quit;
title; 
SAS Output

Coûts de sinistres groupés par territoire et sexe de l'assuré

type_territoire sexe SommeParTerri
Rural F $1,054,940.63
Rural M $769,472.39
Semi-urbain F $6,361,838.58
Semi-urbain M $10,618,243.12
Urbain F $6,499,434.84
Urbain M $9,951,378.12

Sommaire ses données

Where

Supposons qu'on cherche les assurées ayant eu plus que 300 000$ en réclamation;

proc sql;
title "les assurés ayant eu une réclamation totale plus grande que 300 000$"; 
    select *
    from libSql.assu_4
    where CoutSinTot > 300000
    order by CoutSinTot desc;
quit;
title; 
SAS Output

les assurés ayant eu une réclamation totale plus grande que 300 000$

numeropol type_prof type_territoire sexe nbsin CoutSinTot
677 Ingénieur Urbain M 1 $508,207.17
28694 Actuaire Semi-urbain M 1 $439,876.57
46599 Technicien Semi-urbain F 1 $394,231.80
17455 Technicien Semi-urbain M 1 $386,423.51
78691 Technicien Urbain M 1 $376,290.63
69019 Infirmière Semi-urbain M 3 $329,942.11

ou seulement les numéros de police des assurés ayant eu quatre sinistres et plus

proc sql outobs=10;
title "les numéros de police d'assurance avec plus de 4 sinistres"; 
    select numeropol,nbsin 
    from libSql.assu_4
    where nbsin ge 4;
quit;
title; 
SAS Output

les numéros de police d'assurance avec plus de 4 sinistres

numeropol nbsin
32986 4
37283 4
54321 4
9719 7
38482 4
23513 4
6668 5
27496 4
71973 5
78650 5

GROUP BY

La clause GROUP BY nous permet de briser les résultats de la requête en sous-ensembles de lignes. D'ailleurs nous l'avons fait dans l'exemple où nous cherchions les coûts de sinistres groupés par territoire.

Regardons un autre exemple où nous cherchons les coûts de sinistres groupés par type de profession des assurés

proc sql; 
title "Coûts de sinistres groupés par profession";  
    select type_prof as profession, 
            sum(CoutSinTot) as SommeParTerri format=DOLLAR15.2
    from libSql.assu_4
    group by type_prof
    order by type_prof;
quit;
title; 
SAS Output

Coûts de sinistres groupés par profession

profession SommeParTerri
Actuaire $1,809,775.39
Autre $877,677.13
Avocat $2,802,637.32
Hockeyeur $1,231,994.47
Infirmière $4,555,766.72
Informaticien $2,606,574.75
Ingénieur $3,183,580.62
Médecin $1,017,608.62
Professeur $415,699.64
Technicien $16,753,993.03

HAVING

La clause HAVING fonctionne avec la clause GROUP BY pour restreindre les groupes dans les résultats d'une requête en fonction d'une condition donnée. PROC SQL applique la condition HAVING après regroupement des données et application de fonctions agrégées. Par exemple, la requête suivante restreint les groupes à inclure uniquement les professions de'actuaire, infirmière et hockeyeur.

proc sql; 
title "Coûts de sinistres groupés par profession";  
    select type_prof as profession, 
            sum(CoutSinTot) as SommeParTerri format=DOLLAR15.2
    from libSql.assu_4
    group by type_prof
    having type_prof in("Actuaire", "Infirmière", "Hockeyeur")
    order by type_prof;
quit;
title; 
SAS Output

Coûts de sinistres groupés par profession

profession SommeParTerri
Actuaire $1,809,775.39
Hockeyeur $1,231,994.47
Infirmière $4,555,766.72

Les doublons

Avec proc sql, il est très facile de se débarrasser des doublons avec select distinct. Si l'on observe les données suivantes, on voit bien que nous avons des doublons.

proc sql outobs=5;
    select type_prof, 
            type_territoire
    from libSql.assu_4 
quit;
title; 
SAS Output
type_prof type_territoire
Technicien Semi-urbain
Ingénieur Semi-urbain
Ingénieur Semi-urbain
Technicien Semi-urbain
Technicien Semi-urbain
proc sql outobs=10;
    select distinct type_prof, 
            type_territoire
    from libSql.assu_4
    order by type_prof, type_territoire;
quit;
title; 
SAS Output
type_prof type_territoire
Actuaire Rural
Actuaire Semi-urbain
Actuaire Urbain
Autre Rural
Autre Semi-urbain
Autre Urbain
Avocat Rural
Avocat Semi-urbain
Avocat Urbain
Hockeyeur Rural

Création de colonnes

On peut ajouter du texte dans une nouvelle colonne. Créons une nouvelle colonne qui calcule le coût total des réclamations plus taxes (9%)

proc sql outobs=10; 
    select *, CoutSinTot * 1.09 as CoutSinTot_Taxes format=DOLLAR15.2
    from libSql.assu_4
quit;
title; 
SAS Output
numeropol type_prof type_territoire sexe nbsin CoutSinTot CoutSinTot_Taxes
67942 Technicien Semi-urbain M 0 . .
52484 Ingénieur Semi-urbain M 0 . .
27817 Ingénieur Semi-urbain M 0 . .
32986 Technicien Semi-urbain F 4 $15,282.23 $16,657.63
45769 Technicien Semi-urbain M 0 . .
22914 Technicien Semi-urbain F 0 . .
60045 Technicien Semi-urbain M 0 . .
13590 Infirmière Urbain M 2 $3,174.52 $3,460.22
76422 Infirmière Urbain F 0 . .
15847 Technicien Semi-urbain F 0 . .

Nous pouvons créer des noms de colonnes qui s'affichent avec l'option LABEL="du TEXT avec des caractères spéciaux" ou les effacer complètement en écrivant un caractère spécial LABEL=#

proc sql outobs=5; 
    select CoutSinTot label ="montant des sinistres avant les taxes", CoutSinTot * 1.09 as CoutSinTot_Taxes format=DOLLAR15.2
    label ="montant des sinistres qui inclut la taxe de 9%"
    from libSql.assu_4
quit;
title; 
SAS Output
montant des sinistres avant les taxes montant des sinistres qui inclut la taxe de 9%
. .
. .
. .
$15,282.23 $16,657.63
. .
. .
. .
$3,174.52 $3,460.22
. .
. .
proc sql outobs=5; 
    select CoutSinTot label ="#", CoutSinTot * 1.09 as CoutSinTot_Taxes format=DOLLAR15.2
    label ="montant des sinistres qui inclut la taxe de 9%"
    from libSql.assu_4
quit;
title; 
SAS Output
  montant des sinistres qui inclut la taxe de 9%
. .
. .
. .
$15,282.23 $16,657.63
. .

Créer une colonne calculée

Comme nous l'avons déjà fait, cherchons dans notre table d'assurance la somme des sinistres encourus. Mais cette fois, utilisons la table originale equipe_1

proc sql outobs=5;
    select numeropol,
        langue,
        type_prof,
        alimentation,
        type_territoire, 
        sum(cout1, cout2, cout3,cout4,cout5,cout6,cout7)
        from libSql.equipe_1
quit; 
SAS Output
numeropol langue type_prof alimentation type_territoire  
1 F Autre Végétarien Semi-urbain .
1 F Autre Végétarien Semi-urbain .
1 F Autre Carnivore Semi-urbain 243.8571
5 F Technicien Végétarien Rural .
5 F Technicien Végétarien Rural .

Assigner un nom à une colonne

On voit bien que dans la table précédente, la variable de la nouvelle colonne calculée ne possède aucune étiquette label (le nom de la colonne qui s'affiche)

proc sql outobs=5;
    select * 
    from libSql.tempo
quit;
SAS Output
numeropol langue type_prof alimentation type_territoire _TEMA001
1 F Autre Végétarien Semi-urbain .
1 F Autre Végétarien Semi-urbain .
1 F Autre Carnivore Semi-urbain 243.8571
5 F Technicien Végétarien Rural .
5 F Technicien Végétarien Rural .
proc contents data=libSql.tempo;
run;
SAS Output

The CONTENTS Procedure

Data Set Name LIBSQL.TEMPO Observations 5
Member Type DATA Variables 6
Engine V9 Indexes 0
Created 01/30/2018 14:55:26 Observation Length 96
Last Modified 01/30/2018 14:55:26 Deleted Observations 0
Protection   Compressed NO
Data Set Type   Sorted NO
Label      
Data Representation SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64    
Encoding utf-8 Unicode (UTF-8)    
Engine/Host Dependent Information
Data Set Page Size 65536
Number of Data Set Pages 1
First Data Page 1
Max Obs per Page 681
Obs in First Data Page 5
Number of Data Set Repairs 0
Filename /mnt/hgfs/myfolders/data/tempo.sas7bdat
Release Created 9.0401M5
Host Created Linux
Inode Number 277
Access Permission rwxrwx---
Owner Name root
File Size 128KB
File Size (bytes) 131072
Alphabetic List of Variables and Attributes
# Variable Type Len Format
6 _TEMA001 Num 8  
4 alimentation Char 25 $25.
2 langue Char 1  
1 numeropol Num 8  
3 type_prof Char 25 $25.
5 type_territoire Char 25 $25.

En effet, SAS assigne un nom de variable temporaire à la nouvelle variable calculée si aucun nom n'a été défini par l'utilisateur.

Donnons CoutTotal comme nom de variable à cette table;

proc sql outobs=5;
    select numeropol,
        langue,
        type_prof,
        alimentation,
        type_territoire, 
        sum(cout1, cout2, cout3,cout4,cout5,cout6,cout7) as CoutTotal format=DOLLAR15.2
        from libSql.equipe_1
quit; 
SAS Output
numeropol langue type_prof alimentation type_territoire CoutTotal
1 F Autre Végétarien Semi-urbain .
1 F Autre Végétarien Semi-urbain .
1 F Autre Carnivore Semi-urbain $243.86
5 F Technicien Végétarien Rural .
5 F Technicien Végétarien Rural .

Calcul sur une colonne calculée;

Nous pouvons effectuer un calcul sur une colonne existante et créer une nouvelle colonne avec ce calcul. Nous pouvons aussi créer une nouvelle colonne à partir de colonnes calculées. Pour ce faire, il faut utiliser le mot clé CALCULATED.

Par exemple, nous calculons la somme des coûts de sinistres encourus dans une colonne coutTotal et on multiplie ce résultat par un taux d'inflation de $2\%$ sur 5 ans.

proc sql outobs=5;
    select numeropol,
        langue,
        type_prof,
        alimentation,
        type_territoire, 
        sum(cout1, cout2, cout3,cout4,cout5,cout6,cout7)as coutTotal format=DOLLAR15.2, 
        calculated CoutTotal*(1.02**5) as coutTotalInfla format=DOLLAR15.2, 
        2005 - year(date_naissance) as age                
        from libSql.equipe_1
quit; 
SAS Output
numeropol langue type_prof alimentation type_territoire coutTotal coutTotalInfla age
1 F Autre Végétarien Semi-urbain . . 50
1 F Autre Végétarien Semi-urbain . . 50
1 F Autre Carnivore Semi-urbain $243.86 $269.24 50
5 F Technicien Végétarien Rural . . 40
5 F Technicien Végétarien Rural . . 40