Procédure SQL de SAS 1.2 La base de données · 3Procédure SQL de SAS On peut utiliser une clause...

1 Procdure SQL de SAS

Procdure SQL de SAS

RsumCette vignette dcrit lusage de la procdure SQL qui permet linter-rogation et la gestion de tables SAS laide du langage de requtestandard de bases de donnes relationnelles.

Plan des tuteuriels : Prise en main Gestion des donnes Graphiques Macros-commandes Bases de donnesLes procdures du module SAS/STAT sont tudies dans les cours destatistique affrents.

1 Introduction

1.1 Langage SQL

Le langage SQL (Structured Query Language) est un langage dinterroga-tion de bases de donnes standardis commun la plupart des logiciels de basede donnes. La procdure sql tudie dans cette vignette en constitue une im-plmentation dans la version 9.3 de SAS. Cette procdure permet dextraire,corriger et mettre jour des donnes dans une table SAS, souvent plus rapide-ment que par une tape data.

Le terme de table dsigne toujours une table SAS, correspondant un sto-ckage de donnes propre ce logiciel. On utilise galement deux nouveauxtypes dobjets : les vues et les index. Une vue est le stockage dune interroga-tion ou ensemble de requtes : elle contient la description ou dfinition dunetable virtuelle. Une vue est donc une interrogation laquelle on donne un nom,pour son usage ultrieur dans une autre procdure SAS. Le principal intrt dedfinir une vue est le gain despace mmoire. Un index est un systme de poin-teurs permettant dans certains cas daccder plus rapidement aux informationscontenues dans une table SAS.

1.2 La base de donnes

Cration

Excuter le programme tuteur-sql.sas du rpertoire wikistat/data quicre quatre tables SAS contenant des informations sur les ventes dun grossisteen articles de sport. Consulter les tables afin de retrouver les contenus dcritsci-dessous.

Description

Les informations disponibles sont les suivantes :

1. Dans la table produit, on trouve dans cet ordre, les nom, cot de pro-duction et cot figurant dans le catalogue de vente des diffrents articlesvendus par le grossiste.

2. Dans la table client, on trouve les noms des magasins revendeurs quise fournissent chez le grossiste, le numro de client de chaque magasinet sa ville dimplantation. Par exemple, il y a trois magasins Flots Bleux,leurs numros sont 3,8 et 11, et ils sont situs Hendaye (un magasin) etLa Torche (deux magasins).

3. Dans la table employe, on trouve le numro de chaque employe dugrossiste, son nom, son anciennet, sa ville, sa fonction et le numro delemploy qui le supervise. Par exemple, Jeanne (numro 201, manager)est supervise par Filmon (101, directeur). Jeanne supervise Albert, Ju-lien, Monique et Alain.

4. Dans la table facture, on trouve, pour un certain nombre de ventesralises par le grossiste, le numro de facture, le nom du magasin-client,son numro, le numro de lemploy du grossiste qui a tabli la facture,le matriel vendu, la quantit vendue, et le prix de facturation unitaire(ventuellement diffrent du prix catalogue, car il peut prendre en comptedes remises ou un cot de livraison).

1.3 Syntaxe de la procdure

Commandes

proc sql ;alter table dclaration de modification ;

http://wikistat.frhttp://wikistat.fr/pdf/st-tutor1-sas-init.pdfhttp://wikistat.fr/pdf/st-tutor2-sas-gestion.pdfhttp://wikistat.fr/pdf/st-tutor3-sas-graph.pdfhttp://wikistat.fr/pdf/st-tutor4-sas-macros.pdfhttp://wikistat.fr/pdf/st-tutor5-sas-sql.pdfhttp:://wikistat/data

2 Procdure SQL de SAS

create table dclaration de cration ;delete dcl-destruction ;describe dcl-description ;drop dcl-suppression ;insert dcl-insertion ;reset ;select dcl-slection ;update dcl-mise jour ;validate dcl-valuation ;

Remarques : il est inutile de rpter linstruction proc sql avant chaque dclaration,

sauf si lon excute une tape data ou si lon fait appel une autre proc-dure entre deux commandes de sql.

linstruction run nest pas ncessaire.

Options

inobs=n restreint le nombre dobservations traites (par exemple dans uneclause where) sur une table fournie en entre de la procdure.

outobs=n restreint le nombre dobservations traites (par exemple ins-res) dans une table retourne par la procdure.

feedback rappelle la dfinition des vues parentes lors de la descriptiondune vue (commande describe).

noprint pas ddition

1.4 Procdure sql vs. tape data

On veut connatre la taille moyenne 10 ans et par sexe des enfants dont lamre consommait entre 10 et 20 cigarettes par jour au moment de leur nais-sance. crire le programme permettant de calculer ces quantits laide desprocdures summary, sort et print ; puis comparer avec le programmesuivant utilisant la procdure sql.

proc sql;select sexenf , mean(tenf_10) as tmoy

from sasuser.statlab2where consm_n=10a20ciggroup by sexenforder by tmoy;

2 Linstruction SELECT

2.1 Slection

Excuter et commenter le code suivant :

proc sql;title "liste des prix et des produits";select prodlist, nomprod from produit;

On peut slectionner toutes les variables dune table en utilisant :

proc sql; select * from produit;

Dans lexemple suivant, on somme les annes danciennet des vendeurs,ville par ville. Un tri croissant est ensuite effectu. Notez la dfinition de laliastotannee par linstruction as. Notez galement que cet alias est utilis parlinstruction order by dans la mme procdure. Linstruction order bytotannee desc trierait par ordre dcroissant.

proc sql;select villeemp, sum(anneemp) as totannee

from employewhere titreemp="respven"group by villeemporder by totannee;

Il est possible dutiliser des expressions arithmtiques dans une clauseselect, pour raliser des calculs sur des colonnes numriques. Par exempletapez et commentez :

proc sql;select nomprod, prodlist, coutprod,

prodlist-coutprodfrom produit;

http://wikistat.fr

3 Procdure SQL de SAS

On peut utiliser une clause where dans linstruction select, pour garderou retirer des observations. Exemples de syntaxes :

where a>bwhere (a>b) and (c>d)where (a-b)>100where a between 5 and 15where city not in ("paris","toulouse")where a is null /*vrai si a est manquante*/where a like "c%"where a like "____e"

2.2 Extraction

Exercice 1 : Utilisez des clauses where pour afficher successivement les in-formations sur :

1. les employs ayant au moins 10 ans de service,

2. les employs qui ne vivent pas Mimisan, et qui ont plus de 10 ans deservice,

3. les employs dont le nom comence par la lettre s,

4. les employs ayant un nom de cinq lettres qui finit par un e,

5. les employs ayant travaill 1,5 ou 10 ans,

6. les employs dont le numro est compris entre 301 et 401,

7. les employs pour lesquels la variable patremp prsente une valeurmanquante, i.e. les employs nayant pas de responsable au dessus deuxdans la hirarchie de lentreprise.

Les instructions having et count(*) permettent de faire comme si onappliquait une clause where un groupe dobservations. Lexemple suivantpermet de mieux comprendre leur utilisation. Ce code opre limpression desseuls clients ayant deux magasins.

proc sql;title "Clients ayant deux magasins";select nomclient, numclient, villeclient

from clientgroup by nomclienthaving count(*)=2order by nomclient,2,3;

La commande group by dfinit le groupe qui sera valu par having.Seuls les groupes dont le having est valu comme vrai seront traits.

2.3 Jonction (jointure) de deux tables

Une requte peut porter sur plusieurs tables. Le code suivant afficheles prix de production, de catalogue et de facturation de chaque pro-duit vendu par le client de Mimisan. La runion de ces informationsncessite lappel des tables produit et facture. La clause whereproduct.prodname=invoice.prodname sert runir ces tables selonla variable commune prodname.

proc sql;title "Information sur les produits

vendus par Surf40";select numfact, produit.nomprod,coutprod,

prixfact,prodlist,nomclient,numclientfrom produit, facturewhere produit.nomprod=facture.nomprod

and nomclient="Surf40";

Notez quil faut faire prcder, dans linstruction select, le nom dunevariable apparaissant dans plus dune des tables jointes par un nom de table.Lunion des deux tables est ici faite par from.

Exercice 2 : Adapter le code prcdent joignant les tables client etfacture pour visualiser les produits vendus Mimisan.

2.4 Jonction dune table elle-mme

Il peut tre aussi intressant de joindre une table elle-mme pour rendreplus lisible certaines informations. Par exemple, on souhaite afficher, partirde la table employe, le nom de son responsable ct du nom de chacun des

http://wikistat.fr

4 Procdure SQL de SAS

employs. Dans ce cas, il faut dabord dupliquer la table, ce qui peut se faire di-rectement dans linstruction from de la manire suivante : from employeemp1, employe emp2. Dans ce cas, emp1 et emp2 sont des alias (ounoms temporaires) de tables.

Linstruction where prcise ensuite la clause :

where emp1.patremps=emp2.numemp.

Exercice 3 : Ecrivez le code permettant dafficher le nom et la fonction de sonresponsable cot du nom et de la fonction de chacun des employs.

2.5 Jonction de plusieurs tables

Il est bien sur possible de joindre plus de deux tables.

Exercice 4 : Ecrire une requte permettant dafficher les produits vendus parSamuel des magasins de La Torche (vous pourrez crer des alias de tablespour allger votre code).

Une requte peut contenir une sous-requte, qui restitue une ou plusieursvaleurs ensuite utilises par la requte qui la contient. Par exemple :

proc sql;select numemp,nomclient,numclient,nomprod,numfact

from facturewhere numemp in (216,314)order by 1,2,3,4;

renvoie des informations sur les ventes ralises par Alain (identifiant 216)et Georges (314).

Supposons que lon veuille afficher ces informations mais que lon neconnaisse pas les identifiants des vendeurs. Dans le code suivant :

proc sql;select numemp,nomclient,numclient,nomprod,numfact

from facturewhere numemp in(select numemp from employe

where nomemp in ("Alain","Georges"))order by 1,2,3,4;

une sous-requte est value en premier et slectionne les identifiantsdAlain et Georges dans la table employe. Ces numros sont ensuite utili-ss dans la clause where de la requte principale pour slectionner les lignesde facture.

Exercice 5 : Ecrivez un code utilisant une sous-requte et la clause not indans la clause where pour afficher les produits proposs par le fournisseurmais non-vendus dans les magasins.

Linstruction validate permet de vrifier si la syntaxe dune instructionselect est correcte, sans avoir lexcuter. Par exemple :

proc sql;validateselect numemp, nomemp

from employewhere numemp > 200 and numemp

5 Procdure SQL de SAS

where ...order by ... desc;/* affichage de la table cre */select * from ...

La commande order by ... desc va permettre ici de trier les employspar ordre dcroissant danciennet.

Exercice 7 : Crez une table nouvprix (pour nouveaux prix), copie de latable produit.

3.2 Modification de tables

Diverses instructions permettent de modifier les donnes et la structuredune table ; alter permet dajouter, supprimer des colonnes, modifier leursattributs ; update permet dajouter/modifier des valeurs une colonne.

Executez et commentez :

proc sql;alter table nouvprixadd prix2011 num format=euro.;select * from nouvprix;

A noter la cration de la variable prix2011 : la commande utilise permetde prciser que cette dernire est de type numrique et exprime en euros. Lesprix 2011 sont obtenus en ajoutant 20% au prix catalogue :

proc sql;update nouvprixset prix2011=prodlist*1.2;select * from nouvprix;

La cration de nouvprix, lajout et le remplissage de prix2011 auraient putre faits en une seule tape. On peut aussi ne modifier que certaines valeursdune colonne. Par exemple,

Exercice 8 : Adapter le code de lexercice prcdent pour crire une procdureSQL qui applique en 2008 une augmentation de 20% au prix catalogue desproduits cotant moins de 240 euros, et qui laisse inchang le prix des produitscotant plus de 240 euros.

Le programme obtenu peut tre crit de manire plus courte, avec loptioncase ... end

proc sql;update tableset prix2011=prodlist *case when prodlist

6 Procdure SQL de SAS

Notons quil est aussi possible de supprimer une table :

proc sql;drop table nomtable;

3.3 Utilisation des vues

Lexemple suivant illustre la cration dune vue SQL, cest--dire une re-qute sauvegarde, laquelle on affecte un nom par create view nomas. Une vue peut ensuite tre appele, comme un table, par une tape DATA,une procdure ou une instruction select. Cependant, contrairement unetable, une vue ne contient pas de donnes, et ne peut donc tre mise jour enutilisant update, alte, insert, delete. Excutez le programme suivantqui liste les factures dont le montant dpasse 1500 euros, et affiche le nom delemploy ayant fait la vente :

proc sql;create view ventimp asselect numfact, nomclient, numclient,

nomemp, nomprod, quantfact, prixfactfrom facture as i, employe as ewhere i.numemp-e.numemp and

(quantfact*prixfact)>1500;select * from ventimp order by numfact;describe view ventimp;proc univariate;run;

Exercice 9 : Modifier le script prcdent pour crer une vue gdeqte qui se-lectionne dans la table facture les factures portant sur plus de 25 articles.Executez ensuite (et commentez) :

proc sql;describe view gdeqte;

proc univariate data=ventimp;var quantfact prixfact;

run;

proc sql;drop view gdeqte;

Annexe 1 : syntaxe des commandes

La commande create

Elle permet de crer des tables, des vues, ou des index, partir dautrestables ou dautres vues.

Cration dune table

Syntaxe

create table nom-table as query-expression ;

create table nom-table like nom-table ;

create table nom-table (def-col ) ;

La premire syntaxe est utilise pour stocker les rsultats dune interrogation.Cest une faon de crer des tables temporaires. La deuxime syntaxe est utili-se pour crer une table ayant les mmes noms de variables et mmes attributsquune autre table. La troisime syntaxe est utilise quand on veut crer unetable dont les colonnes ne sont pas prsentes dans des tables dj existantes.Les syntaxes 2 et 3 crent des tables vides, quil faut ensuite remplir avec lacommande insert. Cration dune table SAS permanente dans la librairiesql

libname sql sql;proc sql;create table sql.statlab like sasuser.statlab2;create table sql.statlab2 as

select sexenf, gsenf, tenf_n,penf_n, tenf_10, penf_10from sasuser.statlab2where (consm_n=nonfum);

http://wikistat.fr

7 Procdure SQL de SAS

Cration dune vue

Syntaxecreate view nom-vue as query-exp ;Une vue tant une interrogation stocke et ne contenant pas de donnes, on nepeut utiliser les instructions suivantes quand on se rfre une vue : insert,delete, alter, update.

Cration dune vue partir dune table

create view labv2 asselect sexenf, gsenf, tenf_n, penf_n,tenf_10, penf_10

from sql.statlab2where (sexenf=fille);

proc print data=labv2; run;

Cration dun index

Un index stocke la fois les valeurs des colonnes dune table, et un systme dedirections qui permet daccder aux lignes de cette table partir des valeurs delindex. Lutilisation de lindex lors dinterrogations ou autres instructions dela procdure est dtermine par le systme. Lindex est automatiquement mis jour quand on modifie la table laquelle il est associ. Il permet damliorerla performance de certaines commandes, par exemple la comparaison dunecolonne indexe une valeur constante laide de lexpression where.

Syntaxecreate index nom-index on nom-table ;Le mot-cl unique garantit que chaque valeur de la colonne indexe estunique. Ceci peut tre utile quand on manipule des variables telles que le nu-mro de scurit sociale. Cration de lindex simple gse associ au groupesanguin

proc sql;create index gse on sql.statlab2 (gsenf);

Cration de lindex composite consm associ deux variables

proc sql;create index consm on sql.statlab2 (consm_n,consm_10);

La commande alter

Elle permet dajouter ou de supprimer des colonnes dans une table SAS, ouden modifier les attributs (longueur, label, format).

Syntaxe

alter table nom-table< add def-col >< modify def-col >< drop nom-col > ;

Modification dune table existante

alter table sql.statlab2add gender char(6);

La commande delete

Elle permet de supprimer des lignes dans une table.

Syntaxe

delete from nom-table < where sql-exp > ;

Suppression des lignes dune table

delete from sql.statlab2 where gsenf=A+;

La commande describe

Elle donne la dfinition dune vue, et des vues parentes si loptionfeedback est spcifie.

Syntaxe

describe view nom-vue ;

http://wikistat.fr

8 Procdure SQL de SAS

Description dune vue

describe view labv2;

La commande drop

Elle permet de dtruire indiffremment une table ou une vue.

Syntaxe

drop table nom-table < , nom-table > ;

drop view nom-vue < , nom-vue > ;

La commande insert

Elle permet dajouter des lignes une table.

Syntaxe

insert into nom-table < ( nom-col < , nom-col > >)

values ( value < , value >) ;

Il existe deux autres manires dutiliser la commande insert (voir laide enligne).

Insertion de lignes dans une table

insert into sql.statlab2values (fille,AB,0,0,0,0,d)values (garcon,AB,10,10,10,10,e);

La commande select

Elle permet de slectionner des colonnes dans une table, et dafficher lesrsultats dans la fentre output.

Syntaxe

select liste dobjets from liste < where sql-exp > ;

La commande update

Elle permet de modifier les valeurs de certaines observations pour des co-lonnes dune table existante.

Syntaxe

update nom-table set nom-col=sql-exp < where sql-exp > ;

Modification dune table

update sql.statlab2set gender=sexenf;

La commande validate

Elle permet dvaluer la syntaxe dune interrogation sans lexcuter, et re-tourne un message dans la fentre log.

Syntaxe

validate query-exp ;

Cette commande est essentiellement utile dans des applications utilisant desmacro-variables. validate retourne alors une valeur indiquant si linterro-gation est valide grce la macro-variable SQLRC (SQL Return Code).

Annexe 2 : Solutions

/* Exercice 1*/proc sql;select * from employewhere anneemp>9 order by nomemp;proc sql;select * from employewhere (villeemp not in ("Mimisan"))and (anneemp>9);proc sql;select * from employewhere nomemp like "S%";

http://wikistat.fr

9 Procdure SQL de SAS

proc sql;select * from employewhere nomemp like "____e";proc sql;select * from employewhere anneemp in (1,5,10);proc sql;select * from employewhere numemp between 301 and 401;proc sql;select * from employewhere patremp is null;/*----------------------------------------*//* Exercice 2*/proc sql;select facture.nomclient, facture.numclient,

nomprod, prixfactfrom client, facturewhere facture.nomclient=client.nomclient

and facture.numclient=client.numclientand villeclient="Mimisan";

/*----------------------------------------*//* Exercice 3*/proc sql;title "Renseignements employs";select emp1.nomemp, emp1.titreemp,

emp2.nomemp, emp2.titreempfrom employe emp1,employe emp2where emp1.patremp=emp2.numemp;/*----------------------------------------*//* Exercice 4*/proc sql;title "Performances Samuel";select facture.*, client.*from employe, facture, client

/* mix des tables par numclient */

where facture.numclient=client.numclient/* choix de la ville */and client.villeclient="LaTorche"/* employe la Torche */and employe.villeemp=client.villeclient/* nomms Samuel */and employe.nomemp="Samuel";

/*----------------------------------------*//* Exercice 5*/proc sql;title "invendus";select produit.nomprod, prodlistfrom produitwhere produit.nomprod not in (select nomprod

from facture);/*----------------------------------------*//* Exercice 6*/proc sql;title "Employes anciens";create table anciens asselect nomemp, titreemp, anneempfrom employewhere anneemp>5order by anneemp desc;select * from anciens;/*----------------------------------------*//* Exercice 7*/proc sql;title "Nouveaux prix";create table nouvprix asselect *from produit;proc print data=nouvprix;run;/*----------------------------------------*//* Exercice 8*/

http://wikistat.fr

10 Procdure SQL de SAS

/* Cration de la colonne prix2007 */proc sql;alter table nouvprixadd prix2007 num format=euro.;select * from nouvprix;proc sql;update nouvprixset prix2007 = prodlist*1.2;select * from nouvprix;/* Dbut de lexercice */proc sql;alter table nouvprixadd prix2008 num format=euro.;select * from nouvprix;proc sql;update nouvprixset prix2008=prodlist whereprix2007>240;select * from nouvprix;proc sql;update nouvprixset prix2008=prodlist*1.2 whereprix20071500;select * from ventimp order by numfact;describe view ventimp;proc sql;

create view gdeqte asselect numfact, nomclient, numclient,nomemp, nomprod, quantfact, prixfactfrom facture as i, employe as ewhere i.numemp=e.numemp andquantfact>25;select * from ventimp order bynumfact;describe view ventimp;proc sql;describe view gdeqte;proc univariate data=ventimp;var quantfact prixfact;run;proc sql;drop view gdeqte;

http://wikistat.fr

IntroductionLangage SQLLa base de donnesSyntaxe de la procdureProcdure sql vs. tape data

L'instruction SELECTSlectionExtractionJonction (jointure) de deux tablesJonction d'une table elle-mmeJonction de plusieurs tables

Tables et vuesCration de tablesModification de tablesUtilisation des vues