Mettre des données côte-à-côte pour votre reporting

Lorsque vous devez présenter vos données (faire du reporting), vous pouvez vous trouver dans la situation suivante : mettre côte-à-côte des données qui n’ont rien à voir entre elles. La situation peut se résoudre très rapidement avec un merge sans instruction BY. Mais que faire dans le cas où vous devez quand même grouper vos données par une clé (exemple l’identifiant du client) mais dans chaque source plus d’une observation par clé ? C’est ce que je vous propose de découvrir dans cet article.

1. Les données pour l’exemple

Deborah a deux lignes d’observation et Patrick aussi.

data demography;
length cl_name $10 criteria $10 ;
input cl_name $ criteria $ crit_value ;
datalines;
deborah age 15
deborah height 1.66
patrick age 14
patrick height 1.75
;
run;

Deborah a acheté 4 articles (DVD, téléphone portable, une radio et des écouteurs (4 lignes d’observations pour Deborah) tandis que Patrick a acheté deux articles.

data sell;
length cl_name $10 achat $10;
input cl_name $ achat $;
datalines;
deborah dvd
deborah mobile
deborah radio
deborah headset
patrick mobile
patrick tv
;
run;

2. Le résultat sans instruction BY

data mix_demo_sell;
merge demography
sell;
by cl_name;
run;

Vous vous retrouvez avec un merge MANY-to-MANY qui ne résous par votre problème. Pensez toujours à vérifer votre log après un MERGE. Ce type de note est souvent signe d’une erreur de raisonnement dans votre programme.

proc print data=mix_demo_sell;
run;

On observe ici un RETAIN implicite propre au merge.

3. Ajouter un compteur pour chaque client

Pour contourner le problème, on ajouter un identifiant supplémentaire dans les deux tables.

Dans la table DEMOGRAPHY, le client DEBORAH a deux observations. On aura donc CNT=1 et CNT=2.

data demography;
set demography;
by cl_name;
if first.cl_name then cnt=1;
else cnt+1;
run;

Dans la table SELL, le client DEBORAH a quatre observations. On aura donc CNT=1, CNT=2, CNT=3 et CNT=4.

data sell;
set sell;
by cl_name;
if first.cl_name then cnt=1;
else cnt+1;
run;

Il ne reste plus qu’à combiner les deux tables à partir de la variable client (CL_NAME) et de la variable CNT.

data mix_demo_sell;
merge demography
sell;
by cl_name cnt;
run;

proc print data=mix_demo_sell;
run;

Le résultat désiré se présente ainsi :

Lecture complémentaire

• Savoir interpréter une note liée au MERGE

• La base de la jointure de deux data sets avec MERGE

• Oh attention danger avec MERGE et IF

Sauvegarder le résultat d’une comparaison de deux data sets dans un data set avec PROC COMPARE

Fréquemment, le programmeur est amené à comparer deux datasets. Deux méthodes s’offrent à lui : un merge accompagné de l’option IN ou la procédure PROC COMPARE. Voici une illustration de PROC COMPARE.

1. Préparer les données

Pour illustrer la procédure PROC COMPARE, il nous faut deux data sets. Le premier s’intitule CLASS et est sauvegardé dans la bibliothèque SASHELP (SASHELP.CLASS). Le second s’appelle également CLASS. C’est un dérivé du premier. Il est savegardé dans la bibliothèque temporarire WORK (WORK.CLASS).

Voici comment est créé le second data set à partir du premier :

• Une observation de la variable NAME passe de Alice à Alica
• Une observation de la variable AGE prend la valeur 16 quand le nom est Robert
• Une observation est ajoutée. La variable NAME prend la valeur Extra. Les autres variables ont des valeurs manquantes.

data class;
set sashelp.class;
if name=‘Alice’ then name=‘Alica’;
if name=‘Robert’ then age=16;
output;
if _N_=1 then
do;
name=‘Extra’;
output;
end;
run;

2. L’importance d’avoir des données triées

Dans la procédure PROC COMPARE utiliser par la suite, j’ai choisi d’utiliser l’instruction ID. Cette instruction requiert que les données soient triées préalablement. En effet, si ce n’est pas le cas un message d’erreur comme celui-ci apparaît :

proc sort data=class;
by name;
run;

3. Enregistrer le résultat de la différence dans un data set

Ce qui m’intéresse ici est de sauvegarder le résultat de la comparaison dans un data set et non de l’afficher dans la sortie .txt. Pour ce faire, l’option OUT= précise le nom du data set qui sauvergardera les résultat et l’option NOPRINT stoppe l’affichage des résultats dans la log.

Quatres options précisent le contenu du résultat :

• OUTNOEQUAL : seules les observations où une différence est détectée seront sauvegardés
• OUTBASE : une ligne contiendra les lignes d’observation du fichier de base défini dans l’option DATA=
• OUTCOMP : une ligne contiendra les lignes d’observation du fichier de comparaison indiqué avec l’option COMPARE=
• OUTDIF: une ligne annotera les valeurs présentant des différences

En outre, l’instruction ID me permet de lister ma/mes variables que je considère comme des variables clés et d’en tenir compte dans la comparaison. J’ai choisi de comparer les observations seulement si elles ont la même valeur dans la variable NAME. Si une valeur de NAME n’est présente que dans un des deux fichiers, cette inconsistence sera également enregistrée.

proc compare data=sashelp.class compare=class
out=diff_class outnoequal outbase outcomp outdif
noprint;
id name;
run;

4. A quoi ressemble le fichier contenant les différences

Voici le contenu du fichier DIFF_CLASS répertoriant les les différences entre le fichier SASHELP.CLASS et le fichier WORK.CLASS.

options ls=max nocenter;
proc print data=diff_class width=min;
format _all_;
run;

Deux points sont à noter ici.

Tout d’abord, on a trois cas où la valeur de la variable définie dans ID est présente dans un seul des deux fichiers. Dans chacun de ces trois cas, le fichier DIFF_CLASS enregistre une seule ligne d’observations.

• NAME=Alica est seulement présent dans le fichier de comparaison (WORK.CLASS)
• NAME=Alice est seulement présent dans le fichier de base (SASHELP.CLASS)
• COMPARE=Extra est seulement présent dans le fichier de comparaison (WORK.CLASS)

Puis, on a un cas où NAME=Robert est présent dans les deux fichiers mais avec des différences dans les autres variables. Une troisième ligne DIF précise où les différences et les égalités se situent.

• Valeurs numériques
  • E pour égalités : c’est le cas des variables HEIGHT et WEIGHT
  • un nombre qui mesure la différence :  l’âge dans WORK.CLASS et de 4 plus grand que celui dans le fichier de base SASHELP.CLASS
• Valeurs caractères
  • Un point pour des égalités : la variable SEX
  • Des xxx pour une différence : le cas ne se présente pas dans notre exemple

5. Un fichier est créé même si aucune différence n’est enregistrée

Il n’existe pas d’options dans PROC COMPARE pour ne créer un fichier que si au moins une différence entre les fichiers est trouvée.

proc compare data=sashelp.class compare=sashelp.class;
out=nodiff outnoequal outbase outcomp outdif
noprint;
id name;
run;

Vous devrez donc ensuite compléter votre programme à la suite de PROC COMPARE pour supprimer ces fichiers si c’est ce que vous avez besoin.

Tout sur l’instruction BY

Me rendant compte que l’instruction BY pouvait poser des difficultés lorsqu’on débute avec SAS, j’ai décidé de faire le point sur cette instruction.

1. Une instruction locale

L’instruction BY peut servir dans toutes les étapes data et procédures à l’exception de PROC SQL.

2. Un ordre défini par le nom des variables :

L’instruction BY est suivie du nom des variables servant pour le tri.

Dans un premier temps, les observations sont triées selon les valeurs de la première variable citée, ici SEX. Puis, pour chacune des valeurs prises par SEX (M et F), les données sont triées par NAME.

proc sort data=sashelp.class out=class;
   by sex name;
run;

3. PROC SORT : le premier usage de l’instruction BY : L’instruction BY est logiquement obligatoire dans un PROC SORT. Sinon SAS ne saurait pas dans quel ordre trier les données. Le mot DESCENDING peut-être ajouté pour préciser un ordre décroissant. Ici les données sont d’abord triées par SEX puis par nom en ordre descendant.

proc sort data=sashelp.class out=class;
   by sex descending name;
run;

L’autre manière de trier les données est d’utiliser la procédure PROC SQL.

Idée : Vous aurez souvent un PROC SORT avant d’utiliser l’instruction BY dans une autre procédure ou étape data. Simplifiez-vous la vie en copiant l’insstruction au niveau du PROC SORT et en la collant au niveau de la procédure ou étape data suivante.

4. SAS vous informe si les données ne sont pas triées comme indiqué

L’instruction BY précise à SAS dans quel ordre les données doivent être lues. Si les données ne respectent pas cet ordre, SAS s’arrête et fourni un message d’erreur.

5. L’instruction BY : obligatoire ou optionnelle ?

L’instruction BY est optionelle dans une instruction SET. Utilisée pour empiler les données de deux data sets, elle permettra d’intercaler les observations des deux sources selon leur valeurs au lieu de mettre d’abord toutes les observations du premier data set cité et ensuite toutes les observations du second data set.

L’instruction BY reste pratiquement indispensable avec MERGE puisqu’elle sert à relier les observations de deux data sets par les variables du même nom. Sans elle, les observations du second data set pour les variables du même nom réécrirait sur celle du premier.

L’instruction BY est indispensable avec FIRST et LAST.

6. D’autres usages de l’instruction BY

L’instruction BY peut s’ajouter dans un PROC REPORT. Une option NOBYLINE permettra de changer le titre à chaque nouvelle combinaison de valeurs désignée par l’instruction. Néanmoins la mise à jour du titre par cette approche avec ODS RTF par exemple ne fonctionne pas.

L’instruction BY peut servir dans toutes less procédures (sauf PROC SQL) et notamment dans les procédures statistiques. Voir dans les lectures complémentaire l’usage de l’instruction BY avec PROC FREQ.

Lectures Complémentaires

• Trier les données par ordre croissant et décroissant  : PROC SORT et l’instruction BY
• Empiler des data sets : L’instruction BY utilisée avec SET
• La base de la jointure entre deux data sets avec MERGE : l’instruction BY utilisée avec MERGE
• Retrouver la valeur suivante avec LAG et MERGE : un rare cas de MERGE sans instruction BY
• Mettre à jour un data set à partir d’un autre : l’instruction BY avec UPDATE
• Repérer les premiers et derniers records (FIRST/LAST) : l’instruction BY avec FIRST et/ou LAST
• Jongler avec les records grâce aux compteurs : l’instruction BY avec FIRST
• 9 points pour personnaliser ses titres : l’instruction BY utilisée en combinaison avec TITLE
• Faire pivoter un data set : un premier exemple avec PROC TRANSPOSE : l’instruction BY avec PROC TRANSPOSE
• Un tableau à une dimension : PROC FREQ et ODS OUTPUT : l’instruction BY avec PROC FREQ

SAS Online Doc

• BY-Group Processing in SAS Programs

La 3ème méthode qui fait la différence pour sélectionner en se basant sur une second data set

Suite au précieux conseil d’Arnaud Gaborit (A.I.D.), je vous propose de compléter l’article du 26 janvier dernier intitulé « Deux méthodes pour sélectionner en se basant sur une second data set« . Les personnes travaillant sur de grosses bases de données apprécieront.

Lectures complémentaires :

• L’article « 2 méthodes pour de nouveaux formats » intéresseront ceux qui dans un premier temps découvre la création de formats en se basant sur un data set.
• Pour l’usage de la fonction PUT, vous pouvez consulter « Convertir une variable caractère en numérique et inversement« .
• L’option NODUPKEY de PROC SORT est discuté dans l’article « Identifier et supprimer les doublons« .

1. Rappel : les données et le résultat attendu

D’un côté, on a un data set contenant la liste des effets secondaires observés. Il y a une ligne par effet secondaire. Seuls les patients ayant eu un effet secondaire sont enregistrés.

data ae_multi;
   input patref ae_id;
   datalines;
1 1
1 2
2 1
4 1
4 2
5 1
;
run;

De l’autre côté, on a la liste des patients correspondant à la population qui nous intéresse. Une ligne correspond à un patient.

data pat_uniq;
   input patref;
   datalines;
2
3
4
;
run;

Le but est de garder uniquement les effets secondaires des patients présents dans notre population.

patref ae_id

   2     1
   4     1
   4     2

2. Créer un format à partir d’un data set

Dans une premier temps, un format numérique (TYPE=N est la valeur par défaut) appelé PATREF (FMTNAME=’PATREF’) est créé à partir du data set sur les patients. La variable PATREF sert de valeur START. Pour toutes les valeurs de START, on applique le même libellé (LABEL=’retenir’).

Si le data set contient plusieurs variables (ce qui sera généralement le case), l’option KEEP servira à limiter la sélection à la variable servant à définir START.

data pat (keep=start fmtname label);
   set pat_uniq (rename=(patref=start)); *(keep=patref);
   fmtname='PATREF';
   label='retenir';
run;

Si plusieurs valeurs identiques pour la variable START sont présentes dans votre fichier, vous devrez supprimer les doublons. L’étape suivante ne marchera pas sinon.

*proc sort data=pat nodupkey;
*by start;
*run;

Le data set est à présent convertit en format au moyen de l’option CNTLIN. Ce format est sauvegardé par défaut dans le catalogue FORMATS de la bibliothèque WORK.

proc format cntlin=pat;
run;

3. Sélectionner les observations de son choix

Ici, l’option WHERE est privilégiée à l’instruction IF pour des raisons de performance.

Ensuite, grâce à la fonction PUT, les valeurs de PATREF sont converties en RETENIR si elles sont présentes dans le data set PAT_UNIQ. Seules ces valeurs RETENIR sont gardées.

data ae_multi;
   set ae_multi (where=(put(patref,patref.) = 'retenir'));
run;

Voir le résultat :

proc print data=ae_multi;
run;

Deux méthodes pour sélectionner des données en se basant sur un second data set

Avec IF/SELECT/WHERE, OUTPUT/DELETE vous savez comment garder une partie des données disponible en fonction de la valeur de variables. Mais comment faire si les données servant de critère sont dans un autre data set ?

Voici un exemple :

• D’un côté, on a un data set SAS avec tous les effets secondaires (adverse events)  de tous les patients. Un patient peut avoir plusieurs effets secondaires et donc plusieurs lignes. Il peut aussi ne pas être présent s’il aucun effect secondaire n’a été enregistré.
• De l’autre côté on a un data set contenant seulement les patients répondant aux critères du protocol d’étude clinique (une ligne par patient).

Comment garder tous les effects secondaires de ces patients valides par protocole et seulement de ceux-là ?. SAS a deux possibilités :

1. un MERGE dans une étape data utilisé avec son option (IN=)
2. un PROC SQL avec la condition WHERE… IN ().

Vous trouverez un rappel sur le MERGE en lisant : « La base de la jointure de deux data sets avec MERGE« .

1. Les data sets AE_MULTI et PAT_UNIQ servent d’exemple

Une ou plusieurs lignes par patients dans le data set AE_MULTI : le data set AE_MULTI contient six effets secondaires (6 lignes d’observations) se référant à 4 patients (numéros : 1, 2, 4 et 5).

data ae_multi;
   input patref ae_id;
   datalines;
1 1
1 2
2 1
4 1
4 2
5 1
;
run; 

Une seule ligne par patient dans le data set PAT_UNIQ : le data set PAT_UNIQ contient trois patients (3 observations) : numéros 2, 3 et 4.

data pat_uniq;
   input patref;
   datalines;
2
3
4
;
run;

En d’autres termes, on souhaite garder les observations de AE_MULTI si et seulement si le patient est aussi enregistré dans le data set PAT_UNIQ. Trois observations sont à conserver : celles du patient 2 avec ses deux effets secondaires et celle du patient 4 avec son effet secondaire.

patref    ae_id

   2        1
   4        1
   4        2

2. Deux solutions

Dans ce cas du MERGE, on annote (flag) les données avec des variables temporaires (AE et PAT).

• Si la valeur de PATREF est présente dans le fichier AE_MULTI, alors AE=1, sinon AE=0.
• Si la valeur de PATREF est présente dans le fichier PAT_UNIQ, la variable PAT=1, sinon elle est égale à 0.

Si le patient est à la fois dans les deux data sets  (si AE=1 et PAT=1), alors les données sont envoyées (ouputted) dans le data set VERSION1.

Comme se sont les variables du data set AE_MULTI qui nous intéresse et non celles du data set PAT_UNIQ, on ne garde parmi les variables de PAT_UNIQ seulement la variable PATREF servant de lien et listée dans l’instruction BY.

data version1;
   merge ae_multi (in=ae)
         pat_uniq (in=pat keep=patref);
   by patref;
   if ae and pat;
run;

Avec la procédure SQL, on sélectionne toutes les données disponibles dans le data set AE_MULTI en précisant que les valeurs de la variable PATREF doit aussi être présentes dans une autre sélection, celle définie entre parenthèses.

proc sql;
   create table version2 as
      select *
      from ae_multi
      where patref in (select patref from pat_uniq);
quit;

Entre les deux méthodes, on notera deux différences :

1. Avec le merge, il est possible de lier des data sets ayant plus d’une variable commune (by pays centre patient;).
2. Avec la procédure SQL, on peut multiplier les données dans le WHERE. Par exemple, on pourrait à la fois choisir les patients qui sont dans le data set PAT_UNIQ et exclure les effets secondaires qui sont aussi présents dans un autre fichier que AE.

Savoir interpréter une note liée au MERGE

Dans un cas particulier, une jointure many-to-many avec un MERGE, SAS génère une note. Il est très important de savoir l’interpréter. En effet, il est fréquent de vouloir un FULL JOIN (PROC SQL) mais d’utiliser maladroitement le MERGE (Data Step). Je vous propose donc de voir la différence entre les deux approches.

Rappel : si dans chacun des deux data sets à fusionner, il y a des doublons pour les variables communes, il s’agit d’une jointure MANY-TO-MANY.

Données utilisées pour illustrer le sujet : voici deux data sets appelés FRST_MANY et SECOND_MANY avec chacun trois variables. Les variables X et Y sont présentes dans les deux data sets. Dans le premier data set, trois lignes d’observations ont pour valeur x=A, y=B. Dans le second data set, deux lignes d’observations ont pour valeurs x=A, y=B et deux autres x=B, y=D.

 frst_many       second_many
 a    x    y     x    y    b
 1    A    B     B    D    2
 2    A    B     B    D    8
 9    A    B     A    B    4
 3    C    D     A    B    3
 5    B    D     E    F    6

1. Premier Choix, le FULL JOIN de la PROC SQL : on veut que chaque ligne d’observations de FRST_MANY soit dupliquée s’il y a plusieurs lignes d’observations dans SECOND_MANY ayant les mêmes BY variables ; et inversement.

En d’autres termes, vu que x=A, y=B apparaît trois fois dans le premier data set et deux fois dans le second, il y aura six lignes d’observations après la fusion des jeux de données.

De même, vu qu’il y a une observation x=B, y=D dans le premier jeu et deux dans le second jeux, il y aura deux observations après la fusion et la variable a=5 du premier jeux sera dupliquée pour chaque ligne.

      final
 a    x    y    b
 1    A    B    3
 2    A    B    3
 9    A    B    3
 1    A    B    4
 2    A    B    4
 9    A    B    4
 5    B    D    2
 5    B    D    8
 3    C    D    .
 .    E    F    6

En terme de programmation SAS, cela donne :

proc sql;
   create table final as
   select a,
          coalesce(one.x,two.x) as x,
          coalesce(one.y,two.y) as y,
          b
   from   frst_many one
   full join
          second_many two
   on     one.x=two.x and
          one.y=two.y;
quit;

2. Second Choix, MERGE : pour chaque combinaison de BY variables, le nombre de lignes dans le data set final correspond au nombre de ligne le plus important parmi les deux data sets d’origine.

Dans ce cas, SAS génère la note suivante, très importante :

NOTE: MERGE statement has more than one data set with repeats of BY values.

Le mieux est encore de regarder l’exemple pour comprendre ce qui se passe.

Vu qu’il y a trois lignes d’observations pour x=A, y=B dans le premier jeu, et seulement deux dans le second, il y aura trois lignes d’observations au final pour ces BY variables. La deuxième et dernière valeur de la variable b du second data set (b=3) sera dupliquée pour les lignes supplémentaires.

Vu qu’il y a deux lignes d’observations pour x=B, y=D dans le second jeu et une seule dans le premier, la valeur de la variable a du premier data set (a=5) sera également dupliquée pour la ligne supplémentaire.

      final
 a    x    y    b
 1    A    B    4
 2    A    B    3
 9    A    B    3
 5    B    D    2
 5    B    D    8
 3    C    D    .
 .    E    F    6

Pour obtenir ce résultat, il faut passer par un MERGe et si besoin trier les données :

proc sort data=frst_many;
   by x y;
run;

proc sort data=second_many;
   by x y;
run;

data final;
   merge frst_many
         second_many;
   by x y;
run;
 

Annexe : syntaxe pour créer les data sets et ainsi tester la PROC SQL et le MERGE.

data frst_many;
   input a x $ y $;
   datalines;
1 A B
2 A B
9 A B
3 C D
5 B D
;
run;
 

data second_many;
   input x $ y $ b;
   datalines;
B D 2
B D 8
A B 4
A B 3
E F 6
;
run;

La base de la jointure de deux data sets avec MERGE

Tout d’abord, une jointure c’est quoi ? Une jointure est le rapprochement de plusieurs data sets pour n’en former plus qu’un. Maîtriser les jointures est primordial pour un programmeur. On peut citer deux raisons : fusionner des données est un travail récurrent ; une mauvaise jointure a des conséquences sur toute la suite du travail. La syntaxe de SAS peut paraître triviale. L’important est de connaître ses données et de savoir clairement ce qu’on attend à la fin. Prenez le temps qu’il faut pour acquérir cette compétence, vous ne le regretterez pas.

Les jointures peuvent être définies selon trois critères.

• Ajouter des variables : on distingue d’un côté les jointures où le but principal est d’empiler des observations les une en dessous des autres (SET, UNION,…) des jointures où le premier but est d’ajouter des variables (MERGE, JOIN,…).
• Avoir des variables communes aux deux data sets : selon les situations des variables communes doivent être précisées. Parfois, néanmoins, le but est différent et aucune variable commune n’est listée. On s’intéressera au cas le plus fréquent : des variables communes listées.
• Jointure dans un data set : enfin la syntaxe du data step (SET, MERGE) se distingue de celle de la procédure SQL. Dans le cas présent, on se concentrera sur celle du data step.

Objectif : En résumé, il s’agira de présenter la base de la jointure MERGE, propre au data step, où les variables communes sont listées dans une instruction BY. Voici donc pour commencer trois des quatre situations de base du MERGE avec des variables communes.

Exemples : Dans les exemples qui suivent, il y a deux data sets à chaque fois. Le premier a pour variable A, X, Y, et le second a les variables X, Y et B. Les variables X et Y sont les variables communes. Le résultat de la jointure est un data set nommé FINAL. En fin d’article, vous trouverez le code pour créer rapidement ces différents data sets sources.

1. Différents noms de jointure : si on a deux data sets qui ont une ou plusieurs variables communes, on aura le choix entre plusieurs jointures :

• ONE-TO-ONE : si dans chaque data set, cette variable (ou la combinaison de ces variables) n’apparaissent qu’une fois, la jointure des deux data sets sera une jointure ONE-TO-ONE.
• ONE-TO-MANY ou MANY-to-ONE : si un des deux data sets a pour cette variable plusieurs fois la même valeur, la jointure s’appellera ONE-TO-MANY ou MANY-to-ONE.
• MANY-TO-MANY : maintenant si dans chacun des deux data sets il y a des doublons pour les variables communes, il s’agit d’une jointure MANY-TO-MANY.

2. Des doublons dans aucun des data sets

Dans l’exemple ci-dessous, chacune des combinaisons x=,y= n’apparaît qu’une fois. Une jointure des deux data sets sera ONE-TO-ONE.

Avant deux data sets

   frst_one      second_one
 a    x    y     x    y    b
 1    A    B     B    D    2
 3    C    D     A    B    4
 5    B    D     E    F    6

Après un seul data set

      final
 a    x    y    b
 1    A    B    4
 3    B    D    2
 5    C    D    .
 .    E    F    6

Lister plus de deux data sets ONE-TO-ONE-TO-ONE-TO…: dans ce cas particulier, il est possible de lister plus de deux de data sets ayant tous des BY variables uniques.

2. Des doublons parmi les variables communes dans un et un seul des data sets.

S’il y a des doublons dans un des deux data sets, des lignes supplémentaires seront ajoutées. Et les observations de l’autre data sets seront dupliquées pour chaque doublons.

MANY-TO-ONE : Ici, c’est le premier data set qui a des doublons. La valeur b=4 est répétée pour chacun des x=A, Y=B du premier data set.

Avant deux data sets

  frst_many        second_one
 a    x    y      x    y    b
 1    A    B      B    D    2
 2    A    B      A    B    4
 9    A    B      E    F    6
 3    C    D
 5    B    D

Après un seul data set

      final
 a    x    y    b
 1    A    B    4
 2    A    B    4
 9    A    B    4
 3    B    D    2
 5    C    D    .
 .    E    F    6

ONE-TO-MANY : A l’inverse, ici, c’est le second data set qui a des doublons. C’est donc la valeur a=1 qui est répété pour chaque x=A, Y=B et a=5 pour chacune des combinaisons x=B, y=D du second data set.

Avant deux data sets

  frst_one       second_many
 a    x    y     x    y    b
 1    A    B     B    D    2
 3    C    D     B    D    8
 5    B    D     A    B    3
                 A    B    4
                 E    F    6

Après un seul data set

      final
 a    x    y    b
 1    A    B    3
 1    A    B    4
 5    B    D    2
 5    B    D    8
 3    C    D    .
 .    E    F    6

Après la jointure, il est possible de garder seulement certaines observations. Cela fera l’objet d’un autre article. Jeudi prochain, nous verrons le cas particulier du MANY-TO-MANY.

3. En langage SAS, cela donne quoi?

Quelque soit le type de jointure parmi les trois listées (ONE-TO-ONE, MANY-TO-ONE et ONE-TO-MANY) la syntaxe du MERGE est la même. Je choisi dans cet exemple de nommer le premier data set FRST_… et l’autre SECOND_… Le data set créé s’appelle FINAL. Vous trouverez en bas de l’article le code pour créer ces data sets selon qu’il y a des doublons (FRST_MANY, SECOND_MANY) ou pas (FRST_ONE, SECOND_ONE).

L’instruction BY implique que les données soient préalablement triées de la même manière. Si vous êtes sûr que l’ordre est bon, vous pouvez vous passer d’un tri. Mais c’est rare. Du coup, on a le choix entre un PROC SORT ou un ORDER BY (« GROUP BY ») dans une PROC SQL. 

proc sort data=frst_…;
   by x y;
run;

proc sort data=second_…;
   by x y ;
run;

data final;
   merge frst_… second_…;
   by x y;
run;

Annexe : créer les data sets pour tester les jointures

data frst_one;
   input a x $ y $;
   datalines;
1 A B
3 C D
5 B D
;
run;

data second_one;
  input x $ y $ b;
   datalines;
B D 2
A B 4
E F 6
;
run;

data frst_many;
   input a x $ y $;
   datalines;
1 A B
2 A B
9 A B
3 C D
5 B D
;
run;

data second_many;
   input x $ y $ b;
   datalines;
B D 2
B D 8
A B 3
A B 4
E F 6
;
run;

Empiler des data sets

Ajouter des lignes à un data set en utilisant celles d’un autre data set, c’est possible avec SAS. Selon les particularités du data set, une ou plusieurs méthodes sont disponibles. Trois data sets en fin d’articles sont disponibles pour tester les différentes méthodes.

1.  L’instruction SET dans un data step offre probablement le plus de flexibilité 

Groupées les données : La souplesse de l’instruction SET vient notamment de l’instruction BY. Sans cette instruction, toutes les données du premier data set sont lues et ajoutée au data set final. Puis, seulement après les données du data set suivant sont ajoutées. Si on veut que les lignes apparaissent dans un ordre précis défini par une instruction BY, les données doivent être préalablement triées dans cet ordre. 

Deux data sets et plus : en outre, plus de deux data sets peuvent êtes mis bout à bout. Les premières données qui s’afficheront seront celles du premier data set listé dans l’instruction SET.

Autorise un nombre de variables différent : A l’exception des variables listées dans une instruction BY, les data sets n’ont pas besoin d’avoir les même variables. La variable présente dans seulement certains data sets sera présente au final. Des valeurs manquantes seront ajoutées si besoin.

Même longueur et type pour les variables communes : Il est néanmoins important que certains attributs des variables présentes dans plusieurs data sets soient identiques.

• Longueur : SAS utilisera la longueur de la première variable lue. Si la longueur de la seconde variable est plus grande, les valeurs, textes notamment, seront tronquées.
• Type numérique ou caractère: L’attribut sur le type de la variable est aussi essentiel. SAS aura des problèmes en lisant à la fois des variables numériques et caractères du même nom.
• Nom de variable : Si les variables ont des noms différents. Il est possible de les renommer préalablement avec l’option RENAME.

Un nom de data set au choix : par ailleurs, on peut donner un nouveau nom au data set créé

La syntaxe du data set toujours disponible : enfin, les mêmes manipulations qu’avec une instruction SET avec un seul data set peuvent êtes effectuées. Les options (IN=) sont disponibles pour établir des conditions basées sur le data set source.

data seq_all;
   set seq1 seq2 seq3;
run;

SAS Online Doc : The SET Statement

2. La procédure DATASETS (et PROC APPEND) peut s’avérer plus performante

La procédure DATASETS dispose de l’instruction APPEND. Cette procédure est plus récente que sa jumelle PROC APPEND. Elle propose d’autres instructions fortement utiles comme COPY et DELETE.

• Nombre de data sets d’entrée: Ici, il est impératif d’avoir deux et seulement deux data sets pour une jointure donnée.
• Structure des data sets d’entrée : Chaque data set doit avoir les mêmes variables.
• Nom du data set de sortie : Le data set final portera le nom du data set listé dans la base.
• Performances : APPEND pourra s’avérer plus performant si le data set défini dans la base est large. En effet, dans ce dernier cas, seul les observations du deuxième data set sont lues intégralement.

Priorité dans l’usage des bibliothèques : Dans l’exemple suivant, le data set SEQ1, SEQ2 et SEQ3 sont dans la bibliothèque WORK. Si aucune bibliothèque n’est donnée dans l’instruction APPEND, SAS utilise celle définie dans l’instruction PROC DATA SETS. Et comme aucune n’y est précisée, cela revient à utiliser la bibliothèque temporaire (WORK le plus souvent).

proc datasets;*lib=work;
   append base=seq1 data=seq2;
   *append base=work.seq1 data=work.seq2;
   append base=seq1 data=seq3;
run;

Voici la même manipulation avec PROC APPEND.

proc append base=seq1 data=seq2;
*proc append base=work.seq1 data=work.seq2;
run;
proc append base=seq1 data=seq3;
run;

Vous pouvez vous reporter à la documentation SAS :The DATASETS Procedure et consulter l’instruction APPEND. On y rappelle notamment quand le data set de la base (BASE=), les options DROP, KEEP et RENAME ne sont pas exécutées.

3. Au final pas de doublons avec l’UNION d’une PROC SQL

Important : L’UNION de deux data sets avec une PROC SQL enlèvera les doublons. Il est donc important de savoir s’il y a des doublons qui sont à enlever ou non.

Ici, on peut faire l’union de plus de deux data sets. Ils devront néanmoins tous avoir les mêmes variables.

proc sql;
   create table seq_all as
      select * from seq1
   union
      select * from seq2
   union
      select * from seq3;
quit;

4. Insérer de nouvelles observations extraites d’un autre data set: 

Il est possible aussi d’ajouter les observations avec l’instruction INSERT INTO. Le data set d’origine est alors actualisé. Dans l’exemple ci-dessous on ajoute seulement les observations des data sets SEQ2 et SEQ3 s’il si le test mesure la pression systolique (Systolic Blood Pressure, SBP).

NOTE : Il est important de sélectionner les observations à ajouter d’un data set différent de celui à mettre à jour. Sinon, SAS risque de rencontrer des problèmes. Voir la SAS Online Doc: INSERT Statement pour plus de précisions.

proc sql;
  insert into seq1
     select *
     from seq2
     where test_nom=‘SBP’;
  insert into seq1
     select *
     from seq3
     where test_nom=‘SBP’;
quit;

Annexe :

data seq1;
   length test_nom $3 test_unit $4;
   input test_nom $ test_seq test_val test_unit $;
   datalines;
SBP 1 120 mmHg
DBP 1 80 mmHg
DBP 1 80 mmHg
;
run;

data seq2;
   length test_nom $3 test_unit $4;
   input test_nom $ test_seq test_val test_unit $;
   datalines;
SBP 2 115 mmHg
DBP 2 85 mmHg
;
run;

data seq3;
   length test_nom $3 test_unit $4;
   input test_nom $ test_seq test_val test_unit $;
   datalines;
SBP 3 117 mmHg
DBP 3 81 mmHg
;
run;

Produit cartésien : un notion pas si barbare !

Enfant, vous avez peut-être joué à ce jeu où les mots d’une colonne sont à relier avec les mots d’une autre colonne. Pour s’amuser, on peut tracer toutes les combinaisons possibles. Avec 4 valeurs à gauche et 3 valeurs à droite, 12 traits sont tracés. 

Le produit cartésien c’est la même chose : créer toutes les combinaisons possibles. Ce mot fait parti du vocabulaire pour combiner deux bases de données.

Proc SQL ou data step ? : Seule la procédure SQL permet de faire cette manipulation occasionnelle. Le MERGE du data step ne peut pas.

1. Un exemple : dans l’exemple ci-dessous, le premier jeu de données a trois lignes d’observations et le second en a deux.

–ONE–
x     y
1    11
2    22
3    33

—–TWO—–
 a    b    c
 9    8    3
99    5    2

Pour chaque ligne du premier jeu, on veut les deux observations du second jeu. Cela donne un nouveau data set avec 3*2 lignes d’observations.

x  y  a b c
1 11  9 8 3
1 11 99 5 2
2 22  9 8 3
2 22 99 5 2
3 33  9 8 3
3 33 99 5 2

2. Une procédure SAS : la procédure SQL liste les variables des deux jeux de données. Le nom des deux data sets  sont séparés pas une virgule.

proc sql;;
   select x, y, a, b, c
   from one, two;
quit;

Oh attention danger avec MERGE et IF

Oh attention danger ! Cela me fait penser à une chanson de Sardou. Mais c’est aussi la phrase qu’il faut avoir à l’esprit quand on veut combiner par un MERGE deux jeux de données et en même temps mettre à jour une variable avec une condition IF.

Lorsque MERGE et IF sont utilisés dans un seul data step pour actualiser une variable, le programmeur a souvent en tête un MERGE puis un IF. C’est à dire d’équivalent de deux data steps, un pour le MERGE et un pour IF. Hors dans les faits, SAS agit différemment.

Il est donc essentiel de savoir repérer la situation pour utiliser deux steps au lieu d’un. Pour cela je vous propose de passer par un exemple.

1. Les données d’origine : je vous propose en exemple deux jeux de données appelés ONE et TWO ayant une variable commune ID et une variable unique, X dans le premier cas, Y dans le second. On compte plusieurs ID identiques dans ONE. Chaque ID est unique dans TWO. On fait donc un merge MANY-TO-ONE.

—ONE—
id     x   
 1    999
 1    888
 1    777
 2    66

—TWO—
id     y
 1    10
 2    12

2. La méthode claire en deux étapes : quand x est égal à 999, l’observation de Y est mise à jour. Dans l’exemple, seule la première observation de Y est concernée.

Dans un premier temps, les données sont combinées.

data safe;
   merge one two;
   by id;
run;

id     x      y
 1    999    10
 1    888    10
 1    777    10
 2    666    12 

Dans un second temps, la variable Y est mise à jour.

data safe;
   set safe;
   if x=999 then y=999;
run;

id     x      y
 1    999    999
 1    888     10
 1    777     10
 2    666     12 

3. La méthode risquée : en regardant le résultat, on remarque la  mise à jour de la variable Y ne concerne plus seulement la première observation mais est étendue à l’ensemble des observations de l’ID concerné (ID=1)-

data danger;
   merge one two;
   by id;
   if x=999 then y=999;
run;

id     x      y
 1    999    999
 1    888    999
 1    777    999
 2    666     12

En conclusion, les deux notations sont correctes. Il est seulement important de savoir clairement le résultat qu’on recherche. Le premier cas reste le plus fréquent. Lorsqu’il s’agit de mettre à jour une variable une fois un MERGE terminé via une IF condition, il faut le faire en deux étapes pour éviter des sorties différentes de ses attentes.

Retrouver la valeur suivante avec LAG ou un MERGE

 

Dans un précédent article, la fonction LAG a été présentée. Il s’agissait alors de créer une nouvelle variable contenant la valeur précédente d’une variable existante. Pour récupérer la valeur suivante cette fois, je vous propose deux méthodes : utiliser la fonction LAG avec un tri décroissant ou faire un MERGE.

1. Trier par ordre décroissant et utiliser la fonction LAG : pour retrouver l’information suivante plutôt que l’information précédente, il vous suffit de trier les observations par ordre décroissant au préalable. Dans notre exemple précédent, (1, 2, 3, 4, 5) devient (2, 3, 4, 5, .). Afin de retrouver l’ordre d’origine, vous aurez besoin d’un second tri après. Cette fois-ci, c’est la dernière observation par patient qui sera manquante puisqu’il n’y a pas d’observation après pour un patient donné. En SAS, cela veut dire qu’il faudra utiliser LAST après le tri final ou FIRST avant le tri final.

proc sort data=test;
   by patient descending dt;
run;

data next_dt;
  set test;
  by patient;
  next_dt=lag(dt);
  if first.patient then next_dt=.;
run;

proc sort data=next_dt;
   by patient dt;
run;

2. Extraire la valeur suivante avec un MERGE : en extrayant la variable date sans sa première observation dans un jeu de donnée, on obtient la liste des valeurs suivantes. Il suffit ensuite d’ajouter ces valeurs au jeu de données d’origine pour avoir la 2ème date comme valeur suivante pour la première observation, etc. Un MERGE sans instruction BY est suffisant. Comme avant, on actualise la dernière observation par BY variable via LAST.

proc sort data=test;
   by patient dt;
run;

data next_dt;
   merge test
         test (firstobs=2 rename=(dt=next_dt));
run;

data next_dt;
   set next_dt;
   by patient;
   if last.patient then next_dt=.;
run;