Discussion :

[AllXS7117]

Bonjour,

J'ai deux tables qui n'ont pas le même nombre de lignes par identifiant. Je voudrais récupérer une variable de la table E0 où il y a moins de ligne par identifiant dans la table E1 qui en contient beaucoup plus. L'idée est de respecter le nombre de ligne par identifiant de la table E0, et d'avoir des données manquantes sur le reste des observations par identifiant sur E1

Aucun des types de jointures (gauche, droite, full, inner) ne marche évidemment dans cette situation. Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
proc sql;
create table f as
select a.IDX2, a.IDX, a.ndem, b.nbheur
from E1 a right outer E0 b
on a.IDX2=b.IDX2;
quit;

J'ai aussi rajouté distinct après select, mais ça n'a pas fonctionné non plus. Peut-être faut-il rajouter distinct seulement pour les variables de la table E0 pour que SAS prenne en compte le nombre de lignes ? Comment faire dans ce cas ?

Quelqu'un saurait s'il y a un type de jointure qui corresponde à cette situation ?

[Stackware]

Bonjour,
Je ne suis pas sûr de bien comprendre la problématique. Pourrais-tu donner un exemple avec quelques lignes des 2 tables et le résultat attendu? Ça aiderait pas mal.
Une jointure outer join ne permet que d'afficher des lignes présentes dans une des tables avec les variables de la seconde à null, en plus des lignes de la jointure classique.
Pour prendre en compte le nombre de lignes, on est obligé d'en passer par un group by et l'ajout de count(*), avec un filtre having sur la colonne count(*).
Intuitivement je me dis que ce que tu veux obtenir n'est peut-être pas possible en une seule requête, mais avant de proposer une solution j'attends ton exemple

[AllXS7117]

Merci de ta rapidité !
j'ai voulu essayer les group by having count, mais mon problème est de compter le nombre de ligne de la table E0 pour renseigner le même nombre de ligne sur E1 avec la variable NBHEUR, pas de faire une restriction sur une variable

Table E0 :

ID NBHEUR
1-1 10
1-1 20

1-2 05

Après la jointure, j'ai la variable NBHEUR renseignée seulement sur le même nombre de ligne que la table E0 par identifiant. voila ce que ça donne sur la table E1 :

Table E1 :

ID BHEUR
1-1 10
1-1 20
1-1 .
1-1 .

1-2 05
1-2 .
1-2 .


N'y a t'il pas moyen d'utiliser la fonction range (*) par identifiant 1-1, 1-2 etc... pour dire à sas de retenir le nombre de ligne de E0 ?

[Stackware]

Désolé d'insister, mais je n'ai pas vu le résultat attendu?

[AllXS7117]

J'ai 2 tables :

Table E0 :

ID NBHEUR
1-1 10
1-1 20

1-2 05


Table E1 : ma table de travail principal

ID var 1 var 2
1-1
1-1
1-1
1-1

1-2
1-2
1-2

Je veux dans ma table E1 le même nombre d'heures renseignées que dans E0 :

Table E1 :

ID NBHEUR
1-1 10
1-1 20
1-1 .
1-1 .

1-2 05
1-2 .
1-2 .


J'espère que c'"est plus clair comme ça

[Stackware]

Bon,

Ton problème n'est pas simple du tout. On a des doublons dans la table E1, que l'on doit compléter avec les données de la TABLE E0, sans pouvoir identifier les lignes à mettre à jour.
Je ne vois pas de solution, sauf programmatique (en mode ligne à ligne), donc pas en proc sql.
De plus la table E1, qui avait var1 et var2 en plus d'ID, voit var1 devenir NBHEUR, et var2 disparaitre... On pourrait s'en tirer s'il n'y avait qu'une seule ligne concernant ID dans la table E1, en cumulant les heures. On pourrait peut-être s'en tirer s'il y avait une date, différente à chaque fois, qui, associée avec ID serait une clé unique : il suffirait d'ajouter les lignes "manquantes" de E0 par un UNION et une clause restrictive NOT EXIST sur E0 vis à vis de E1.
Donc, là, je ne sais pas faire en SQL.

[AllXS7117]

On pourrait peut-être s'en tirer s'il y avait une date, différente à chaque fois, qui, associée avec ID serait une clé unique : il suffirait d'ajouter les lignes "manquantes" de E0 par un UNION et une clause restrictive NOT EXIST sur E0 vis à vis de E1.
Donc, là, je ne sais pas faire en SQL.

je pense que tu as trouvé la solution. Sur E0 j'ai effectivement une date différente par ligne. De là, j'ai fait un nouvel identifiant à partir d'un compteur, ce qui fait que ma table E0 ressemble à ça désormais :

ID NBHEUR
1-1-1 100
1-1-2 200
1-1-3 40
1-1-4 50

1-2-1 10
1-2-2 30


peut-on faire la méthode dont tu parles avec seulement la table E0 qui a une date par ligne ? Si oui, peux-tu illustrer avec un programme pour l'Union et la clause NOT EXIST ??



P.S : ne prend pas l'exemple de table que j'ai donné au pieds de la lettre, les variables var 1 et var 2 ne disparaissent pas sur E1 après la jointure, elles sont juste là pour dire que c'est ma table principale de travail. Pareil pour l'exemple ci-dessus, c'est juste pour illustrer, j'ai des millions d'observations en réalité !

[m.brahim]

Bonjour,

Je te propose cette solution:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
 
data e0;
input id$ heure;
cards;
1-1 10
1-1 20
1-2 05
;
run;
 
DATA e00 ;
SET e0 ;
obs1=_n_;
run; 
 
data e1;
input ID$;
cards;
1-1 
1-1 
1-1
1-2
1-2
1-2
;
run;
 
DATA e11;
SET e1 ;
obs2=_n_;
run; 
 
 
DATA work.complet (drop=obs1 obs2) ;
  merge e11 e00; 
  by id;
  if lag(obs1) =obs1 then heure=.;
RUN ;

Cordialement

[AllXS7117]

en fait, j'ai des millions d'observations, je ne peux pas essayer ta solution M.Brahim. Merci quand même!

Je vais essayer l'Union corr de E0 sur E1

[Stackware]

Salut,

Je ne pense pas que ça répondra à ton problème. L'union corr gardera, s'il y en a dans la table de travail, les lignes du type ID, DATE, NBHEUR=null. Ce qu'il faut, c'est que si de telles lignes existent, il faut les mettre à jour. Il faut également ajouter les lignes manquantes.
Ces lignes manquantes ne pourront être ajoutées avec un union corr qu'après avoir mis les lignes à jour lors d'un premier passage.
Ex:
T0
1 date1 10
1 date2 20
2 date1 10

T1
1 date1 .
1 date2 20
2 date1 .
2 date2 50

un union corr brut donnera
1 date1 10
1 date2 20
2 date1 10
1 date1 .
2 date1 .
2 date2 50

donc en 3 passes (mises à jour, ajout T0 et ajout T1)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
create table W as 
select a.id, a.date, a.nbheur from T0 a, T1.b
where a.id = b.id and a.date = b.date and a.nbheur <> b.nbheur
union
select a.id, a.date, a.nbheur from T0 a
where (a.id, a.date) not in (
select distinct (b.id, b.date) from T1 b
)
union
select b.id, b.date, b.nbheur from T1 b
where (b.id, b.date) not in (
select distinct (a.id, a.date) from T0 a
)

On peut certainement faire bien mieux et être plus malin que ce code un peu basique, mais il a l'avantage de bien séparer les 3 étapes pour qu'elles soient visibles.
Encore une fois, je suis certain que quelqu'un sur ce forum sera à même de sortir une requête qui ira 100 fois plus vite

[sasadm]

une solution grace au hash.
attention version 9.2 minimum requise. Si version 9.1.3 alors il faudra adapter le chargement du hash.

il faut egalement que la table e0 ne soit pas trop volumineuse.

EDIT: on peut bien entendu garder le principe de la construction du rang et faire la jointure avec une methode plus traditionnelle. Genre un merge. Dans ce cas creer une vue pour e1 sur le meme principe que _num_e0 puis merger les 2 vues.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
data e0;
input id$ heure;
cards;
1-1 10
1-1 20
1-2 05
;
run;
 
data _num_e0 / view=_num_e0;
 
set e0;
by id;
/* rang de l'heure a ajouter */
	if first.id then _num_=0;
	_num_ + 1;
run;
 
data e1;
input id $;
cards;
1-1 
1-1 
1-1
1-2
1-2
1-2
;
run;
 
data res;
/* chargement des e0 dans 1 objet hash */
if _n_=1 then do;
       declare hash h_e0(dataset:"_num_e0");
 
	   /* def de la cle du join */
       h_e0.definekey("id", "_num_");
 
	   /* definition des  donnee a ajouter */
		h_e0.definedata("heure");
 
       h_e0.definedone();
end;
 
	set e1;
 
	/* declaration obligatoire de la var a ajouter dans le pdv */
	length  heure 8; 
 
	by id;
 
	/* rang de la donnee e1 */
	if first.id then _num_=0;
	_num_ + 1;
 
	/* jointure rang a rang */
	rc=h_e0.find();
 
run;

[hossward]

Bonjour,
Une solution : les deux tables proposées par SASADM et la PROC SUMMARY.
Grace à la puissance de cette proc, on pourrait accélérer aussi le traitement des données.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
data E0;
input id$ heure;
cards;
1-1 10
1-1 20
1-2 05
;  run ;
 
DATA _NUM_E0 / view=_NUM_E0 ;
SET e0 ; by id notsorted ;
var1=_n_ ;
var2=var1+10 ;
if first.id then  rang=0 ; rang+1 ;
run;  
 
data E1 ;
input ID$  ;
cards ; 
1-1
1-1
1-1
1-2
1-2
1-2
;  
 
DATA _NUM_E1 / view=_NUM_E1 ;
SET e1 ; by id notsorted ;
if first.id then  rang=0 ; rang+1 ;
run ;   
 
proc summary nway data=_NUM_E0 classdata=_NUM_E1 ;
   by id;
   class rang ;
   /* Dans l'IDGROUP, on peut mettre les variables à ajouter */
   output out=finale (drop=_type_ _freq_ rang)  idgroup(out(heure var1 var2)=) ;
run;

Ward