PROC SQL Partie_1

Contenu

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 SAS	l'équivalent en SQL
Data set	Table
Observation	Ligne
variable	Colonne

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