Discussion :

[chadi18]

Bonjour à tous,

Je bloque sur un code et je voudrais votre aide si possible

J'ai 2 tables, et je voudrais chercher dans la 1ère table la valeur la plus proche d'une variable dans la 2ème table.

Table 1 :
var1 var2
a 0.01
b 0.02
c 0.03

Table 2 :
var3 var4
0.018 ?

0.02 étant la valeur la plus proche de 0.018, je veux qu'il me met "b" dans var4

Quelqu'un pourra m'aider ?
Merci d'avance

[RemiBousquet]

En voici un solution, à base d'étape data, de tri et de macro variables.
Si tes volumes sont raisonnables, ça devrait suffire. Sinon, il faudrait optimiser (avec du SQL?).

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
/* 0- Construction des tables de test table1 et table2 */
data table1 ;
    input var1 $ var2 ;
    cards ;
    a 0.01
    b 0.02
    c 0.03
    ;
run ;
 
data table2 ;
    var3 = 0.018 ;
run; 
 
/* 1- Identification de la valeur à approcher */
data _NULL_ ;
    set table2 ;
    call symputx ("VAR3", var3) ;
run ;
%put %nrstr(&VAR3.) = &VAR3. ;
 
/* 2- Calcul des ecarts entre var2 et la valeur à approcher */
data table3 ;
    set table1 ;
    ecart_var2_var3 = abs(var2 - &var3.) ;
run ;
proc sort data = table3 ;
    by ecart_var2_var3 ;
run;
 
/* 3- Identification de l'observation la plus proche */
data _NULL_ ;
    set table3 (obs=1);
    call symputx ("VAR1", var1) ;
run ;
%put %nrstr(&VAR1.) = &VAR1. ;
 
/* 4- Ajout de l'information dans table2 */
data table2 ;
    set table2 ;
    var4 = "&VAR1."; 
run ;

[chadi18]

Merci beaucoup

Mais il y a juste 2 petites précisions:

- J'utilise SAS 8.02
- Je travaille sur une table de 5 millions observations, donc on peut dire que c'est volumineux...

[RemiBousquet]

AVec SAS 8.02, j'imagine qu'il faut remplacer call symputx ("MACRO", Variable) par un truc du genre call symput ("MACRO", strip(put(VARIABLE,8.2))) ;

Pour les 5 millions d'observation, si dans table 2 tu as une seule ligne, ça doit rester largement jouable.
Peut être un keep = var1 ecart_var2_var3 à rajouter dans l'étape 2. Et des options de la proc sort pour accélerer le tri.

[Manoutz]

la version SQL...

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
/* 0- Construction des tables de test table1 et table2 */
DATA table1 ;
    input var1 $ var2 ;
    cards ;
    a 0.01
    b 0.02
    c 0.03
    ;
run ;
 
DATA table2 ;
    var3 = 0.018 ;
run; 
 
/* 1- Identification de la valeur à approcher */
DATA _NULL_ ;
    SET table2 ;
    call symputx ("VAR3", var3) ;
run ;
 
proc sql;
	select strip("&var3.") as VAR3, VAR1 as VAR4
	from TABLE1
	having abs(input("&var3.",best12.)-VAR2)=min(abs(input("&var3.",best12.)-VAR2))
	;
quit;

reste à voir quelle méthode est la plus performante.

Bon courage,

Manoutz

[RemiBousquet]

Le sql sera plus rapide certainement.

Un point d'attention : comment gérer le cas où plusieurs valeurs sont les plus proches ?
Par exemple, si var3 = 0.015, "a" et "b" sont tous les deux à une même distance de 0.005.
Dans ce cas, une règle sera à implémenter dans le code.

[Manoutz]

Le sql sera plus rapide certainement.

Je pense aussi, un retour de chadi18 sera le bienvenu

Un point d'attention : comment gérer le cas où plusieurs valeurs sont les plus proches ?
Par exemple, si var3 = 0.015, "a" et "b" sont tous les deux à une même distance de 0.005.
Dans ce cas, une règle sera à implémenter dans le code.

exact

[chadi18]

Manoutz ta méthode marche trop bien sur table1 et table2, par contre je voulais te demander si ca marchera sur les vrais tables où table1 contient 113 observations et 3 variables, et table2 contient 5 millions d'observations à peu près et 145 colonnes dans SAS 8.02 ? et est ce que je dois trier table1 au début?

Et comment faire pour appliquer ce code sur la totalité des 5 millions de lignes de table2 et non pas seulement sur une seule ligne et le stocker dans la table? Parcque ton code m'affiche le résultat d'une seule ligne dans Output, mais le stoque pas dans table2...

Merci beaucoup pour tous encore une fois.

[Manoutz]

ca marchera sur les vrais tables où table1 contient 113 observations et 3 variables, et table2 contient 5 millions d'observations à peu près et 145 colonnes dans SAS 8.02 ?

comment connaitre la vauer de var3? ton exemple précédent est mal choisi alors...

et est ce que je dois trier table1 au début?

non, c'est un des intérêts de la sql

Et comment faire pour appliquer ce code sur la totalité des 5 millions de lignes de table2 et non pas seulement sur une seule ligne et le stocker dans la table? Parcque ton code m'affiche le résultat d'une seule ligne dans Output, mais le stoque pas dans table2...

merci de clarifier ce que tu veux

[chadi18]

Voila une copie des vrais tables:

table1: (113 lignes)
var1 var2
1 0.0003818673
2 0.0002714304
.
.
.

table2: (5 millions lignes)
... var3 ... var_resultat
... 0.0006015165 ... ?
... 0.0005590248 ... ?
.
.
.

Il faut que pour chaque valeur de "var3" dans table2, il cherche la valeur la plus proche dans table1 "var2", et écris la valeur correspondante de "var1" dans une variable que j'ai nommé "var_resultat" sous table2 :o

Merci

[Manoutz]

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
DATA table1 ;
    input var1 $ var2 ;
    cards ;
    a 0.01
    b 0.02
    c 0.03
    ;
run ;
 
DATA table2 ;	
    input var3  ;
    cards ;
		0.0006015165 
		0.0205590248 
		;
run; 
 
proc sql;
	create table SOLU as
	SELECT  B.VAR3, A.VAR1 AS VAR4, A.VAR2
	FROM TABLE1	as A
		right join TABLE2 as B
		on B.VAR3 ne .
	group by B.VAR3
	having abs(B.VAR3-A.VAR2)=min(abs(B.VAR3-A.VAR2))
	;
quit;

[chadi18]

ca marche parfaitement

merci beaucoup

juste une derniere chose, est ce que je dois trier table1 sur var2 avant ?

et toc Résolu

[Manoutz]

Non, comme je te le disais avant, tu n'as pas à trier. la magie du sql!

[chadi18]

Merci

[datametric]

Peut -être pour éviter une problème de fusion avec le RIGHT JOIN, je mettrai un CROSS JOIN.

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
DATA table1 ;
    input var1 $ var2 ;
    cards ;
    a 0.01
    b 0.02
    c 0.03
	d 0.04
    ;
run ;
 
DATA table2 ;
    input var3;
    cards ;
    0.015
    0.023
    0.029
    0.031
    ;
run ;
 
proc sql;
create table result (keep=var3 var1) as 
select b.* , a.*, abs(var3-var2) as ecart
from table1 a cross join table2 b
group by var3
having ecart=min(ecart)
;
quit;

[Manoutz]

Il faut que pour chaque valeur de "var3" dans table2, il cherche la valeur la plus proche dans table1 "var2", et écris la valeur correspondante de "var1" dans une variable que j'ai nommé "var_resultat" sous table2

autrement dit on se base sur les valeurs présentes dans table2, donc je suis parti sur un right join.

je connais pas le cross join, ca correspond à quoi? Intuitivement je fais correspondre ca à une inner join. C'est quoi la subtilité?

Merci

Manoutz

[datametric]

C'est le contraire de l'inner join car le cross join crée un carré latin (ou un produit cartésien) c'est-à-dire tous les croisements possibles entre deux variables des tables listées.
C'est pratique pour créer des tables de références contenant tous les croisements possibles de certaines variables d'études. Une fois cette table construite, tu peux y joindre des données tierces.

Pour visualiser imagine une table avec 1 ligne par année et une seconde table avec 12 lignes correspondant au 12 mois de l'année.
pour chaque année il va coller les 12 mois de la seconde table directement car aucune clef n'est spécifiée.

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
data mois;
do mois=1 to 12;
output;
end;
run;
 
data annee;
do annee=2000 to 2010;
output;
end;
run;
 
proc sql;
create table table_ref as 
select *
from annee cross join mois;
quit;

[Manoutz]

Ok merci c'est dans l'esprit du full join alors...

Ceci étant je pense que (je me répète je sais..) dans ce cas le right join suffit

[chadi18]

Merci pour l'info

Mais en tout cas j'ai un problème de resource vu que ma table2 fait 5 millions d'observations et 3.5 gb en taille, donc avec les produits cartésiens fait dans vos 2 méthodes j'ai pas réussi à faire tourner le code sur mon poste (pas assez de resources), faut que je trouve un autre poste avec 50 gb au minimum je pense pour utiliser.

Merci en tout cas, vos codes marchent parfaitement sinon.

[Manoutz]

c'est pas que surprenant!

5 millions * 113 (lignes table 1), ca fait une table de plus de 500 millions d'observations!