Quiero trabajar un poco con objetos hash en SAS. Pero antes quería demostraros con una comparativa de código muy sencilla y muy rápida la necesidad de trabajar con estos objetos en SAS. La problemática es muy habitual en nuestro trabajo diario. Tenemos una tabla SAS muy grande, con millones de registros y tenemos que cruzarla con otra tabla SAS muy pequeña para quedarnos sólo con los registros que aparezcan en la tabla pequeña. Tenemos unos clientes que han recibido un contacto comercial y hemos de quedarnos con sus saldos históricos en determinados productos. Veamos los distintos métodos que planteo para machear registros, conjuntos de datos de partida:
options fullstimer;
*CONJUNTO DE DATOS GRANDE, EXISTE LA
POSIBILIDAD DE QUE TENGA DATOS REPETIDOS;
data grande;
do i=1 to 20000000;
idcliente=int(ranuni(0)*1000000);
drop i;
output;
end;
run;
*CONJUNTO DE DATOS PEQUEÑO, NO TIENE
REGISTROS DUPLICADOS;
data pequenio;
do i=1 to 2000000;
idcliente=int(ranuni(34)*1000000);
drop i;
if mod(idcliente,1132)=0 then output;
end;
run;
proc sort data=pequenio nodupkey; by idcliente;quit;
Tenemos que cruzar el dataset grande con el pequeño, 20 millones frente a unos miles. En mi larga (muy larga) experiencia trabajando con SAS casi todo el mundo (menos Sonia) con el que he coincidido haría:
*OPCION 1: ORDENACION Y MERGE;
proc sort data=grande; by idcliente;
proc sort data=pequenio; by idcliente; run;
data machea;
merge grande (in=a) pequenio (in=b);
by idcliente;
if a and b;
run;
Ordenamos los dos conjuntos de datos SAS y realizamos un merge, también es habitual ver el outer join. Este tipo de cruces, con los datos empleados, en un equipo local poco potente tarda algo más de 1 minuto. Mi amiga Sonia y yo hasta hace unos meses hubiéramos hecho:
*OPCION 2: CREACION DE FORMATOS;
proc sort data=pequenio out=selec nodupkey; by idcliente;
data selec;
set selec;
fmtname="selec";
label="1";
start=idcliente;
run;
proc format cntlin=selec;quit;
proc delete data=selec;quit;
*********************************;
data machea2;
set grande;
if put(idcliente,selec.)="1";
run;
El empleo de formatos para los cruces de tablas SAS aumentó mi productividad exponencialmente y permitió que fuera una de las personas más productivas en una gran entidad bancaria. Este cruce tarda aproximadamente unos 10 segundos en realizarse. Tiene una limitación cuando realizamos cruces con valores repetidos, a ver si algún lector identifica el problema, poco habitual pero que limita este tipo de cruces. Por último quería acercaros al uso de objetos hash con SAS. Puede que escriba largo y tendido sobre el tema pero hoy sólo un ejemplo:
*OPCION 3: MANEJO DE OBJETOS HASH;
data machea3 ;
set pequenio point = _n_ ;
declare hash objhh (dataset: 'pequenio') ;
objhh.DefineKey ( 'idcliente' ) ;
objhh.DefineDone () ;
do until ( fin ) ;
set grande end = fin ;
if objhh.find () = 0 then output ;
end ;
stop ;
run ;
Con el mismo equipo este proceso tarda en ejecutarse 4 segundos. ¿Merece o no la pena aprender a trabajar con objetos hash? Podéis ser aun más productivos, algo que en algunos casos no repercute en vuestra calidad laboral, si eres más productivo y más riguroso tendrás más trabajo, si no eres productivo y fallas constantemente mejor haces poco trabajo, eso sí, tu hora de salida siempre ha de ser posterior a la del responsable. En fin, dudas, sugerencias, salir a las 15 horas en rvaquerizo@analisisydecision.es
Sería interesante hacer un diseño de experimentos y ver en qué situaciones realmente se gana. Estuve probando los hash hace unos meses para optimizar varias queries y al final la solución ganadora fue utilizar un paso data con una cláusula IN convenientemente situada, y generada con macrovariables, eso sí, la «tabla pequeña» tiene que ser muy pequeña. Lo de los formatos y los duplicados lo dejo para otros lectores… :OP
Es más rápido el merge, pero la ordenación lo fastidia todo. Se trata de evitarla (fu fu fu). En determinadas ocasiones no podemos permitirnos realizar la ordenación simplemente porque no tenemos espacio. Si trabajamos con datasets ordenados el merge le da mil vueltas a todas las demás soluciones.
Tengo yo un caso de 216 millones de registros donde la ordenación ni siquiera era posible. Lo solventaba con el tema de los formatos.
No, no, no estoy hablando de merge, en este caso concreto se sustituía por un set tabla(where=(variable in (a,b,c,d,…,xxx))), y el listado que va en la cláusa IN generado con macrovariables, en cualquier caso como digo, la tabla pequeña tiene que ser muy pequeña… el caso concreto era aprox 100 millones de registros cruzado contra unos 100 valores…
Hay una cuarta opción que me gusta más: proc sql.
Tiene las siguientes ventajas:
1) Es más simple de escribir, entender y mantener.
2) No le dices a SAS cómo quieres cruzar las tablas: sólo que te las cruce. Delegas el cómo en el autor del optimizador de los cruces de SQL.
3) Igual el plan de cruce no es bueno, pero al correr el programa en una versión mejorada (futura) de SAS, igual se ha mejorado la optimización de los cruces y, sin hacer nada, mejora el rendimiento de la aplicación.
4) No sé bien qué pasa con los formatos y los hashes cuando el cruce se realiza sobre dos o más columnas.
5) Es más fácil migrar la aplicación a una plataforma no SAS.
El caso que indicas es uno de esos «casos de libro» en el que el optimizador:
1) «Hashea» la tabla pequeña en memoria.
2) Recorre la grande fila a fila.
3) Busca la fila correspondiente en la pequeña por «hash».
4) Escribe las filas en el fichero de salida.
Si no lo hace, es que SAS no es tan bueno como cierta gente opina.
Más: el que quiera saber cómo va eso de los «hashes» en R, puede leer http://www.datanalytics.com/blog/2010/08/17/una-tarea-para-mis-lectores.
En general estoy de acuerdo en lo que dices, pero solo como regla general. Este caso era de esas «excepciones excepcionales» en las que:
1) La existencia de una eventual aplicación que mantener o migrar dependía de obtener ciertos resultado en un periodo de tiempo 1X, incluyéndose más de 200 repeticiones de determinado experimento.
2) Se evaluaron todas las posibilidades para que dichos resultados estuvieran disponibles en un periodo de tiempo 1X: Proc sql, Merge, Formatos, Hash, Where variable IN. Siendo la última la que proporcionaba mejores resultados, ya que alguna de las otras opciones llegaba incluso a 4X.
Repito, estoy de acuerdo en general en lo que dices, pero hay excepciones excepcionales que requieren medidas excepcionales, y uno debe estar preparado, por eso este artículo comienza con «Trucos SAS» y no «Buenas prácticas SAS»… ;)
Creo que faltan dos opciones más:
1) proc sql
2) proc sql con un índice sobre el campo de cruce de la tabla grande
La opción (2) tiene la ventaja de que si la tabla grande tiene muchas columnas (y el optimizador entra a ella por índice), SAS no tiene que leer toda la tabla (potencialmente muy grande) sino solamente el índice (que es mucho más estrecho) y acceder a la tabla grande sólo para recuperar aquellas (potencialmente pocas) filas en que coincida la clave de cruce.
Tienes razón. No quise emplear el SQL. Pero hay que evaluarlo sobre todo la opción 2 porque es EL PROBLEMA con el que se encuentran un % muy alto de los lectores.
Cierto, faltó la de los índices (el proc sql a secas sí que estaba incluido) que en mi caso se descartó porque la tabla grande no era siempre la misma (habrían sido necesaria la creación de índices varias decenas de veces) pero en una aplicación más estable podría ganar…
Hoy la metodología de los formatos ha tenido un éxito sin precedentes. 60 minutos a 1 minuto…
Muy buenas a todos,
Estaba mirando las distintas formas expuestas de cruce de tablas. Quisiera hacer referencia a un «error» que se produce en el cruce mediante formatos. Si te fijas, la tabla «pequenio» no contiene el valor «1» y en cambio, la tabla resultante del cruce sí. Se debe a que en la tabla «grande» existe este valor. Luego el valor que se ha dado a los distintos valores de idcliente es «1» (format value).
Sé que es un error sin importancia y tiene fácil solución. Pero por si a alguien se le ha presentado dicho problema, que sepa a qué es debido (si no lo sabe ya).
sL2
Por cierto, en el comentario anterior se me olvidó: gracias por publicar todos estos trucos. Creo sinceramente que se aprende muchísimo. A los que participan con sus comentarios también.
El error que comentas es debido a estos «ejemplos forzados». Por cierto, ejecutando PROC SQL y cruce con formatos en WHERE sobre tablas Oracle también devuelve un error pero se ejecuta correctamente. Una forma muy eficiente de realizar subconsultas.
A ver si localizo alguna ejecución y os reporto este problema.
COLABORADORES Y LECTORES DE ESTE BLOG
HOLA A TODOS:
He leído casi todo lo relacionado con SAS que se ha publicado en este Blog el cual es muy ilustrativo y me ha sido de utilidad para resolver casos menores de ordenación de bases de datos. Esta «entrada» es la que creo se parece a mi duda la cual es:
Para una variable tengo diferentes valores de mediciones (observaciones) obtenidas en el tiempo, para la última medición selecciono las 10 mayores (por ej. las 10 alturas máximas) que corresponden a 10 individuos dentro de la población, luego quiero que esos mismos 10 individuos sean seleccionados para las mediciones anteriores. Esto se repite para casi 2500 registros (muestras) que conformarían una tabla chica contra casi 250000 registros de una tabla grande.
¿Significa esto que debo hacer un cruce de tablas una chica (la que tiene los índividuos más altos) y la grande que contiene el total de las observaciones?. Por ej. en la última medición de una muestra los mayores registros los tienen los individuos: 34, 78, 3, 78, 21, 1, 55, 5, 40 y 99, la idea es que de la base grande esos mismos individuos sean identificados y selecionados para todas las medciones anteriores que incluya por supuesto sus respectivos valores de altura. Y así para el resto de las muestras.
Si tengo que realizar cruce de tablas, alguien me podría orientar sobre la sintaxis en SAS. O ¿cómo podría solucionar esto?
Estoy intentando aprender sobre manejo de bases de datos en SAS apoyándome en expertos como Ustedes colaborados y lectores de este Blog.
Saludos desde México.
Juan Carlos T. U.
Efectivamente tienes que cruzar las tablas. Mira este mensaje:
https://analisisydecision.es/proc-sql-merge-set/
Puede ayudarte a cruzar tablas. Pero en tu caso puedes hacer:
proc sql;
create table datos as select
a.*,b.*
from tabla_grande a, tabla_peque b
where a.arbol = b.arbol;
quit;
De este modo obtendrás los datos de la tabla grande para los que tienes en la tabla pequeña. Imagino que irán por ahí los tiros.
Yirli, ¿exactamente que deseas hacer? ¿Eliminar duplicados, unir las tablas sin duplicados? Ojo si unes tablas con registros duplicados por el campo de unión.
Hola, si yo uno las tablas concatenando unas variables de la tabla pequeña y de la grande
Así:
PROC SQL;
…
…
FROM TABLA_PEQUEÑA A INNER JOIN TABLA_GRANDE B ON
CAT(A.VAR1,A.VAR2,A.VAR3)=CAT(B.VAR1,B.VAR2,B.VAR3,B.VAR4)
;
QUIT;
¿cómo hago eso con objetos hash?
Buena pregunta. La apunto para realizar una entrada. Gracias.
Buenos días, me gustaría saber como realizar un cruce en SAS de dos tablas sin utilizar un campo de cruce:
Tabla1
Tabla2
CLAVE: Sin campo de cruce
TIPO: Both
Lo que pretendo es que a partir de una tabla en la que no existe como campo clave el código de empresa, meter todas las combinaciones posibles.
Si alguien me pudiese ayudar se lo agradecería
Un saludo
Eso es el producto cartesiano de dos tablas, en SAS:
proc sql;
create table tablon as select
a.*,
b.*
from tabla1 a, tabla2 b;
quit;
Ojo que es un todas con todas…
Muchas gracias rvaquerizo, ¿es necesario ordenar las tablas que forman parte del cruce? Ej:
%ordena_tabla(libent=&libtab1,tabent=&tabinput_1,campos_clave=&campos_clave_1,num_claves=&num_claves,tabsal=_tabinput_1,elim_dup=&elim_dup1);
¿y con lo de «tablon» a que te refieres?
Gracias
Perdona ya está solucionado
Gracias por la aclaración
Buenas Raul,
Una duda respecto al hash object:
Haces el paso como sigue:
data machea3 ;
set pequenio point = _n_ ;
declare hash objhh (dataset: ‘pequenio’) ;
objhh.DefineKey ( ‘idcliente’ ) ;
objhh.DefineDone () ;
do until ( fin ) ;
set grande end = fin ;
if objhh.find () = 0 then output ;
end ;
stop ;
run ;
No lo acabo de entender bien..¿qué diferencia habría
respecto a lo que pongo abajo?
data machea3;
if _N_ = 1 then do;
declare hash objhh(dataset:’pequenio’);
objhh.definekey(‘idcliente’);
objhh.definedone();
end;
set grande;
if objhh.find () = 0 ;
run;
Gracias,
Hola, probablemente sea un tema de versiones de SAS. Lo han ido evolucionando, nada tiene que ver el uso de objetos hash en la V9.1 con el uso en la 9.3
ok, gracias