Import et export des fichiers Microsoft dans l'environnement SAS 9.2 sur Windows 64-bits

Import et export des fichiers Microsoft dans l'environnement SAS 9.2 sur Windows 64-bits

| DATAMETRIC | datametric |

Identification des problèmes et solutions envisageables pour l'import et l'export des fichiers Microsoft Excel ou Access dans l'environnement SAS 9.2 sur Windows 64-bits ♪

- Titre : Import et export des fichiers Microsoft dans l'environnement SAS 9.2 sur Windows 64-bits

- Auteurs : DATAMETRIC, datametric

- Parution : 11 octobre 2011

- Mise à jour : 11 octobre 2011

- Licence : Copyright ® 2011 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

- 1. Introduction

- 2. Accès aux fichiers sur la plate-forme SAS 9.2 64-bits

- 2-A. La nouvelle solution SAS PC FILE SERVER

- 2-B. Les moteurs de connexion envisageables

- 2-C. Connexion avec le logiciel Excel

- 2-C-1. Utilisation de la PROC IMPORT

- 2-C-2. Utilisation du LIBNAME

- 2-C-3. Utilisation de la PROC EXPORT

- 2-D. Connexion avec le logiciel Access

- 2-D-1. Utilisation de la PROC IMPORT

- 2-D-2. Utilisation du LIBNAME

- 2-D-3. Utilisation de la PROC EXPORT

- 3. Erreurs couramment rencontrées avec le module PCFILE SERVER

- 3-A. Mauvaise initialisation du service PCFILE SERVER

- 3-A-1. Erreur sur les procédures

- 3-A-2. Erreur sur les LIBNAME

- 3-B. Impossibilité d'atteindre un fichier sur le réseau

- 3-C. Impossibilité de remplacer un fichier existant

- 4. Les solutions à apporter pour stabiliser ou réussir ses échanges entre SAS et les produits Office

- 4-A. Installer une version récente de SAS 9.2

- 4-B. Installer une version d'Excel compatible

- 4-C. Installer les drivers spécifiques Microsoft Access Database Engine 2010

- 4-D. Donner les pleins droits aux utilisateurs sur une clef de registre

- 4-E. Utiliser SAS Enterprise Guide

- 5. Conclusion

- 6. Liens

- 7. Remerciements

- 8. À propos de l'auteur

1 - Introduction

Avec l'arrivée des OS Windows 64-bits, de nombreux problèmes d'importation et d'exportation de fichiers Office sont apparus sur SAS. En effet, lorsque l'on souhaite importer des fichiers Excel ou Access dans les environnements Windows 64-bits (Windows 2008 64-bits, Windows 2008 R2, Windows Vista 64 et Windows 2003 64-bit) ce message est susceptible d'apparaître lorsque l'on utilise un code développé initialement sur les plates-formes 32-bits :

ERROR: DBMS type EXCEL (ACCESS) not valid for import.

Le problème est récurrent, car la stratégie de la plupart des sociétés étant d'installer la version 64-bits de SAS sur des serveurs ou des PC, l'application n'est alors plus compatible avec le moteur de connexion DBMS=EXCEL qui est un moteur 32-bits.

Si cette stratégie est conservée, il est nécessaire d'utiliser les moteurs de connexion 64-bits tels qu'ils sont décrits dans les exemples ci-dessous et de connaître les incompatibilités entre les commandes SAS, les moteurs et les types de fichiers Office. Autrement, il est possible d'installer la version 32-bits de SAS sur la plate-forme 64-bits et d'utiliser les connecteurs habituels.

Ce dossier technique présente les différents changements qui doivent être pris en compte pour importer et exporter les fichiers Office. Il a été préparé à partir du retour d'expérience des missions de conseil réalisé par l'auteur chez ses clients et des discussions avec certains experts chez l'éditeur et le support. Bien entendu, l'auteur présente par avance ses excuses si d'autres cas réels n'ont pas été appréhendés dans ce dossier alors qu'ils auraient pu aider le lecteur.

La première partie de cet article présente les possibilités d'accès aux fichiers en utilisant le client traditionnel de SAS sur la plate-forme SAS 9.2 64-bits.

La seconde partie détaille les erreurs courantes rencontrées lors de l'utilisation des nouvelles syntaxes fournies détaillées auparavant.

La dernière partie donne des solutions pour stabiliser le système d'échange entre SAS et Office et présente la façon dont SAS Enterprise Guide gère l'import et l'export de données.

2 - Accès aux fichiers sur la plate-forme SAS 9.2 64-bits

Dans cette première partie, nous allons nous intéresser aux deux modules d'accès aux données Office (Excel et Access) présents dans SAS : le module SAS ACCESS TO PC FILES et le module SAS PC FILE SERVER.

Tout d'abord, une présentation du nouveau module SAS PC FILE SERVER sera donnée. Les moteurs inclus dans ce module permettent de gérer les accès aux données sur les plates-formes UNIX et Windows 64-bits.

Ensuite, nous étudierons grâce à des exemples concrets le moteur de connexion à des données Excel 2003 et 2007, puis sur le même mode travail, nous présenterons le moteur de connexion à des données Access 2003 et 2007.

Les exemples détaillés de cette partie concerneront tout aussi bien l'importation de données que l'exportation de celles-ci, au travers des procédures et du LIBNAME.

Les données utilisées n'ont rien de spécifique à ce dossier. L'ensemble des tests a été réalisé grâce aux tables de la bibliothèque SASHELP. Ainsi, les données de la table SASHELP.CLASS ont été copiées dans un fichier Excel 2003 (nommé D_Excel_2003.xls) puis 2007 (nommé D_Excel_2007.xlsx) et ensuite dans un fichier Access 2003 (nommé D_Access_2003.mdb) et 2007 (nommé D_Access_2007.accdb).

2-A - La nouvelle solution SAS PC FILE SERVER

PC FILE SERVER est une application qui gère les requêtes de lecture et d'écriture de fichiers Office. Elle est fournie en standard pour des plates-formes UNIX et Windows 64-bits dans les dépôts SAS.

Elle propose deux moteurs de connexion, EXCELCS et ACCESSCS, permettant de répondre à des besoins précis que nous verrons plus loin. Installée en tant que service à partir des plans dans le dépôt SAS, elle utilise le Microsoft Access Connectivity Engine et les drivers ODBC version 12 pour Microsoft Excel et Microsoft Access.

Par extension, cette application permet d'accéder également à des SGBD ayant des connecteurs ODBC, mais ce point ne sera pas abordé dans cet article.

2-B - Les moteurs de connexion envisageables

Avec l'ajout de cette solution, il est possible de gérer les différentes versions des fichiers Office sur la plate-forme 64-bits de Windows avec différents moteurs :

- la source de données MS-Excel amène l'utilisation des moteurs SAS suivants : « XLS », « EXCELCS », « PCFILES » ;

- la source de données MS-Access amène l'utilisation des moteurs SAS suivants : « ACCESSCS », « PCFILES ».

Mais pourquoi avoir autant de moteurs ? Se valent-ils en performance ? Ce dossier amènera les réponses à ces questions.

2-C - Connexion avec le logiciel Excel

Sur une plate-forme 64-bits, il existe trois moteurs de connexion qui possèdent leurs propres contraintes.

Le moteur « XLS »

- Ne peut pas communiquer avec les fichiers Microsoft Office 2007 files (XLSX, XLSM, et XLSB).

- Ne peut pas être utilisé avec un LIBNAME. Il ne peut être utilisé qu'au sein des PROC IMPORT et EXPORT.

- Reste le moteur le plus stable pour tout autre type de fichiers (i.e. hormis Excel 2007).

Le moteur « EXCELCS »

- Peut communiquer avec les fichiers Microsoft Office 2007 files (XLSX, XLSM, et XLSB).

- Ne peut pas être utilisé avec un LIBNAME. Il ne peut être utilisé qu'au sein des PROC IMPORT et EXPORT.

- Ne renomme pas les colonnes ayant des caractères spéciaux (il remplace ces caractères par des soulignements alors que le moteur XLS renomme complètement la variable en VARn).

- Peut créer des fichiers Excel 2007. Toutefois, s'il s'agit de créer un fichier au format Excel 2007 plutôt que de mettre à jour un fichier existant, il faut lui donner une extension XLSB (B pour Binaire).

Le moteur « PCFILES »

- Peut communiquer avec les fichiers Microsoft Office 2007 (XLSX, XLSM, et XLSB).

- Doit être utilisé avec un LIBNAME. Il ne peut pas être utilisé au sein des PROC IMPORT et EXPORT.

- L'utilisation de PCFILES est basée sur les moteurs (engine) Microsoft : Microsoft Jet Engine et Microsoft ACE engine. Par défaut le LIBNAME utilise MSENGINE= ACE.

Nous allons détailler ci-dessous quelques exemples d'importation et d'exportation à des données Excel 2003 (*.xls) et 2007 (*.xlsx) en utilisant SAS 9.2 64-bits. Nous allons comparer les résultats des moteurs du module SAS PC FILE SERVER avec les moteurs du module ACCESS to PC FILES (classiquement utilisé dans les versions 32-bits de SAS).

Pour cela, les procédures IMPORT et EXPORT seront présentées avec quelques options spécifiques, à savoir :

- GETNAMES : option permettant de récupérer les noms de colonnes dans la première ligne du fichier Excel ;

- SHEET : option permettant de spécifier une feuille particulière du classeur Excel, tant pour l'import que pour l'export ;

- MSENGINE : option permettant de changer le moteur de connexion fourni par Windows ;

- REPLACE : option nécessaire pour remplacer le fichier en sortie d'une procédure.

Ensuite, l'étape LIBNAME sera utilisée pour illustrer ce type de connexion.

2-C-1 - Utilisation de la PROC IMPORT

Fichier

Moteur et option

Code

Journal

Résultat

Excel 2003

EXCEL

proc IMPORT datafile=« D:\data\D_Excel.xls » dbms=Excel out=donnees replace;Run;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement. Connection Failed. See log for details.

KO

Excel 2003

EXCELCS

Proc IMPORT datafile=« C:\data\D_Excel_2003.xls » dbms=ExcelCS out=donnees replace;Run;

NOTE: WORK.DONNEES data set was successfully created. NOTE: The data set WORK.DONNEES has 14 observations and 3 variables.

OK

Excel 2003

EXCELCS +GETNAMES

Proc IMPORT datafile=« C:\data\D_Excel_2003.xls » dbms=ExcelCS out=donnees replace;getnames=yes;

ERROR: Statement or Option « GETNAMES » not valid for EXCELCS IMPORT.

KO

Excel 2003

XLS

proc IMPORT datafile=« C:\data\D_Excel_2003.xls » dbms=xls out=donnees replace;

NOTE: Variable Name Change. numéro de crédit -> VAR1 NOTE: Variable Name Change. Date d'octroi -> Date_d_octroi NOTE: The import data set has 14 observations and 3 variables. NOTE: WORK.DONNEES data set was successfully created.

OK

Excel 2003

XLS + GETNAMES

proc IMPORT datafile=« C:\data\D_Excel_2003.xls » dbms=xls out=donnees replace; getnames=yes;

NOTE: Variable Name Change. numéro de crédit -> VAR1 NOTE: Variable Name Change. Date d'octroi -> Date_d_octroi NOTE: The import data set has 14 observations and 3 variables. NOTE: WORK.DONNEES data set was successfully created.

OK

Excel 2007

EXCEL

PROC importProc IMPORT datafile=« D:\data\D_Excel_2007.xlsx » dbms=Excel out=donnees replace;

ERROR: DBMS type EXCEL not valid for import

KO

Excel 2007

EXCELCS

PROC importProc IMPORT datafile=« C:\data\D_Excel_2007.xlsx » dbms=ExcelCS out=donnees replace;

NOTE: WORK.DONNEES data set was successfully created. NOTE: The data set WORK.DONNEES has 14 observations and 3 variables.

OK

Excel 2007

EXCELCS +GETNAMES

PROC importProc IMPORT datafile=« C:\data\D_Excel_2007.xlsx » dbms=ExcelCS out=donnees replace;getnames=yes;

ERROR: Statement or Option « GETNAMES » not valid for EXCELCS IMPORT.

KO

Excel 2007

XLS

PROC importProc IMPORT datafile=« C:\data\D_Excel_2007.xlsx » dbms=xls out=donnees replace;

Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later Requested Input File Is Invalid ERROR: Import unsuccessful. See SAS Log for details.

KO

2-C-2 - Utilisation du LIBNAME

Fichier

Moteur

Code

Journal

Résultat

Excel 2003

EXCEL

LIBNAME xl2003 « C:\data\D_Excel_2003.xls » ;

Sans moteur de connexion spécifié, SAS prend par défaut le moteur « EXCEL ».

KO

Excel 2003

EXCELCS

LIBNAME xl2003 ExcelCS « C:\data\D_Excel_2003.xls » ;

ERROR: The EXCELCS engine cannot be found.

KO

Excel 2003

PCFILES

LIBNAME xl2003 PCFILES path=« C:\data\D_Excel_2003.xls »;

NOTE: Libref XL2003 was successfully assigned

OK

Excel 2003

PCFILES + ACE

LIBNAME xl2003 PCFILES path=« C:\data\D_Excel_2003.xls » MSENGINE= ACE;

NOTE: Libref XL2003 was successfully assigned

OK

Excel 2003

PCFILES + JET

LIBNAME xl2003 PCFILES path=« C:\data\D_Excel_2003.xls » MSENGINE= JET;

NOTE: Libref XL2003 was successfully assigned

OK

Excel 2003

XLS

LIBNAME xl2003 xls « C:\data\D_Excel_2003.xls » ;

ERROR: The XLS engine cannot be found.

KO

Excel 2007

EXCEL

LIBNAME xl2007 « C:\data\D_Excel_2007.xlsx »;

ERROR: The EXCEL engine cannot be found. ERROR: Connect: Classe non enregistrée. ERROR: Error in the LIBNAME statement.

KO

Excel 2007

PCFILES

LIBNAME xl2007 PCFILES path=« C:\data\D_Excel_2007.xlsx »;

NOTE: Libref XL2007 was successfully assigned

OK

Excel 2007

PCFILES + ACE

LIBNAME xl2007 PCFILES path=« C:\data\D_Excel_2007.xlsx » MSENGINE= ACE;

NOTE: Libref XL2007 was successfully assigned

OK

Excel 2007

PCFILES + JET

LIBNAME xl2007 PCFILES path=« C:\data\D_Excel_2007.xlsx » MSENGINE= JET;

ERROR: MSENGINE=JET does not support the file type in the PATH option. Use MSENGINE=ACE instead. ERROR: Error in the LIBNAME statement.

KO

Excel 2007

XLS

LIBNAME xl2007 xls « C:\data\D_Excel_2007.xlsx » ;

ERROR: Error in the LIBNAME statement.

KO

2-C-3 - Utilisation de la PROC EXPORT

Les résultats de ces exécutions valent pour la création d'un fichier ou pour l'ajout d'un onglet dans un classeur existant.

À noter également, lorsque l'export doit écraser un fichier existant, un backup est créé sous la forme d'un fichier ayant une extension .bak après l'extension .xls (par exemple : monfichier.xls.bak)

Fichier

Moteur

Code

Journal

Résultat

Excel 2003

EXCELCS

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass3.xls' dbms=ExcelCS replace;sheet=class1;run;

NOTE: « CLASS1 » range/sheet was successfully created.

OK

Excel 2003

XLS

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass1.xls' dbms=xls replace;sheet=class1;run;

NOTE: The export data set has 19 observations and 5 variables NOTE: « C:\data\xlsclass1.xls » file was successfully created ..

OK

Excel 2007

EXCEL

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass2.xls' dbms=Excel replace;sheet=class1;run;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement. Connection Failed. See log for details.

KO

Excel 2007

EXCELCS + fichier Excel Binaire

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass3.xlsb' dbms=ExcelCS replace;sheet=class1;run;

NOTE: « CLASS1 » range/sheet was successfully created.

OK

Excel 2007

EXCELCS + fichier Excel standard + extension xls

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass3.xls' dbms=ExcelCS replace;sheet=class1;run;

NOTE: « CLASS1 » range/sheet was successfully created. (le fichier peut être ouvert sous Excel)

OK

Excel 2007

EXCELCS + fichier Excel standard + extension xlsx

Proc EXPORT data=sashelp.class outfile='C:\data\xlsclass3.xlsx' dbms=ExcelCS replace;sheet=class1;run;

NOTE: « CLASS1 » range/sheet was successfully created. (le fichier ne peut être ouvert par contre)

KO

2-D - Connexion avec le logiciel Access

Sur une plate-forme 64-bits, il est obligatoire d'utiliser le moteur ACCESSCS pour gérer les accès dans les procédures (PROC). Les affectations de bibliothèques (LIBNAME) utiliseront le moteur PCFILES.

Le moteur ACCESSCS :

- peut lire les fichiers Microsoft Office 2007 (ACCDB) ;

- ne peut pas être utilisé avec un LIBNAME. Il ne peut être utilisé qu'au sein des PROC IMPORT et EXPORT ;

- gère correctement les noms de colonnes ayant plusieurs caractères spéciaux.

Le moteur PCFILES :

- peut lire les fichiers Microsoft Office 2007 (ACCDB) ;

- doit être utilisé avec un LIBNAME. Il ne peut pas être utilisé au sein des PROC IMPORT et EXPORT ;

- l'utilisation de PCFILES est basée sur les moteurs (engine) Microsoft : Microsoft Jet Engine et Microsoft ACE engine. Par défaut le LIBNAME utilise MSENGINE= ACE.

Nous allons détailler ci-dessous quelques exemples d'importation et d'exportation à des données Access 2003 (*.mdb) et 2007 (*.accdb) en utilisant SAS 9.2 64-bits. Nous allons comparer les résultats des moteurs du module SAS PC FILE SERVER avec le moteur du module ACCESS to PC FILES (classiquement utilisé dans les versions 32-bits de SAS).

Pour cela, les procédures IMPORT et EXPORT seront présentées avec quelques options spécifiques, à savoir :

- SHEET : option permettant de spécifier une feuille particulière du classeur Excel, tant pour l'import que pour l'export ;

- REPLACE : option nécessaire pour remplacer le fichier en sortie d'une procédure.

Ensuite, l'étape LIBNAME sera utilisée pour illustrer ce type de connexion.

2-D-1 - Utilisation de la PROC IMPORT

Pour importer une table de la base Access, 2003 ou 2007, il convient d'utiliser uniquement le moteur ACCESSCS.

Fichier

Moteur

Code

Journal

Résultat

Access 2003

ACCESS

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS DBMS= ACCESS replace;DATABASE=« C:\data\D_Access_2003.mdb »;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement.

KO

Access 2003

ACCESSCS

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS3 DBMS= AccessCS replace;DATABASE=« C:\data\D_Access_2003.mdb »;

NOTE: WORK.CLASS3 data set was successfully created.

OK

Access 2003

PCFILES

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS3 DBMS= PCFILES replace;DATABASE=« C:\data\D_Access_2003.mdb »;

ERROR: DBMS type PCFILES not valid for import.

KO

Access 2007

ACCESS

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS DBMS= ACCESS replace;DATABASE=« C:\data\D_Access_2007.accdb »;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement.

KO

Access 2007

ACCESSCS

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS7 DBMS= AccessCS replace;DATABASE=« C:\data\D_Access_2007.accdb »;

NOTE: WORK.CLASS7 data set was successfully created.

OK

Access 2007

PCFILES

PROC IMPORTPROC IMPORT TABLE=class OUT=CLASS7 DBMS= PCFILES replace;DATABASE=« C:\data\D_Access_2007.accdb »;

ERROR: DBMS type PCFILES not valid for import.

KO

2-D-2 - Utilisation du LIBNAME

La seule façon de créer un LIBNAME sur une base Access reste la solution employant le moteur PCFILES.

Fichier

Moteur

Code

Journal

Résultat

Access 2003

ACCESS

LIBNAME myref3 ACCESS « C:\data\D_Access_2003.mdb »;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement.

KO

Access 2003

ACCESSCS

LIBNAME myref3 ACCESSCS « C:\data\D_Access_2003.mdb »;

ERROR: The ACCESSCS engine cannot be found. ERROR: Error in the LIBNAME statement.

KO

Access 2003

PCFILES

LIBNAME myref3 PCFILES path=« C:\data\D_Access_2003.mdb »;

NOTE: Libref MYREF3 was successfully assigned

OK

Access 2007

ACCESS

LIBNAME myref7 ACCESS « C:\data\D_Access_2007.accdb »;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement.

KO

Access 2007

ACCESSCS

LIBNAME myref7 ACCESSCS « C:\data\D_Access_2007.accdb »;

ERROR: The ACCESSCS engine cannot be found. ERROR: Error in the LIBNAME statement.

KO

Access 2007

PCFILES

LIBNAME myref7 PCFILES path=« C:\data\D_Access_2007.accdb »;

NOTE: Libref MYREF7 was successfully assigned

OK

2-D-3 - Utilisation de la PROC EXPORT

Les tests confirment qu'un seul moteur est exploitable sur cette plate-forme. Le moteur habituel « Access » ne fonctionne plus.

Fichier

Moteur

Code

Journal

Résultat

Access 2003

ACCESS

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=ACCESS REPLACE ;DATABASE=« C:\data\D_Access_2003.mdb »; RUN;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement. Connection Failed. See log for details.

KO

Access 2003

ACCESSCS

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=ACCESSCS REPLACE ;DATABASE=« C:\data\D_Access_2003.mdb »; RUN;

NOTE: « CLASS » table was successfully created.

OK

Access 2003

PCFILES

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=PCFILES REPLACE ;DATABASE=« C:\data\D_Access_2003.mdb »; RUN;

ERROR: DBMS type PCFILES not valid for export.

KO

Access 2007

ACCESS

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=ACCESS REPLACE ;DATABASE=« C:\data\D_Access_2007.accdb »; RUN;

ERROR: Connect: Classe non enregistrée ERROR: Error in the LIBNAME statement. Connection Failed. See log for details.

KO

Access 2007

ACCESSCS

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=ACCESSCS REPLACE ;DATABASE=« C:\data\D_Access_2007.accdb »; RUN;

NOTE: « CLASS » table was successfully created.

OK

Access 2007

PCFILES

PROC EXPORT DATA= sashelp.class OUTTABLE=class DBMS=PCFILES REPLACE ;DATABASE=« C:\data\D_Access_2007.accdb »; RUN;

ERROR: DBMS type PCFILES not valid for export.

KO

3 - Erreurs couramment rencontrées avec le module PCFILE SERVER

Ce chapitre présente les erreurs rencontrées au fil du temps dans l'usage de ces connecteurs sur la plate-forme 64-bits. Ces erreurs sont non imputables au code SAS, mais plutôt à la stabilité du système et des composants SAS.

3-A - Mauvaise initialisation du service PCFILE SERVER

3-A-1 - Erreur sur les procédures

Problème : Il arrive que la connexion n'ait pas le temps de s'initialiser correctement, notamment lors des PROC EXPORT.

1    PROC EXPORT OUTFILE='d:\xlsair.xls' DATA=sashelp.air
2    DBMS=Excelcs REPLACE;
3    run;

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: Server communication failure.  The client log file (PCFILES.log) may contain additional  information..
ERROR: CLI disconnect failed: Server communication failure.  The client log file (PCFILES.log) may contain additional information.
ERROR: CLI disconnect failed: Server communication failure.  The client log file (PCsFILES.log) may contain additional information.
ERROR: Error in the LIBNAME statement.
ERROR: Export unsuccessful.  See SAS Log for details

Solution : comme le montre l'exemple de code suivant, il faut créer avant la PROC EXPORT un LIBNAME sur le fichier de destination qui pourtant n'existe pas encore.

3-A-2 - Erreur sur les LIBNAME

Problème : lors de l'utilisation d'un LIBNAME, ici xls_obj, il arrive que la connexion n'ait pas le temps de s'initialiser correctement.

sas

data EXTRACTION (drop=CENTRE CODE_CENTREPRODUIT
2011-04-01T07:45:23,871 INFO  [00000015] - 612! rename=(centre2=centre id_centre=CODE_CENTRE PRODUIT2=PRODUIT ));
2011-04-01T07:45:23,871 INFO  [00000015] - 613    set xls_obj.OBJECTIFS_RM;

ERROR: CLI prepare error: Server communication failure.  The client log file (PCFILES.log) may contain additional information.

Solution : lorsqu'il s'agit d'accéder à des fichiers Excel 2000-2003, il est préférable d'utiliser l'option MSENGINE= JET afin de minimiser ce genre d'erreur.

LIBNAME xls_obj PCFILES path= "\ref\OBJECTIFS_RM.xls" MSENGINE= JET ;

L'utilisation de PCFILES est basée sur les moteurs Microsoft : Microsoft Jet Engine et Microsoft ACE Engine. Par défaut le LIBNAME utilise MSENGINE= ACE.

3-B - Impossibilité d'atteindre un fichier sur le réseau

Problème : l'erreur suivante provient encore de l'interaction de PC FILE SERVER avec le système.

Libname xl2007 PCFILES "SasData\D_Excel_2007.xlsx";

ERROR: CLI error trying to establish connection: [Microsoft] [ODBC Excel Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
ERROR: Error in the LIBNAME statement. Connection Failed.
See log for details.

Solution : lorsque le fichier à importer ou à exporter se trouve sur un lecteur réseau (i.e. en dehors des lecteurs physiquement rattachés à la machine, soit dans 95 % des cas maintenant) il arrive que PC FILE SERVER en tant que service Windows n'ait pas pu reconnaître le mappage réseau (ce dernier étant déclaré après le démarrage des services).

Dans ce cas, il faut spécifier le nom universel du chemin du fichier, soit redémarrer le service après que le mappage réseau a été réalisé.

Le nom universel (UNC, Universal Naming Convention en anglais) est le nom du répertoire composé de la façon suivante : \\nom_du_serveur\Nom_du_repertoire\D_Excel_2007.xlsx

3-C - Impossibilité de remplacer un fichier existant

Cette erreur est susceptible d'apparaître lorsque de nombreuses PROC EXPORT sont générées par une boucle MACRO (%do %end). Le système ne semble plus suivre et refuse d'accéder aux fichiers.

Dans ce cas, plutôt que de laisser SAS tenter de remplacer le fichier cible, il est préférable de le supprimer soi-même. La méthode la plus simple étant de passer la commande SAS suivante juste avant la PROC EXPORT.

Dans l'exemple, elle permet de supprimer physiquement le fichier nommé mybook.xls :

data _null_;
fname="fictmp";
rc=filename(fname,"D:\temp\mybook.xls");
if rc=0 and fexist(fname) then rc=fdelete(fname);
rc=filename(fname);
run;

Note : il peut arriver dans certains cas que le fichier ne puisse même plus être supprimé. L'expérience a montré que l'utilisateur SYSTEM se retrouvait propriétaire du fichier et ne pouvait rendre les droits au propriétaire originel : si l'étape DATA présentée ci-avant est exécutée par l'utilisateur, elle ne peut fonctionner, car il ne sera plus le propriétaire du fichier.

Il faut alors redémarrer le service PC FILE SERVER et se connecter en tant qu'administrateur pour libérer les fichiers. Ce cas est fort heureusement peu courant.

4 - Les solutions à apporter pour stabiliser ou réussir ses échanges entre SAS et les produits Office

4-A - Installer une version récente de SAS 9.2

Installer SAS 9.2 Maintenance 3 permet de profiter des derniers correctifs. Cette version a été largement améliorée au niveau de la stabilité et supporte la plupart des plates-formes 64-bits.

SAS 9.3 M0 n'a pas changé la gestion des drivers Excel, ce ne peut être la raison de migrer malheureusement.

4-B - Installer une version d'Excel compatible

La dernière version mise à jour par MS Office pour Excel 2007 est la version 12.0.6557, le support SAS liste dans cette note : http://support.sas.com/kb/40/383.html, quelques problèmes pouvant encore apparaître lors de l'utilisation de celle-ci.

Pour éviter tout problème de stabilité entre SAS et Excel 2007, il vaut mieux utiliser la version 12.0.4518. Autrement, les utilisateurs qui vont ouvrir des fichiers Excel depuis le serveur (lorsque les utilisateurs travaillent directement sur le serveur depuis MSTSC par exemple), risqueront de visualiser un fichier avec de nombreuses données manquantes alors que ce même fichier peut être ouvert sur leur poste (XP 32-bits) sans problème.

4-C - Installer les drivers spécifiques Microsoft Access Database Engine 2010

Ces drivers semblent plus stables pour les échanges. Indépendamment du niveau de maintenance de SAS 9.2 (M1, M2, M3).

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255

4-D - Donner les pleins droits aux utilisateurs sur une clef de registre

Afin d'éviter certaines erreurs du service PC FILE SERVER, il est parfois nécessaire de modifier les droits sur HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC en donnant les pleins droits au groupe SAS. Dans l'exemple ci-dessous le groupe SAS se nomme LogAsBatch et hérite des pleins droits sur la clef ODBC.

4-E - Utiliser SAS Enterprise Guide

La version SAS Enterprise Guide (EG) 4.1 possède quelques bogues : le plus connu étant le blocage des futurs imports d'un fichier sur le nombre d'observations issu du premier import. Par exemple, si le fichier a été importé à l'instant avec vingt lignes, une seconde importation n'en importera pas plus même si le fichier Excel a été modifié entre-temps (cf. http://support.sas.com/kb/14/647.html).

Néanmoins, si l'on se focalise sur la version EG 4.2 et 4.3, il arrive très souvent que l'utilisateur de SAS Enterprise Guide (EG) constate un meilleur fonctionnement dans ces imports/exports qu'avec le client traditionnel.

À la différence du client traditionnel, SAS Enterprise Guide n'utilise pas le module SAS ACCESS TO PC FILES. SAS EG réalise ces importations et exportations en deux temps. Dans un premier temps, il utilise un composant Windows pour transformer les fichiers Excel ou Access en un fichier texte stocké dans un espace temporaire (dans l'image illustrant cette action, le répertoire est C:\Users\datametric\AppData\Local\Temp\ SEG4020).

Dans un second temps, l'assistant d'importation crée une étape DATA pour récupérer le résultat. L'image montre bien que SAS crée une étape DATA basée sur l'INFILE.

Il faut donc conclure que la tâche d'import de SAS visible dans le projet n'est donc que la phase finale du processus d'importation. Il en va de même pour la tâche d'exportation, l'outil va utiliser une API pour créer les fichiers au format XLS et MDB.

En ce qui concerne les exports au format XLSX, EG 4.3 n'a pas la possibilité d'exporter nativement dans ce format. Il faut utiliser cette tâche personnalisée mise à disposition par SAS :

- http://support.sas.com/documentation/onlinedoc/guide/customtasks/samples/SAS.Tasks.ExcelExport.zip

Une fois de plus, cet outil n'utilise pas le module SAS, mais les composants Windows.

Sachez que SAS Enterprise Guide pourrait utiliser ce module et donc générer des PROC IMPORT et EXPORT réexploitable. Cependant, les conditions d'utilisation sont strictes et le code généré utilisera le moteur de connexion DBMS=EXCEL ou ACCESS.

Les conditions sont les suivantes :

- le fichier Excel doit être stocké là où se trouve le moteur d'exécution (si SAS est exécuté en local alors le fichier doit être en local ou sur un répertoire visible depuis l'explorateur Windows) ;

- si le moteur DBMS=EXCEL ou ACCESS n'est pas valide (comme sur les plates-formes 64-bits), l'outil ne permettra pas d'activer cette option.

L'image suivante montre que l'assistant désactive l'option sur une plate-forme 64-bits.

5 - Conclusion

Afin d'utiliser les procédures d'import et d'export de SAS 9.2 sur une plate-forme 64-bits, il est nécessaire de connaître les différents moteurs de connexion ainsi que leurs impacts sur les fichiers de résultat. Les exemples fournis plus haut permettent de se faire une idée des erreurs qui peuvent être générées et des options utilisables ou non.

Dans le cas d'une migration d'une plate-forme Windows 32-bits à 64-bits, les programmes devront être modifiés sur ces points, même si l'ensemble paraît s'intégrer dans une migration à isopérimètre.

Ce tableau synthétise les possibilités d'utilisation des moteurs de connexion selon la source et l'utilisation d'une Procédure ou d'un LIBNAME.

Pour les fichiers Excel 2003

Nom du moteur

Excel

xls

ExcelCS

PCFILES

LIBNAME

Non

Non

Non

Oui

Proc IMPORT

Non

Oui

Oui

Non

Proc EXPORT

Non

Oui

Oui

Non

Pour les fichiers Excel 2007

Nom du moteur

Excel

xls

ExcelCS

PCFILES

LIBNAME

Non

Non

Non

Oui

Proc IMPORT

Non

Non

Oui

Non

Proc EXPORT

Non

Oui

Oui/ Non

Non

Pour les fichiers Access (2003 et 2007 )

Nom du moteur

Access

AccessCS

PCFILES

LIBNAME

Non

Non

Oui

Proc IMPORT

Non

Oui

Non

Proc EXPORT

Non

Oui

Non

Par expérience, la PROC IMPORT doit être utilisée avec DBMS=XLS plutôt que EXCELCS qui exploite un service spécifique sur Windows et apparaît à l'usage plus lent.

Indépendamment de SAS, il est nécessaire d'utiliser les drivers Office de dernière génération tout en sachant que SAS ne sait pas gérer les toutes dernières versions. Il n'est donc pas vain de suivre les mises à jour et les notes techniques à ce sujet.

Cet article nous a montré que les échanges entre SAS et Excel/Access présentent de sérieux problèmes. Pour y remédier, il est envisageable de créer une base SQL Server qui supporterait la fonction de stockage des données. En effet SAS et Excel/Access communiquent très bien avec ce système de base de données ; SAS utilise un connecteur ODBC pour communiquer avec ce système de base de données.

On pourrait alors imaginer qu'Excel serve d'interface de saisie pour des données qui iraient dans les tables SQL. Ainsi SAS pourrait charger les données par la suite pour les traitements utilisateurs. Ce genre de projet paraît surdimensionné pour de simples bases, mais les utilisateurs rencontreront une meilleure stabilité dans leur Procédure et profiteront des avantages d'une base SQL Server.

6 - Liens

http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine#Compatibility

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a002645029.htm

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003102702.htm

http://support.sas.com/kb/41/060.html

http://support.sas.com/kb/33/086.html

http://support.sas.com/kb/33/228.html

http://support.sas.com/kb/40/383.html

http://support.sas.com/kb/40/409.html

http://support.sas.com/kb/37/479.html

http://support.sas.com/kb/40/597.html

http://support.sas.com/kb/36/967.html

7 - Remerciements

J'adresse tous mes remerciements à Fabrice Morlais, Claude Leloup et Maxime Gault de l'équipe de rédaction « Developpez.com » pour le temps qu'ils m'ont accordé pour la correction et l'amélioration de cet article.

8 - À 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 bases de données marketing et de formations personnalisées auprès des équipes d'analystes.