Le syndrome de Stockholm - vous allez adorer les macros SAS - Partie 2

Nous avons vu dans un précédent billet sur les performances qu’il était indispensable d’utiliser la macro %connectora dans nos requêtes avec la construction suivante :

proc sql;
%connectora;
EXECUTE(

    create table PARISIENS as
	select * from IR_BEN_R
    where BEN_RES_DPT = 075

) BY ORACLE;
quit;

⚠ Si vous exécutez ce code alors que la table ORAUSER.PARISIENS existe déjà, vous aurez un message d’erreur. Pensez à l’effacer dans l’arborescence de la sous-fenêtre Serveurs, ou alors utilisez la macro suivante :

%_eg_conditional_dropds(ORAUSER.PARISIENS)

Nous allons maintenant écrire deux macros qui permettent d’alléger grandement la construction ci-dessus.

Comme vu précédemment, sauvegardons-les dans un fichier au chemin suivant Fichiers\sasdata1\download\macros_oracle.sas :

/* Dans le fichier macros_oracle.sas */

%macro oracle_entete();
  proc sql;
  %connectora
  EXECUTE(
%mend;

%macro oracle_end();
  ) BY ORACLE;
  quit;
%mend;

Pour pouvoir utiliser immédiatement ces macros dans un nouveau script, il vous faut exécuter le fichier macros_oralce.sas. Pour pouvoir les utiliser dans vos prochaines sessions, il faut ajouter le fichier macros_oracle.sas dans votre fichier perso.sas (cf. billet précédent).

Voici comment on peut alors utiliser ces macros :

/* Dans un nouveau script */

%oracle_entete

    create table PARISIENS as
	select * from IR_BEN_R
    where BEN_RES_DPT = 075
    
%oracle_end

C’est déjà mieux, mais on peut être amené souvent à créer des tables, d’une part pour y stocker le résultat d’une requête comme pour la table PARISIENS, parfois également pour partir d’une table vide que l’on remplira en utilisant une boucle de requêtes itérant mois par mois (encore une fois pour des questions de performance).

Tenant compte de ces deux cas de figure voyons les nouvelles macros que l’on peut ajouter au fichier macros_oracle.sas :

%macro oracle_create_empty(table);
  %_eg_conditional_dropds(ORAUSER.&table.)
  %oracle_entete()
   create table &table.
%mend;

%macro oracle_create(table);
  %oracle_create_empty(&table.) as
%mend;

La première macro inclut la ligne :

%_eg_conditional_dropds(ORAUSER.&table.)

Cela permet de supprimer la table que l’on souhaite créer si elle existe déjà.

Pour mieux comprendre, voici un exemple de création de table vide.

Dans la deuxième macro, on reprend le code de la première auquel on ajoute le mot clé as nécessaire.

Voici maintenant comment on peut utiliser ces deux macros. Tout d’abord :

%oracle_create_empty(A_REMPLIR)

    (NIR_ANO_17 varchar2(17)
    ,DATE_ENTREE date
    ,DATE_SORTIE date
    ,CODE_CCAM varchar2(10))

%oracle_end

Ce code crée une table vide ORAUSER.A_REMPLIR avec 4 colonnes.

Maintenant :

%oracle_create(PARISIENS)

	select * from IR_BEN_R
    where BEN_RES_DPT = 075

%oracle_end

On a, une fois de plus, créé notre table PARISIENS.

Notez bien que la macro %oracle_create s’occupe seule de supprimer la table si elle existe au préalable, autrement dit les 4 lignes de code ci-dessus sont équivalentes à :

%_eg_conditional_dropds(ORAUSER.PARISIENS)
proc sql;
%connectora;
EXECUTE(

    create table PARISIENS as
	select * from IR_BEN_R
    where BEN_RES_DPT = 075

) BY ORACLE;
quit;

Pour finir, voici les autres macros que l’on trouve dans mon fichier macros_oracle.sas (et le dépôt git où ce fichier sera tenu à jour) :

/* Crée une table avec le préfixe N qui
contient le nombre de lignes */
%macro count_lines(table);
  %oracle_create(N_&table.)
      select count(*) as N
      from &table.
  %oracle_end()
%mend;

/* Comme ci-dessus puis efface la table initiale */
%macro count_lines_drop(table);

  %count_lines(&table.)
  %_eg_conditional_dropds(ORAUSER.&table.)

%mend;

/* Crée une table avec le préfixe Z qui contient le nombre de
valeurs distinctes d' une colonne donnée */

%macro count_distinct_col(table, col, suffix=1);

  %oracle_create(Z_&col._&suffix.)
    select distinct &col.
    from &table.
  %oracle_end()

  %count_lines(Z_&col._&suffix.)
  %oracle_drop(Z_&col._&suffix.)

%mend;

/* ajoute une colonne à une table existante */
%macro add_col(table, col_name, col_type);

  %oracle_entete()
    alter table &table. add &col_name. &col_type.
  %oracle_end()

%mend;