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;