Discussion :

[didier roustand]

Bonjour à tous,

je reviens vers vous avec un problème de manipulation de table, et le problème c'est que c'est une grosse table (16 millions de lignes) donc je veux faire un programme efficace, car ce que je souhaite faire n'est pas trivial (pour moi).

J'ai une base de ce type :
ID1 info1 ID2

avec pour un ID2 donné, différents ID1 possibles.

Mon but est de créer une nouvelle colonne de la façon suivante :

ID1 info1 ID2 col
1 A 1 y
2 B 1 y
3 C 2 z
4 B 2 z
5 A 2 z
6 B 3 x

Pour un ID2 donné, je regarde les "info1" présentes.
Si j'ai B seul présent : col=x
Si j'ai A et B présents : col=y
Si j'ai A, B d'autres présents : col=z.

A noter que seul B peut être seul pour un ID2 donné.

J'espère avoir été clair, mais si ce n'est pas le cas, je réponds aux questions.

merci d'avance

Cordialement

Didier

[la2002]

Bonjour,

Est ce que les valeurs d'info1 pour un ID2 donné peuvent être B et C (Sans A)? Qu'attends tu dans ce cas pour la colonne calculée?

Si ce n'est pas le cas tu peux essayyer ce code.

1-J'affecte d'abord des valeurs numériques pour chaque cas, afin de gérer les priorités
2-je calcule le max pour chaque ID2
3-je joins les 2 tables par un merge (possible avec une proc sql). Avec un merge dans une étapa data, cela permet un calcul à la volée des valeurs attendues de col.

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 table;
  set table;
  valeur=0;
  if info1="A" then valeur=1;
  if info1<>"A" and info1<>"B" then valeur=2;
run;
 
proc sql;
create table val_max as
select max(valeur) as col_chiffre, id2
from table
group by id2
order by id2;
;
quit;
 
proc sort data=table out=table_trie;
   by id2;
run;
 
data table_finale (drop=valeur col_chiffre);
merge table_trie val_max;
by id2;
if col_chiffre=0 then col="x";
if col_chiffre=1 then col="y";
if col_chiffre=2 then col="z";
run;

N'ayant pas sas sous la main, je n'ai pas testé; j'espere ne pas oublié un caractère ou une instruction!
bon courage!

Cela me parait difficile de se passer de la proc sort qui coutera forcément du temps sur une grosse table.

[sasadm]

Salut Didier,

Dans ces cas là, la proc sql fait des merveilles.
L'important avec les grosses tables c'est de minimiser le nombre de lecture et souvent la procédure sql peut épargner de nombreuses étapes de tri, sous selections, renversement, et va-s-y que je te remerge la sous-selection avec la premiere table !

Voici un code qui utilise la proc sql et son arme fatale : le case when combiné à un group by. Je ne garantis pas que c'est la méthode la plus rapide mais je serais prèt à parier.

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
 
 
data test;
input ID1 info1 $ ID2  ;
cards;
1 A 1
2 B 1
3 C 2
4 B 2
5 A 2
6 B 3
;
run;
 
proc sql;
create table test2
as select ID1 
		,info1
		, ID2
		, case
			when min(info1 eq "B") then "x" /* il n'y a que B */
		    else 
				case 
					when max(info1 not in ("A", "B")) then "z" /* A et B et d'autre(s) */
  			        else  "y" /* seulement A et B */
	        	end 
 
			end as col
from test
group by ID2; /* pour que les grandeurs max et min soient calculées par groupes ID2 de 
				même valeur*/
quit;

Attention: je suppose qu'il n'y a pas d'autres alternatives à tes 3 cas. Sinon il faut affiner un peu le code.
Par ailleurs, si ta table d'entrée est déjà triée par ID2 et que SAS "ne le sait pas" (elle est déjà triée par construction et non par une étape de tri), tu peux l'indiquer comme ceci au niveau de la commande from <table> :

Code :

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

1
2
 
	from test (SortedBy=ID2)

L'étape de tri interne à la procédure sql ne sera pas lancée => c'est autant de temps gagné.

[sasadm]

Dans le cas où il y aurait d'autres alternatives à tes 3 cas :

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
 
 
data test;
input ID1 info1 $ ID2  ;
cards;
1 A 1
2 B 1
3 C 2
4 B 2
5 A 2
6 B 3
7 F 4
8 B 4
9 D 5
10 Z 5
;
run;
 
proc sql;
create table test2
as select ID1 
		,info1
		, ID2
		, case
			when min(info1 eq "B") then "x" /* il n'y a que B */
		    else 
				case 
					when max(info1 eq "B") /* présence de B */
						and max(info1 eq "A") /* présence de A */
						and max(info1 not in ("A", "B")) /* présence autre */
 
							then "z"
 
				     else 
						case 
							when max(info1 eq "B") /* présence de B */
						    		and max(info1 eq "A") /* présence de A */
						 			and (max(info1 not in ("A", "B")) eq 0) /* absence d'autre */
							then "y" /* seulement A et B */
	        			end
				end 
	  		end as col
from test
group by ID2;
quit;

[sasadm]

Salut,
Comme le sujet m'intéresse je suis allé un peu plus loin que ta question (veuillez m'en excuser ami lecteur).
un code mieux écrit dans l'enchainement des conditions :

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
 
proc sql;
CREATE TABLE test2
AS SELECT ID_1 
		,info1
		, ID_2
		, case
			when max(info1 eq "B") /* il y a au moins 1 B */
				then 
		    		case 
						when max(info1 eq "A") /* présence de A */
							then 
								case when max(info1 NOT IN ("A", "B")) /* présence other */
 										then "b+a+o" /* A et B et other */
									 else "b+a" /* seulement A et B */
								end
 					   else /*  pas de A  */ 
							case 
								when min(info1 eq "B") /* que B */
						    		then "b" /* seulement B */
								else "b+o" /* b + other */
	        				end
					end 
			else "-" /* il n'y a pas de b */
	  	end AS col
FROM TEST_BIG /*(SortedBy=ID_2)*/
GROUP BY ID_2;
quit;

J'ai testé ce code sur une table de 10 millions de lignes.
Sur mon serveur, le tri de la table prend 14 secondes.
La procédure sql avec l'option (SortedBy=ID_2) prend dans les 30 secondes.
Sans l'option, on passe à une minute.

Voici le jeu de test:

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
 
DATA test;
input ID1 info1 $ ID2  ;
cards;
1 A 1
2 B 1
3 C 2
4 B 2
5 A 2
6 B 3
7 F 4
8 B 4
9 D 5
10 Z 5
;
run;
 
DATA TEST_BIG (drop=ID1 ID2 i) ;
length ID_2 ID_1  8;
set test;
do i=0 to 1000000;
 ID_1=ID1 + 10 * i; 
	ID_2=ID2+10*i;
	output;
	/*ID_2=ID2 +10*i; output;*/
end;
run;
proc sort data=TEST_BIG ;
by ID_2;
run;

Ces résultats ne peuvent être généralisés. Ils dépendent fortement de la table d'entrée.
L'enchaînement des conditions doit être pensé en fonction de la connaissance qu'on peut avoir des fréquences des modalités des variables à tester. Un bon enchaînement traite d'abord les modalités les plus nombreuses de façon à limiter le nombre moyen de tests. Idéalement, les tests traitent chaque cas dans l'ordre décroissant de leur occurrence.
Par exemple s'il n'y a quasiment pas de B dans la table, on ne commence pas par tester si "info égale B" mais au contraire si "info est différent de B".

Il serait intéressant de comparer la méthode présentée ici avec des méthodes alternatives. Si ça tente quelqu'un...