Lire des classeurs Excel 2007 avec SAS 9.1

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.

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;

À 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 :

 

Sélectionnez

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

 

Sélectionnez

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▲

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

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;

%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 ;

[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

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;

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.

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.