Search
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.

Aggregate Functions

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

T

Student's t value for testing the hypothesis that the population mean is zero

USS

uncorrected sum of squares

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