PROC SQL Partie_2

Contenu

PROC SQL Partie_2#

Segmentation dans une variable#

Avec proc SQL, il est très facile de créer des catégories dans une table. Contrairement au data step, nous ne sommes plus obligés de conditionneur avec des if. Il suffit d’inclure toutes les conditions dans la clause case.

Dans l’exemple suivant, nous allons diviser nos assurés en trois groupes d’âge;
1. age $<$ 40 : EncoreJeune
2. age $\leq$40 $<$ 60 : Pas Vieux
3. age $>$ 40 : Commence Vieux
Nous allons aussi afficher seulement la somme des coûts de sinistre
Finalement, pour montrer qu’on peut ajouter une variable qui est basée sur une variable nouvellement calculée dans la même requête, nous allons calculer le coût de sinistre avec un supplément de 2% sur 5 ans (une sorte d’inflation comme exemple)

libname libSql "data";
proc sql outobs=5;
    title 'Créer des catégories';
    select numeropol,
        langue,
        type_prof,
        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,
            case 
                when calculated age < 40 then 'EncoreJeune'
                when calculated age ge 40 and calculated age < 60 then 'Pas Vieux'
                when calculated age ge 60 then 'Commence Vieux'
            end as categorieAge
        from libSql.equipe_1
quit; 

SAS Output

Créer des catégories


numeropol	langue	type_prof	type_territoire	coutTotal	coutTotalInfla	age	categorieAge
1	F	Autre	Semi-urbain	.	.	50	Pas Vieux
1	F	Autre	Semi-urbain	.	.	50	Pas Vieux
1	F	Autre	Semi-urbain	$243.86	$269.24	50	Pas Vieux
5	F	Technicien	Rural	.	.	40	Pas Vieux
5	F	Technicien	Rural	.	.	40	Pas Vieux

Triage des données#

Avec la procédure PROC SQL, le triage de données se fait facilement avec ORDER BY

Trier par une colonne#

proc sql outobs=5;
    title 'données triées par le nombre de sinistre';
    select numeropol,alimentation ,nbsin
        from libSql.equipe_1
        order by nbsin;
quit; 

SAS Output

données triées par le nombre de sinistre


numeropol	alimentation	nbsin
43808	Végétarien	0
81812	Végétarien	0
22456	Carnivore	0
54340	Carnivore	0
31956	Végétalien	0

Trier par de multiples colonnes#

proc sql outobs=5;
   title 'données triées par nombre de sinistre et alimentation';
    select numeropol,alimentation ,nbsin
        from libSql.equipe_1
        order by nbsin,alimentation;
quit; 

SAS Output

données triées par nombre de sinistre et alimentation


numeropol	alimentation	nbsin
85246	Carnivore	0
88270	Carnivore	0
82031	Carnivore	0
88695	Carnivore	0
81449	Carnivore	0

Triage en ordre croissant ou décroissant#

proc sql outobs=5;
    title 'Table génerale contenant toutes les informations';
    select numeropol,alimentation ,nbsin
        from libSql.equipe_1
        order by nbsin  desc,alimentation;
quit; 

SAS Output

Table génerale contenant toutes les informations


numeropol	alimentation	nbsin
18824	Carnivore	5
42279	Végétarien	5
37423	Carnivore	4
70558	Carnivore	4
55240	Carnivore	4

L’ordre peut être dans n’importe quel ordre

proc sql outobs=5;
    select numeropol,alimentation ,nbsin
        from libSql.equipe_1
        order by nbsin  desc,numeropol asc;
quit; 
title;

SAS Output

Table génerale contenant toutes les informations


numeropol	alimentation	nbsin
18824	Carnivore	5
42279	Végétarien	5
390	Carnivore	4
7754	Carnivore	4
8747	Végétarien	4

Trier sur des colonnes avec valeur calculée#

On peut aussi trier sur des variables calculées! c’est formidable! réfléchissez bien lorsqu’on programmait jadis en vba. Calculons l’âge de nos assurés et faisons un tri sur cette variable calculée.

proc sql outobs=5;
    select numeropol,alimentation ,nbsin  ,      
        2005 - year(date_naissance) as age           
        from libSql.equipe_1
        order by nbsin desc, age asc;
quit; 

SAS Output


numeropol	alimentation	nbsin	age
18824	Carnivore	5	36
42279	Végétarien	5	50
43163	Végétalien	4	30
68935	Végétarien	4	31
73779	Végétarien	4	34

Au lieu de spécifier le nom de la colonne sur laquelle nous voulons appliquer un tri, nous pouvons le faire en indiquant le numéro de la colonne (position de la colonne)

proc sql outobs=5;
    title 'Créer des catégories';
    select numeropol,alimentation ,nbsin  ,      
        2005 - year(date_naissance) as age           
        from libSql.equipe_1
        order by 3 desc, 4 asc;
quit; 

SAS Output

Créer des catégories


numeropol	alimentation	nbsin	age
18824	Carnivore	5	36
42279	Végétarien	5	50
43163	Végétalien	4	30
68935	Végétarien	4	31
73779	Végétarien	4	34

Trier sur des colonnes non sélectionnées#

Il est aussi possible de faire un triage sur des colonnes non sélectionnées, par exemple ici, nous sélectionnons seulement numeropol, alimentation ,nbsin , age mais nous appliquons un tri sur type_territoire.

proc sql outobs=5;
    select numeropol,alimentation ,nbsin  ,      
        2005 - year(date_naissance) as age           
        from libSql.equipe_1
        order by type_territoire desc;
quit; 
title;

SAS Output

Créer des catégories


numeropol	alimentation	nbsin	age
68406	Carnivore	1	84
73804	Carnivore	0	36
67988	Végétalien	0	30
49293	Végétalien	1	40
49083	Carnivore	0	43

La sélection d’observation#

Sélectionner des observations selon leurs valeurs#

Le tableau suivant présente un sommaire des comparateurs logiques. (Lien vers la documentation SAS)

Comparison Operators
Symbol	Mnemonic Equivalent	Definition	Example
=	EQ	equal to	`a=3`
^=	NE	not equal to1	`a ne 3`
¬=	NE	not equal to
~=	NE	not equal to
>	GT	greater than	`num>5`
<	LT	less than	`num<8`
>=	GE	greater than or equal to2	`sales>=300`
<=	LE	less than or equal to3	`sales<=100`
	IN	equal to one of a list	`num in (3, 4, 5)`
1 The symbol that you use for NE depends on your personal computer.
2 The symbol => is also accepted for compatibility with previous releases of SAS. It is not supported in WHERE clauses or in PROC SQL.
3 The symbol =< is also accepted for compatibility with previous releases of SAS. It is not supported in WHERE clauses or in PROC SQL.

Par exemple, sélectionnons les assurés ayant plus que 3 sinistres

proc sql outobs=5;
    title 'Créer des catégories';
    select numeropol,alimentation ,nbsin  ,      
        2005 - year(date_naissance) as age           
        from libSql.equipe_1
        where nbsin gt 3
        order by 1 desc;
quit; 

SAS Output

Créer des catégories


numeropol	alimentation	nbsin	age
80730	Végétalien	4	35
74259	Végétarien	4	47
73779	Végétarien	4	34
72873	Carnivore	4	47
72524	Végétarien	4	39

Sélection des observations selon plusieurs conditions#

Sélectionnons des assurés âgés de moins de 40 ans et ayant plus trois sinistres. N’oublions pas que la colonne age est une variable calculée!

proc sql outobs=5;
    title 'Créer des catégories';
    select numeropol,alimentation ,nbsin  ,      
        2005 - year(date_naissance) as age           
        from libSql.equipe_1
        where nbsin gt 3 and calculated age lt 40
        order by 1 desc;
quit; 

SAS Output

Créer des catégories


numeropol	alimentation	nbsin	age
80730	Végétalien	4	35
73779	Végétarien	4	34
72524	Végétarien	4	39
70256	Végétarien	4	37
68935	Végétarien	4	31

D’autres opérateurs conditionnels#

Avec l’opérateur l’opération WHERE, nous pouvons utiliser plusieurs opérateurs;

ANY

specifies that at least one of a set of values obtained from a subquery must satisfy a given condition

where Population> any (select Population from sql.countries)

ALL

specifies that all of the values obtained from a subquery must satisfy a given condition

where Population > all (select Population from sql.countries)

BETWEEN-AND

tests for values within an inclusive range

where Population between 1000000 and 5000000

CONTAINS

tests for values that contain a specified string

where Continent contains 'America';

EXISTS

tests for the existence of a set of values obtained from a subquery

where exists (select * from sql.oilprod);

IN

tests for values that match one of a list of values

where Name in ('Africa', 'Asia');

IS NULL or IS MISSING

tests for missing values

where Population is missing;

LIKE

tests for values that match a specified pattern (footnote 1)

where Continent like 'A%';

=*

tests for values that sound like a specified value

where Name=* 'Tiland';

Exemple d’opérateur `IN`#

proc sql outobs=5;
    title "titre";
    select 
        numeropol, 
        langue,
        type_prof,
        alimentation
        from libSql.equipe_1
        where type_prof in ('Informaticien','Technicien')
        order by numeropol;
quit; 

SAS Output

titre


numeropol	langue	type_prof	alimentation
5	F	Technicien	Carnivore
5	F	Technicien	Végétarien
5	F	Technicien	Carnivore
5	F	Technicien	Carnivore
5	F	Technicien	Végétarien

Exemple d’opérateur `LIKE`#

proc sql outobs=5;
    title "titre";
    select 
        numeropol, 
        langue,
        type_prof,
        alimentation
        from libSql.equipe_1
        where type_prof like "%cien"
        order by numeropol;
quit; 

SAS Output

titre


numeropol	langue	type_prof	alimentation
5	F	Technicien	Carnivore
5	F	Technicien	Végétarien
5	F	Technicien	Carnivore
5	F	Technicien	Carnivore
5	F	Technicien	Végétarien

Sommaire des données#

Il est possible d’appliquer plusieurs fonctions aggregate sur des colonnes, comme des moyennes, des MIN/Max…etc. La liste complète est dans le tableau ci-dessous.

Function	Definition
AVG, MEAN	mean or average of values
COUNT, FREQ, N	number of nonmissing values
CSS	corrected sum of squares
CV	coefficient of variation (percent)
MAX	largest value
MIN	smallest value
NMISS	number of missing values
PRT	probability of a greater absolute value of Student’s t
RANGE	range of values
STD	standard deviation
STDERR	standard error of the mean
SUM	sum of values
SUMWGT	sum of the WEIGHT variable values (footnote 1)
VAR	variance

Exemple: Nous voudrions avoir un tableau avec contenant le numeropol, debut_pol, fin_pol et la moyenne des coûts de sinistres 1 à 7. Toutefois, nous voulons seulement les moyennes supérieures à 1000$

proc sql outobs=5;
    title "Coûts moyens des sinistres";
    select 
        numeropol, 
        debut_pol, 
        fin_pol, 
        mean(cout1, cout2, cout3,cout4,cout5,cout6,cout7) as coutMoyen format=dollar15.2
        from libSql.equipe_1
        where calculated coutMoyen gt 1000;
quit;

SAS Output

Coûts moyens des sinistres


numeropol	debut_pol	fin_pol	coutMoyen
16	18/10/1998	17/10/1999	$1,156.14
29	16/02/1996	15/02/1997	$5,088.87
29	01/06/1999	15/02/2000	$2,255.66
29	16/02/2001	15/02/2002	$24,046.30
65	07/02/1995	06/02/1996	$3,719.03

PROC SQL Partie_2

Contenu

PROC SQL Partie_2#

Segmentation dans une variable#

Triage des données#

Trier par une colonne#

Trier par de multiples colonnes#

Triage en ordre croissant ou décroissant#

Trier sur des colonnes avec valeur calculée#

Trier sur des colonnes non sélectionnées#

La sélection d’observation#

Sélectionner des observations selon leurs valeurs#

Sélection des observations selon plusieurs conditions#

D’autres opérateurs conditionnels#

Exemple d’opérateur IN#

Exemple d’opérateur LIKE#

Sommaire des données#

Exemple d’opérateur `IN`#

Exemple d’opérateur `LIKE`#