manipulation de tables avec variables référencées dans table

Version imprimable

Voir 40 message(s) de cette discussion en une page

13/01/2010, 12h07
AlexFred

manipulation de tables avec variables référencées dans table

Bonjour,
Voici le problème auquel je suis confronté.

Soit une table T1 avec les variables X, A, et Y.
la variable A est toujours ègale à IDEM

Soit une table T2 avec les variables X1 X2 X3 X4 X5.

Je cherche à remplacer la valeur de X1 si cette valeur correspond à une valeur de X de T1 par la modalité de Y correspondant.

Même chose pour X2 .. X5

Quelle serait pour vous la méthode la plus élégante et efficace ?

d'avance merci pour toute piste.

As tu un jeu de données? (même bidon)

Une première ébauche est (à vérifier) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
proc sql;
	create table T3 as
	select	
		case when B.X1 =A.X 
			then A.Y
			else B.X1 end as NEWX1,	
		case when B.X2  =A.X 
			then A.Y
			else B.X2 end as NEWX2,	
		case when B.X3  =A.X 
			then A.Y
			else B.X3 end as NEWX3,	
		case when B.X4  =A.X 		
                              	then A.Y
			else B.X4 end as NEWX4,	
		case when B.X5 =A.X 
			then A.Y
			else B.X5 end as NEWX5
	from T1 as A
		right join T2 as B
		on A.Y ne . or B.X1 ne . or B.X2 ne . or B.X3 ne . or B.X4 ne . or B.X5 ne .
	;
quit;

13/01/2010, 12h47
AlexFred

Bonjour,
j'ai imprimé ta réponse pour essayer de la comprendre.

En attendant
voici un jeu de données bidon

T1 contient des enregistrements du style :
X A Y
1 Idem 1942
2 Idem 7
3 Idem 111
4 Idem 9

T2 contient des enregistrements du style :
X1 X2 X3 X4 X5
3 2 5 6 3
1 3 4 . 2

Au final j'aimerais que ma nouvelle table T2 contienne
X1 X2 X3 X4 X5
111 7 5 6 111
1942 111 9 . 7

Encore merci.

Une approche par les formats ci-dessous.

Tout d'abord, ton jeu de "données bidon" en données SAS :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
data T1 ;
    input X A $ Y ;
    cards ;
    1 Idem 1942
    2 Idem 7
    3 Idem 111
    4 Idem 9
    ;
run ;
data T2 ;
    input X1 X2 X3 X4 X5 ;
    cards ;
    3 2 5 6 3
    1 3 4 . 2
    ;
run ;

Le code consiste à transformer la table T1 en format.
La valeur spéciale "xxx" du format y signifie : ne pas changer Xi.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
data WORK.T1_for_proc_format ;
    set WORK.T1 end = eod ;
    label   = put(Y,8.) ;
    start   = X ;
    end     = X ;
    fmtname = "T1_format"; 
    type    = "n" ;
    output ;
    if eod then do ;
        start   = . ;
        end     = . ;
        label   = "xxx" ;
        hlo     = "o" ; /* --> Condition "other" */
        output ;
        end ;
run ;
proc format library=work cntlin=T1_for_proc_format;
run ;

Ensuite, dans T2, on vérifie la valeur de Xi quand on applique le format créé.
Si le résultat est différent de "xxx", on remplace Xi par la valeur formattée.

Code:

1
2
3
4
5
6
7
8
data T2_transformed ;
    set T2 ;
    if put(X1, T1_format.) ne "xxx" then X1 = input(put(X1, T1_format.),8.);
    if put(X2, T1_format.) ne "xxx" then X2 = input(put(X2, T1_format.),8.);
    if put(X3, T1_format.) ne "xxx" then X3 = input(put(X3, T1_format.),8.);
    if put(X4, T1_format.) ne "xxx" then X4 = input(put(X4, T1_format.),8.);
    if put(X5, T1_format.) ne "xxx" then X5 = input(put(X5, T1_format.),8.); 
run ;

Si tu as un nombre "élevé" de Xi, tu dois pouvoir boucler avec une macro ou un array.

Le résultat sur les données "bidon" :

Code:

1
2
3
Obs     X1      X2    X3    X4     X5
 1      111      7     5     6    111
 2     1942    111     9     .      7

13/01/2010, 15h20
AlexFred

Bravo à Rémi Bousquet.

Effectivement en passant par un format, c'est très astucieux.

Maintenant, je vais essayer de le faire avec la méthode proposée par Manoutz si je peux, mais c'est pas encore gagné.

Merci à vous 2.
A+

Effectivement la méthode de Rémi est bien trouvée :ccool:

La mienne est un peu plus tordue mais évite les formats (que je trouve parfois un peu "rigides")

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
%macro loop(nb=);
proc sql;
%do i=1 %to &nb.;
		CREATE TABLE T3_%cmpres(&i.) AS
	SELECT	
		case when B.X%cmpres(&i.) =A.X 
			then A.Y
			else B.X%cmpres(&i.) end AS NEWX%cmpres(&i.)
	FROM T1 AS A
		RIGHT JOIN T2 AS B
		ON A.X eq B.X%cmpres(&i.)
	order by monotonic(B.X%cmpres(&i.));
%end;	;
quit;
 
 
data T3;
merge %do i=1 %to &nb.; T3_%cmpres(&i.) %end;	;
run; 
 
%mend loop;
 
%loop(nb=5);

Merci Manoutz pour cette autre méthode brillante.

Je l'ai testé, elle marche.
Toutefois je voulais l'appliquer à une base contenant en plus des 5 variables X1..X5, toute une série de variables.

Du coup j'ai changé légèrement ton code qui est devenu
Code:

1 2 3 4 5 6 7 8 9 10 11 12 proc sql; %do i=1 %TO &nb.; CREATE TABLE T3_%cmpres(&i.) AS SELECT A.*,B.*, case when B.X%cmpres(&i.) =A.X then A.Y else B.X%cmpres(&i.) end AS NEWX%cmpres(&i.) FROM T1 AS A RIGHT JOIN T2 AS B ON A.X eq B.X%cmpres(&i.) ORDER BY monotonic(B.X%cmpres(&i.)); %end; ;
Cela marche a priori, mais je récupère une note étrange.

NOTE: La requête telle qu'elle est spécifiée implique de classer en fonction d'un élément qui n'apparaît pas dans sa clause SELECT.
NOTE: La table WORK.T3M_1 a été créée, avec 9454 lignes et 40 col.

Peux tu m'expliquer cela?

En tous cas, encore merci.

14/01/2010, 18h10
RemiBousquet

C'est du SQL, donc je maîtrise pas, donc je ne pourrai pas t'expliquer.

Si tu testes les deux méthodes sur des bases de taille significative, peux tu indiquer si l'une des deux méthodes est plus rapide ?

Code:

1 2 NOTE: La requête telle qu'elle est spécifiée implique de classer en fonction d'un élément qui n'apparaît pas dans sa clause SELECT. NOTE: La table WORK.T3M_1 a été créée, avec 9454 lignes et 40 col.
note 1: C'est pas génant. Ca précise que tu classes ton fichier sortie en variables non présente dans ce fichier (mais présente dans le fichier entrée)

note2: Je vois pas d'où viens le M du WORK.T3M_1

NB je n'ai pas ce deuxième message... Tu peux me décrires tables entrées (ainsi que celle que tu veux en sortie?)

PS le programme est écrit d'une manière flexible de sorte à pouvoir faire varier le nombre de boucles - c'est-à-dire le nombre de Xi dans la table T2. Les autres variables sont à rajouter en non dynamique.

Manoutz

Code:

1 2 NOTE: La requête telle qu'elle est spécifiée implique de classer en fonction d'un élément qui n'apparaît pas dans sa clause SELECT. NOTE: La table WORK.T3M_1 a été créée, avec 9454 lignes et 40 col.
Manoutz écrit:

Citation:

note 1: C'est pas génant. Ca précise que tu classes ton fichier sortie en variables non présente dans ce fichier (mais présente dans le fichier entrée)

Comment ça je classe !!!
Peux tu être plus explicite.

Citation:

note2: Je vois pas d'où viens le M du WORK.T3M_1

C'est tout simplement que j'ai changer le nom de ta table de sortie pour ne pas me mélanger avec un programme précédent.

Citation:

PS le programme est écrit d'une manière flexible de sorte à pouvoir faire varier le nombre de boucles - c'est-à-dire le nombre de Xi dans la table T2. Les autres variables sont à rajouter en non dynamique.

en non dynamique, c'est à dire comme je l'ai fait ci dessus en rajoutant A.*,B.*

Pour le temps d'exécution cela donne:
Code:

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 NOTE: La requête telle qu'elle est spécifiée implique de classer en fonction d'un élément qui n'apparaît pas dans sa clause SELECT. NOTE: La table WORK.T3M_5 a été créée, avec 9454 lignes et 40 col. MPRINT(LOOPM): ; MPRINT(LOOPM): quit; NOTE: La procédure SQL a utilisé (Durée totale du processus) : temps réel 1:02.72 temps processeur 3.78 secondes MPRINT(LOOPM): DATA T3; MPRINT(LOOPM): merge MPRINT(LEFT): T3M_1 MPRINT(LEFT): T3M_2 MPRINT(LEFT): T3M_3 MPRINT(LEFT): T3M_4 MPRINT(LOOPM): T3M_5 ; MPRINT(LOOPM): run; NOTE: 9454 observations copiées de la table WORK.T3M_1. NOTE: 9454 observations copiées de la table WORK.T3M_2. NOTE: 9454 observations copiées de la table WORK.T3M_3. NOTE: 9454 observations copiées de la table WORK.T3M_4. NOTE: 9454 observations copiées de la table WORK.T3M_5. NOTE: La table WORK.T3 a 9454 observations et 44 variables. NOTE: L'étape DATA a utilisé (Durée totale du processus) : temps réel 26.44 secondes temps processeur 0.23 secondes
Je vais récupérer pour Rémi letemps avec l'autre méthode.
A+

Pour Rémi,

Voici le temps d'exécution :

Code:

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
61   DATA S.T1_for_proc_format ;
62       SET S.T_LU_DBL end = eod ;
63       label   = put(u_ID_RETeNU ,8.) ;
64       start   = U_ID_UR ;
65       end     = U_ID_UR;
66       fmtname = "T1_format";
67       type    = "n" ;
68       output ;
69       IF eod then do ;
70           start   = . ;
71           end     = . ;
72           label   = "xxx" ;
73           hlo     = "o" ; /* --> Condition "other" */
74           output ;
75           end ;
76   run ;
 
NOTE:  1753 observations copiées de la table S.T_LU_DBL.
NOTE: La table S.T1_FOR_PROC_FORMAT a 1754 observations et 9 variables.
NOTE: L'étape DATA a utilisé (Durée totale du processus) :
      temps réel                   0.20 secondes
      temps processeur   0.03 secondes
 
 
77   proc format fmtlib lib=library cntlin=S.T1_for_proc_format;
NOTE: Format T1_FORMAT se trouve déjà dans la bibliothèque.
NOTE: Le format T1_FORMAT a été copié dans LIBRARY.FORMATS.
78   run ;
 
NOTE: La procédure FORMAT a utilisé (Durée totale du processus) :
      temps réel                   0.40 secondes
      temps processeur   0.06 secondes
 
NOTE:  1754 observations copiées de la table S.T1_FOR_PROC_FORMAT.
 
79   DATA S.T9454parC ;
80       SET S.T9454par ;
81       IF put(A2_INT_u_ID_UR, T1_format.) ne "xxx" then A2_INT_u_ID_UR = input(put(A2_INT_u_ID_UR, T1_format.),8.);
82       IF put(QUEST_u_ID_UR, T1_format.) ne "xxx" then QUEST_u_ID_UR = input(put(QUEST_u_ID_UR, T1_format.),8.);
83       IF put(CODE_ID_UR, T1_format.) ne "xxx" then CODE_ID_UR = input(put(CODE_ID_UR, T1_format.),8.);
84       IF put(SIGLE_ID_UR, T1_format.) ne "xxx" then SIGLE_ID_UR = input(put(SIGLE_ID_UR, T1_format.),8.);
85       IF put(USER_ID_UR, T1_format.) ne "xxx" then USER_ID_UR = input(put(USER_ID_UR, T1_format.),8.);
86   run ;
 
NOTE:  9454 observations copiées de la table S.T9454PAR.
NOTE: La table S.T9454PARC a 9454 observations et 36 variables.
NOTE: L'étape DATA a utilisé (Durée totale du processus) :
      temps réel                   3.88 secondes
      temps processeur   0.26 secondes

les temps processeur sont vraiment voisins. le temps réel ?

Encore merci à vous 2.
A+

15/01/2010, 12h42
Manoutz

Code:

ORDER BY monotonic(B.X%cmpres(&i.));

signifie que tu tries ton fichier sortie par valeur croissante du numéro d'observation (numéro de ligne) du fichier T2. Dans l'idée tu fais un proc sort de ton fichier de sortie by _N_, où _N_ est puisé dans T2...

Citation:

Citation:
note2: Je vois pas d'où viens le M du WORK.T3M_1

C'est tout simplement que j'ai changer le nom de ta table de sortie pour ne pas me mélanger avec un programme précédent.

Donc t'es ok sur cette note de la log - C'est juste que ca collait pas avec ton dernier code (Le "M" n'est pas présenté)...

Citation:

en non dynamique, c'est à dire comme je l'ai fait ci dessus en rajoutant A.*,B.*

Tout à fait, reste à voir si tu as besoin de toutes les variables de tes deux fichiers entrée..

C'est cela que vous cherchez ?

Je ne maîtrise pas bien les HASH, aussi Xavier va me corriger mais c'est typiquement une problématique à laquelle ils répondent.

Code:

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
DATA T1 ;
    input X A $ Y ;
    cards ;
    1 Idem 1942
    2 Idem 7
    3 Idem 111
    4 Idem 9
    ;
run ;
 
data t2;
array t(*) x1-x5;
do j=1 to 10 ;
do i = 1 to dim(t);
t(i) = int(ranuni(12)*10); 
end;
output;
end;
drop i j;
run;
 
 
data final (keep=x1-x5);
 
if 0 then set T1;
 
declare hash hhh(hashexp: 10, dataset:"T1") ; 	      
	hhh.defineKey('X') ;
	hhh.DefineData ('Y') ; 
	hhh.defineDone() ;  	
 
do until ( eof2 ) ;
	set T2 end = eof2 ;
 
	Array ax (*) x1-x5;
	Do i=1 to dim(ax);
		x=ax(i);
		rc = hhh.find();
		if rc=0 then ax(i)=y;
	end;
 
output;
end ;
stop;
 
run;

Datametric, bravo pour cette 3ème méthode.

Elle me parait être la plus performante.
Code:

1 2 3 4 5 6 NOTE: 1753 observations copiées de la table S.T_LU_DBL. NOTE: 9454 observations copiées de la table WORK.TOTOTMP. NOTE: La table WORK.FINAL a 9454 observations et 41 variables. NOTE: L'étape DATA a utilisé (Durée totale du processus) : temps réel 0.53 secondes temps processeur 0.10 secondes
Une grande partie de l'astuce vient de cette instruction je suppose.
rappel de ton code

Code:

rc = hhh.find();

Peux tu me dire où je peux trouver de la doc par rapport à ces méthodes de HASH.

Merci.

18/01/2010, 15h21
Manoutz

Ca m'intéresse, tu peux expliquer le principe de l'algo vite fait?

yep. Je vais en faire un tuto de ce truc là d'ailleurs, je sens l'inspiration...

Citation:

Une grande partie de l'astuce vient de cette instruction je suppose.

Bof... c'est plutôt cela et .find()

Code:

1
2
3
4
declare hash hhh(hashexp: 10, dataset:"T1") ; 	      
	hhh.defineKey('X') ;
	hhh.DefineData ('Y') ; 
	hhh.defineDone() ;

Décodage rapide :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DATA final (keep=x1-x5); /* Ne conserve au final que les variables d'origine ce qui détruit les variables de travail et celles de T1*/
 
IF 0 then SET T1; /* Initialise la lecture de T1. Obligatoire */
 
declare hash hhh(hashexp: 10, dataset:"T1") ; /* crée un objet hhh à partir de la lecture de la table T1. Cet objet est en mémoire (RAM) */
	hhh.defineKey('X') ; /* C'est la clef de recherche. Imaginez l'étape VALUE d'une proc format. ex : 1 => 1942. 1 représente le code/clef ici ... */
	hhh.DefineData ('Y') ;  /* ... et ici on a le libellé (1942) si l'on continue le parallèle avec VALUE de la proc FORMAT */
	hhh.defineDone() ; /* fin de la montée en mémoire */
 
do until ( eof2 ) ; /* Jusqu'à la dernière ligne de la table , faire ce qui suit : */
	SET T2 end = eof2 ; /* Lire T2, le marqueur de fin du fichier est identifiée par un petit nom "eof2" */
 
	Array ax (*) x1-x5; /* Je monte en mémoire les 5 variables dans un tableau que je nomme ax */
	Do i=1 TO dim(ax); /* pour chacune d'entre elles */
		x=ax(i); /* je suis obligé de créer une variable x prenant les valeurs de X1 car c'est le nom de la clef et la méthode find() oblige à avoir une colonne de même nom pour la recherche */
		rc = hhh.find(); /* si le X en mémoire de t1 concorde avec une valeur X de T2 alors le Return Code vaut 0 */ 
		IF rc=0 then ax(i)=y; /* Si le RC vaut 0 alors je remplace la valeur X1 par ton fameux Y. Ce que j'appelle le libellé dans le parallèle fait avec VALUE de la proc FORMAT */
	end; /* fin de la boucle. J'étais X1 je m'occupe de X2 .. puis X3 etc ...*/
 
output;
end ;
stop;
 
run;

18/01/2010, 17h13
AlexFred

Datametric,

Sais tu depuis quand existe l'objet Hash et les méthodes associées,

J'ignorai son existence.

Cela montre qu'il faut tout le temps aller se documenter.

Merci doublement pour cet algorithme et cette découverte pour moi.
et Merci à ce forum qui nous donne des solutions, voire des pistes.
Et je t'encourage à faire ce tuto.
:roll:
18/01/2010, 18h37
datametric

au moins 2002
19/01/2010, 09h28
RemiBousquet

En attendant le tuto (impatiemment),
il y a une fiche faite par SAS à ce sujet :
http://support.sas.com/rnd/base/data...-tip-sheet.pdf
19/01/2010, 09h44
fafabzh6

Citation:

Envoyé par datametric

yep. Je vais en faire un tuto de ce truc là d'ailleurs, je sens l'inspiration...

Très très bonne idée datametric, nous l'accueillerons à bras ouverts.

Voir 40 message(s) de cette discussion en une page