Lire des classeurs Excel 2007 avec SAS 9.1

DATAMETRIC

Ce document présente une méthode d'accès aux fichiers Excel 2007 (extensions xlsx) grâce au module SAS ACCESS TO ODBC.

Commentez ♪**

- Titre : Lire des classeurs Excel 2007 avec SAS 9.1

- Auteur : DATAMETRIC

- Parution : 18 mars 2010

- Mise à jour : 18 mars 2010

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2010 Stéphane COLAS. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Lire un fichier Excel depuis SAS

- III. Comment générer tous les fichiers DSN correspondant aux fichiers Excel d'un répertoire

- III-A. Récupération de la liste des fichiers Excel

- III-B. Génération des fichiers DSN pour chacun de ces fichiers Excel

- IV. Présentation du programme complet

- V. Remerciements

- VI. À propos de l'auteur

I - Introduction

Les classeurs Excel 2007 portent l'extension .xlsx. Ces fichiers étant des fichiers XML zippés, leur format bloque les anciennes méthodes de lecture telles que celles du logiciel SAS (le module SAS ACCESS TO PC FILES de SAS 9.1 ne peut être utilisé pour accéder à ces fichiers). Il a fallu attendre SAS 9.2 et la mise à jour de ce module pour pouvoir créer de nouveau des LIBNAME sur des fichiers Excel 2007.

Toutefois, pour ceux qui n'ont pas migré vers la version 9.2 de SAS, vous trouverez ci-après une méthode de lecture des fichiers Excel 2007 basée sur le module SAS ACCESS TO ODBC. Cette méthode est automatisée dans la seconde partie de la présentation pour lire l'ensemble des onglets et classeurs Excel d'un répertoire.

Excel 2007 permet des enregistrements portant l'extension .xls, format Excel 97-2003. Sous ce format, vous pourrez continuer à utiliser le module ACCESS TO PC FILES pour la lecture de ces fichiers.

II - Lire un fichier Excel depuis SAS

Cette première partie du document décrit pas à pas comment créer un fichier DSN permettant de se connecter à un classeur EXCEL. Dans un deuxième temps, ce fichier sera exploité dans une étape SAS afin de lire le contenu.

Si vous voulez lire un fichier quelconque à partir d'une source ODBC, vous devez le déclarer dans le gestionnaire des sources de données ODBC. Cela permet de lier le moteur ODBC au fichier xlsx auquel vous souhaitez accéder via SAS ou un autre logiciel qui ne reconnaît pas ce nouveau format Excel.

Pour ce faire, dans le « panneau de configuration » ouvrez « outils d'administration » puis « Source de données (ODBC) ».

1. 2. 3.

Dans la fenêtre qui s'ouvre, nous allons créer un fichier avec les paramètres ODBC que nous exploiterons plus tard.

L'onglet « source de données fichier » permet de créer un fichier texte avec l'extension DSN (Data Source Name) qui contiendra les paramètres de conversion et le nom du fichier source.

Le bouton AJOUTER permet de créer un fichier DSN « AccesClasseur1.dsn » lié à un classeur xlsx. Ainsi vous pouvez valider son contenu dans votre environnement au regard des captures d'écrans de ce document. :

1. 2.

3. 4.

La version 12 du pilote ODBC est importante. Mettez à jour le système au besoin.

Cliquez sur OK dans la fenêtre principale et, avec NotePad, ouvrez le fichier « AccesClasseur1.dsn » que vous avez créé. Dans notre exemple il est stocké dans « D:\data\dsn »

Voici le contenu :

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=1
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 12.0
DriverId=1046
DefaultDir=D:\DATA\BIRD\Excel
DBQ=D:\DATA\BIRD\Excel\Classeur1.xlsx

Le fichier de paramètres contient bien le pilote (ligne 1) pour les formats xlsx, le nom et la localisation du fichier Excel à lire (deux dernières lignes).

Enfin, il est possible de créer un LIBNAME pointant vers « Classeur1.xlsx » et de l'exploiter par des étapes DATA :

libname c1 odbc noprompt="filedsn=D:\Data\Dsn\AccesClasseur1.dsn;";
Data divers;
set c1.'feuil1$'n;
run;

III - Comment générer tous les fichiers DSN correspondant aux fichiers Excel d'un répertoire

À partir d'un seul fichier Excel, cette démonstration a permis de découvrir la structure des fichiers EXCEL et de tester une connexion avec SAS. Nous avons pu analyser le fichier DSN et comprendre que seules deux lignes sont à modifier pour industrialiser ce processus.

Désormais, cette procédure est à automatiser. Cela permettrait de créer un petit programme pour pointer sur l'ensemble des fichiers Excel 2007 d'un répertoire donné, par exemple.

Dans le langage SAS, ce genre de manipulation se gère grâce aux macros.

Les éléments à prendre en compte sont :

- la récupération de la liste des fichiers Excel dans le répertoire de stockage et enregistrement de chacun des noms dans une macro-variable dédiée ;

- la génération des fichiers DSN pour chacun de ces fichiers Excel ;

- la création des LIBNAME ODBC pour chacun de ces fichiers Excel.

III-A - Récupération de la liste des fichiers Excel

Dans cette première étape, nous paramétrons le macro-programme avec le nom du répertoire stockant les fichiers sources :

%let EcoSource = D:\DATA\BIRD\Excel ;

L'utilisation classique d'un FILENAME et de son moteur PIPE permet de lire et de récupérer le résultat de la commande MS-DOS nommée DIR.

Dans notre cas, nous n'avons pas besoin de sauvegarder le résultat dans une table ou un fichier. Nous récupérerons le nom de chaque fichier EXCEL dans autant de macros-variables.

filename dircont pipe "dir/b &EcoSource.\*.xlsx ";
data _null_ ;
infile dircont length=ll ;
input @1 fixls $varying200. ll;
call symputx ( catt("fic",_n_) , scan(fixls,1,'.') );
call symputx ( "nbfic" , _n_ );
run;

Une étape DATA _null_ permet de lire directement le contenu du PIPE et de stocker sans intermédiaire chaque nom de fichier EXCEL (sans l'extension) dans une macro-variable distincte.

Pour la démonstration, voici ce que contiendrait une table DIRCONT si nous remplacions l'étape DATA _null_ par une étape DATA Dircont.

(Contenu de la table DirCont avec la liste des fichiers EXCEL contenus dans le répertoire &EcoSource)

III-B - Génération des fichiers DSN pour chacun de ces fichiers Excel

L'étape DATA nous a permis de charger, en mémoire, le nom de chaque fichier EXCEL dans des macros-variables.

Au cours de cette étape, nous allons modifier le fichier DSN que nous avions généré auparavant pour en faire un modèle dont les dernières lignes sont modifiées : nous paramétrons le chemin du répertoire ainsi que le nom potentiel du fichier EXCEL avec des macros-variables.

Tout d'abord, nous optons pour la stratégie suivante : les fichiers DSN n'ont pas besoin d'être conservés au-delà de la session SAS actuelle. De ce fait, les fichiers sont créés dans la WORK.

Le chemin de la WORK est ainsi récupéré dans la macro-variable &LOCWRK pour indiquer à SAS le répertoire de sauvegarde des fichiers DSN.

data _null_;
a=pathname("work");
call symputx ( "locwrk" , a);
run;

Le fichier DSN créé dans le premier exemple est récupéré et exploité dans une étape DATA qui associe les paramètres ODBC nécessaires à la lecture des fichiers EXCEL.

Nous travaillerons donc avec :

- &EcoSource qui référence le répertoire de stockage des fichiers Excel ;

- &locwrk, pour créer les fichiers DSN dans le répertoire de la WORK ;

- &Fic1, &fic2 qui contiennent le nom de chaque fichier Excel ;

- &nbfic qui contient le nombre de fichiers Excel au total.

Les paramètres modifiés dans le fichier DSN sont situés en fin d'étape :

filename foo "&locwrk.\&&fic&i...dsn";
data _null_;
file foo ;
put "[ODBC]"/
"DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"/
"UID=admin"/
"UserCommitSync=Yes"/
"Threads=3"/
"SafeTransactions=0"/
"ReadOnly=1"/
"PageTimeout=5"/
"MaxScanRows=8"/
"MaxBufferSize=2048"/
"FIL=excel 12.0"/
"DriverId=1046"/
"DefaultDir=&EcoSource"/
"DBQ=&EcoSource.\&&fic&i...xlsx"
;
run;

Par conséquent, pour automatiser la création de tous les fichiers DSN (correspondant à chaque fichier EXCEL du répertoire) nous utiliserons une boucle macro %DO exploitant &nbfic

%do i = 1 %to &nbfic ;
filename foo "&locwrk.\&&fic&i...dsn";
data _null_;
file foo ;
put "[ODBC]"/
"DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"/
"UID=admin"/
"UserCommitSync=Yes"/
"Threads=3"/
"SafeTransactions=0"/
"ReadOnly=1"/
"PageTimeout=5"/
"MaxScanRows=8"/
"MaxBufferSize=2048"/
"FIL=excel 12.0"/
"DriverId=1046"/
"DefaultDir=&EcoSource"/
"DBQ=&EcoSource.\&&fic&i...xlsx"
;
run;
%end&nbsp;;

En parcourant l'explorateur Windows, nous vérifions d'une part que les fichiers DSN sont créés dans la WORK.

(Vue de la WORK depuis l'explorateur Windows)

D'autre part, nous vérifions avec CLASSEUR1.DSN , que les macros-variables en fin d'étape ont bien été remplacées par les valeurs nécessaires.

[ODBC]
DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
ReadOnly=1
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=excel 12.0
DriverId=1046
DefaultDir=D:\Data\Dsn\
DBQ=D:\Data\Dsn\Classeur1.xlsx

IV - Présentation du programme complet

Vous trouverez ci-après le programme complet. Les étapes précédemment décomposées sont regroupées et exploitables de suite. Le point d'entrée reste bien entendu la définition du chemin du répertoire dans lequel se trouvent les fichiers EXCEL.

%Macro VoirEcoFichier;
%let EcoSource = D:\DATA\Dsn ;

filename dircont pipe "dir/b &EcoSource.\*.xlsx ";

data _null_ ;
infile dircont length=ll ;
input @1 fixls $varying200. ll;
call symputx ( catt("fic",_n_) , scan(fixls,1,'.') );
call symputx ( "nbfic" , _n_ );
run;

data _null_;
a=pathname("work");
call symputx ( "locwrk" , a);
run;

%do i = 1 %to &nbfic ;
filename foo "&locwrk.\&&fic&i...dsn";
data _null_;
file foo ;
put
"[ODBC]"/
"DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"/
"UID=admin"/ "UserCommitSync=Yes"/ "Threads=3"/ "SafeTransactions=0"/
"ReadOnly=1"/ "PageTimeout=5"/ "MaxScanRows=8"/
"MaxBufferSize=2048"/
"FIL=excel 12.0"/
"DriverId=1046"/
"DefaultDir=&EcoSource"/
"DBQ=&EcoSource.\&&fic&i...xlsx"
;
run;
libname l&i odbc noprompt="filedsn=&locwrk.\&&fic&i...dsn;";
%end;
/* Insérez des étapes DATA */

%mend VoirEcoFichier;

%VoirEcoFichier;

V - Remerciements

J'adresse ici tous mes remerciements à l'équipe de rédaction de « developpez.com » pour le temps qu'ils ont bien voulu passer à la correction et à l'amélioration de cet article. Je remercie en particulier Antoun, Pixelomilcouleurs et jacques_jean pour leurs relectures patientes.

VI - À propos de l'auteur

Stéphane COLAS dirige DATAMETRIC, société qu'il a fondée en 2002 pour proposer un ensemble de services autour du CRM analytique et dans le choix et la mise en œuvre d'environnements BI. D'abord statisticien, puis consultant et maintenant expert et enseignant à Paris-Dauphine, il mêle désormais des actions de conseil en architecture, de développement de base de données marketing et de formations personnalisées auprès des équipes d'analystes.