Alternative plus rapide au "merge by" et PROC SORT [Résolu]

[Misspatate]

Bonjour à tous

Je dois traiter des tables avec énormément d'observations, auriez-vous une solution beaucoup plus rapide pour les "merge by" que de passer par le chemin classique qu'est de proc sorter les deux tables avant de les merger?
En effet, les "proc sort" me demandent des temps de calculs assez longs et le fait de rentrer dans une data est aussi très très long!
Je pensais à du SQL, mais je ne m'y connais pas trop à vrai dire...

Si vous avez une solution

Merci beaucoup et bonne journée!!!

[MEGAMIND2]

Salut Misspatate,
Tu pourrais poster ton code avec le merge pour te donner une alternative en SQL.

[Misspatate]

Coucou!! Voici le code

Code :

1
2
3
4
5
6
7
8
9
10
11
	proc sort DATA=maBiblio.psdd;
		BY numero3 sim;
	run;
	proc sort DATA=maBiblio.fz;
		BY numero3 sim;
	run;
	DATA maBiblio.psdd;
		merge maBiblio.psdd (IN=r) maBiblio.fz;
		BY numero3 sim;
		IF r;
	run;

[s_a_m]

Hello,
tes tables sont elles sous un SGBD? si oui , tu soumis la requête SQL à la base c'est plu rapide.
tes tables sont elles indexées ? si non tu crée des indexes sur les colonnes de merge et le TRI sur les cléfs de joindures devront être plus rapide.

[MEGAMIND2]

Ce que ça donne en SQL

Code :

1
2
3
4
5
PROC SQL;
	CREATE TABLE maBiblio.psdd AS SELECT a.*,b.* 
	FROM maBiblio.psdd  AS a LEFT JOIN maBiblio.fz  AS b 
	ON a.numero3=b.numero3 AND a.sim=b.sim;
QUIT;

[Misspatate]

Merci beaucoup!! Je vais tester pour voir si c'est plus rapide...

[Misspatate]

Est-ce grave ce warning?

Code :

1
2
3
WARNING: This CREATE TABLE statement recursively REFERENCES the target TABLE. A consequence of
         this IS a possible DATA integrity problem.
WARNING: Variable sim already EXISTS ON file MABIBLIO.PSDD.

Visiblement, cela fonctionne bien mais j'espère juste que ce Warning n'est pas important... (J'ai fait la proc SQL, qu'avec la variable sim ici)

En tout cas, merci beaucoup

[Misspatate]

J'ai une autre question, est ce que a et b sont considérées comme des tables temporaires?

[jerome_pdv2]

a et b sont internes à ta requète sql , elles n'existent pas ailleurs à ma connaissance.


Dans le cas de très grosses tables triée à l'origine tu peux l'indiquer également à la proc sql pour parfois accélérer les choses

En reprenant le code de Brice

Code :

1
2
3
4
5
6
 
PROC SQL;
	CREATE TABLE maBiblio.psdd AS SELECT a.*,b.* 
	FROM maBiblio.psdd  (sortedby= numero3 sim) AS a LEFT JOIN maBiblio.fz (sortedby=numero3 sim) AS b 
	ON a.numero3=b.numero3 AND a.sim=b.sim;
QUIT;

Ou seulement l'une des variables voir une seule table

Code :

1
2
3
4
5
6
 
PROC SQL;
	CREATE TABLE maBiblio.psdd AS SELECT a.*,b.* 
	FROM maBiblio.psdd  (sortedby= numero3) AS a LEFT JOIN maBiblio.fz  AS b 
	ON a.numero3=b.numero3 AND a.sim=b.sim;
QUIT;

Selon les cas cela pourra encore te faire gagner du temps ou pas.

[s_a_m]

le Warning te dit que t'as remplacé ta table maBiblio.psdd.
C'est normal tu n'as pas changé le nom de la table en sortie.

[s_a_m]

A et B s'appel des alias , sa t'évite d'écrire col1.TABLE1,COL2.TABLE2, ...
tu renome temporairement TABLE1 et TABLE2 par A et B.

[MEGAMIND2]

la table maBiblio.psdd est générée à partir d'elle même, c'est normal que tu as un Warning.ps: Je ne connaissais pas ce trie dans du sql, je vais tester donc.

[Misspatate]

Envoyé par s_a_m

Hello,
tes tables sont elles sous un SGBD? si oui , tu soumis la requête SQL à la base c'est plu rapide.
tes tables sont elles indexées ? si non tu crée des indexes sur les colonnes de merge et le TRI sur les cléfs de joindures devront être plus rapide.

Non, elles ne le sont pas... Que veux-tu dire par indexées?

A et B s'appel des alias , sa t'évite d'écrire col1.TABLE1,COL2.TABLE2, ...
tu renome temporairement TABLE1 et TABLE2 par A et B.

Ok

Merci Jérome, faut-il que les tables soit triées à l'avance si c'est pour les retrier ensuite avec "sortedby"?

[s_a_m]

Envoyé par jerome_pdv2

Code :

Le SORTEDBY s'emploie si la table est déjà triée par la colonne spécifiée.
Extrait du HELP SAS.

Code :

the SORTEDTEDBY= DATA SET OPTION TO specify how the DATA are currently sorted

[Misspatate]

Ok, merci

J'ai essayé avec le SORTEDBYet cela fonctionne bien mais c'est plus long que sans.

[s_a_m]

Les indexes. facilite l'accée à la colonne.
c'est comme les sommaires dans les livres. .
exemple de création avec une étape DATA. tu peux en créer avec proc datasets, proc sql;

Code :

1
2
3
4
 
DATA class (INDEX  =( name age));
SET sashelp.class;
run;

[jerome_pdv2]

oui il faut que les tables soient triées à l'avance, le sortedby c'est juste pour indiquer que la table est triée, il faut donc qu'elle le soit.

Dans ton cas ce serait je pense utile si les deux tables sont triée par seulement une seule des deux variables, par exemple par sim, ce qui ne permet pas de faire le merge by sim numero3, mais est un peu plus que ce dont a besoin la proc sql
donc en rajoutant dans ce cas (sortedby=sim) entre les tables et leur allias tu indique qu'elles sont déjà triées par cette variable, et sql devrait alors prendre moins de temps.

[datametric]

Le premier warning s'enlève avec UNDO_POLICY

Code :

1
2
3
4
5
6
7
8
proc copy IN=sashelp out=work;
SELECT class;
run;
 
proc sql undo_policy=none;
CREATE TABLE class AS 
SELECT * FROM class;
quit;

le second warning existe car tu mets a.*, b.* donc toutes les colonnes des tables mêmes celle en commun.
Si tu veux lister les colonnes de tes tables et n'y conserver que celles qui t'intéressent, tu utilises FEEDBACK

Code :

1
2
3
4
proc sql FEEDBACK undo_policy=none;
CREATE TABLE class AS 
SELECT * FROM class;
quit;

Tu lèveras les yeux et regarderas dans la log le résultat. Tu remplaceras ton SELECT par celui fourni et enlèveras le FEEDBACK.

Il n'est pas utile de trier la table. Ce qui baisse tes performances est de faire un LEFT JOIN. Mais si l'on en a besoin, rien n'est à changer. Néanmoins, qu'entends-tu par "un temps assez long" ?
Quels sont les temps entre ta requête SAS et en SQL de MEGAMIND2 ?

[Misspatate]

Je pense aussi mais le fait de trier la table avec proc sort demande à SAS beaucoup de temps, au final, il se peut que ce soit moins efficace

[jerome_pdv2]

Envoyé par jerome_pdv2

oui il faut que les tables soient triées à l'avance, le sortedby c'est juste pour indiquer que la table est triée, il faut donc qu'elle le soit.

Dans ton cas ce serait je pense utile si les deux tables sont triée par seulement une seule des deux variables, par exemple par sim, ce qui ne permet pas de faire le merge by sim numero3, mais est un peu plus que ce dont a besoin la proc sql
donc en rajoutant dans ce cas (sortedby=sim) entre les tables et leur allias tu indique qu'elles sont déjà triées par cette variable, et sql devrait alors prendre moins de temps.

Comme l'indique S.A.M. il y a les index, si tes besoins ne sont pas ponctuels, et utilisent peu ou prou toujours les mêmes tables, tu peux effectivement t'interroger sur l'opportunité de créer des index.

Tu as des papiers qui sont sortis sur la rapidité des différentes méthodes comme par exemple celui-ci

http://www2.sas.com/proceedings/sugi28/096-28.pdf