Lire et écrire dans des fichiers Excel avec LIBNAME XLSX

Lire et écrire dans des fichiers Excel avec Libname XLSX est une alternative élégante à l’utilisation des procédures SAS PROC IMPORT et PROC EXPORT :

Très simple à utiliser : un onglet de votre fichier Excel se manipule comme une table SAS dans votre code. On verra que l’on peut aussi manipuler des plages de cellules dans un onglet.
Avec une seule étape DATA, on peut importer un fichier Excel et créer de nouvelles variables (on lit les données une fois de moins).
L’écriture vers Excel se fait aussi tout à fait intuitivement (néanmoins, moins de liberté qu’avec DDE par exemple).

Assigner le libref FICH_XLS à mon fichier Excel

Pour assigner le libref FICH_XLS à mon fichier Excel exemples.xlsx, cette commande LIBNAME suffit :

/* Assignation du libname avec le moteur XLSX, le chemin est un fichier Excel et non plus un répertoire */
libname FICH_XLS XLSX 
"/mutu/FW/users/HOME/S862975/fichiers/in/exemples.xlsx" ;

Une fois la commande exécutée, une nouvelle librairie FICH_XLS apparait, et les onglets DATA1 et DATA2 de mon fichier constituent les tables de cette librairie.

Exemple 1 : lire un onglet et créer de nouvelles variables en même temps (étape DATA)

Mon onglet DATA2 contient une vingtaine de lignes de ce type :

On souhaite importer cet onglet et calculer deux variables complémentaires IMC et IMG avec les formules suivantes :

IMC = poids (kg) / taille au carré (m x m)
IMG = (1,2 x IMC) + (0,23 x âge) – (10,8 x sexe) – 5,4 (sexe : M=1, F=0)

Tout peut être fait en une étape, y compris les renommages de variables, les drops, et bien sur les filtres

Le code :

data WORK.CALCULS_IMC (rename=(A=IMC B=IMG)) ;
  set FICH_XLS.DATA2 (drop=CITY STATENAME) ;
  length POIDS_KG TAILLE_M A B 8. ;
  format B PERCENT6.2 ;
  POIDS_KG = WEIGHT / 2.2046 ;
  TAILLE_M = HEIGHT * 0.0254 ;
  A = POIDS_KG / TAILLE_M**2 ; 
  B = ((1.2 * A) + (0.23 * AGE) - 5.4) / 100 ;
  if SEX eq "M" then B = B - 0.108 ;
run ;

Le résultat :

Exemple 2 : lire deux onglets en même et les joindre (proc SQL)

Pour cet exemple, on remarque que l’onglet DATA1 contient lui aussi les variables CITY et STATENAME. On peut associer à chaque enfant de l’onglet DATA2 le code de l’état (STATECODE), le numéro de l’état (STATE) et le nom du comté (COUNTYNM) récupérés depuis l’onglet DATA1.

Pour cela, on lit les onglets DATA1 et DATA2 en même temps et on les joint selon les clés CITY et STATENAME avec le code suivant :

proc sql ;
  create table WORK.JOIN_ONGLETS as
  select data2.*, data1.STATECODE, data1.STATE, data1.COUNTYNM
  from FICH_XLS.DATA2 data2
  left join (
    select distinct CITY, STATE, STATECODE, STATENAME, COUNTYNM 
    from FICH_XLS.DATA1
  ) data1
  on data1.CITY = data2.CITY 
  and data1.STATENAME = data2.STATENAME ;
quit ;

Le résultat :

En une requête, on réalise l’équivalent de 2 PROC IMPORT et une jointure.

Exemple 3 : écrire dans un fichier Excel

Dans les deux exemples précédents, on importe des données du fichier Excel pour écrire dans une librairie SAS. On peut tout aussi bien utiliser un LIBNAME XLSX pour écrire vers un fichier Excel.

Il faut cependant garder en tête 2 règles :

Écrire vers un onglet d’un fichier assigné avec LIBNAME XLSX va écraser les cellules si elles sont déjà remplies
L’écriture des données vers un onglet commence à la case A1. Sinon, il faut utiliser une plage de cellules.

Note : les noms des onglets à lire ou écrire peuvent contenir des espaces ou des accents, il faut utiliser la notation FICH_XLS.”Nouvel onglet”n

En reprenant l’exemple 2 et en redirigeant la table en sortie directement vers un nouveau fichier Excel, on obtient le code suivant :

libname NEW_XLS XLSX "/mutu/FW/users/HOME/S862975/fichiers/in/nouveau.xlsx" ;
proc sql ;
  create table NEW_XLS."Rapport détaillé"n as
  select data2.*, data1.STATECODE, data1.STATE, data1.COUNTYNM
  from FICH_XLS.DATA2 data2
  left join (
    select distinct CITY, STATE, STATECODE, STATENAME, COUNTYNM 
    from FICH_XLS.DATA1
  ) data1
  on data1.CITY = data2.CITY 
  and data1.STATENAME = data2.STATENAME ;
quit ;

Le résultat :

En une étape, on a réalisé 2 PROC IMPORT, une jointure et un PROC EXPORT

Exemple 4 : lire et écrire dans une plage de cellules

Même si on n’a pas la souplesse de ODS EXCEL ou DDE, il est possible de lire ou d’écrire seulement une certaine zone d’un onglet Excel.

Il faut au préalable que les « plages » soient définies dans le fichier Excel : on sélectionne les cellules, puis clic droit et on choisit « Définir un nom ». Ce nom sera ensuite visible dans la librairie XLSX sur SAS.

Le code :

data test ;
    set myxls."PLAGE_1"n ;
run ;    
data test2 ;
    do COL1 = 1 to 2 ; 
        COL2 = "A"; COL3 ="B" ; COL4 = "C" ; output ; 
    end ; 
run ;              
data myxls."PLAGE_2"n ;
    set test2 ;
run ;