Truco SAS. Transponer tablas con PROC TRANSPOSE, DATA o PROC SQL

Para transponer datasets disponemos en SAS del PROC TRANSPOSE. El ahora escribiente no es muy partidario de emplearlo. Prefiero otras metodologías para transponer conjuntos de datos SAS. Voy a trabajar con un ejemplo que os servirá para aproximaros al TRANSPOSE y para entender mejor las opciones de lectura de un PASO DATA y el funcionamiento del PROC SQL. La idea es, partiendo de una tabla de hechos por meses, transponer un campo importe. Vamos a simular una tabla con esa estructura:


data hc_mes_importe;
do idcliente=1 to 1000000;
do mes=200901 to 200903;
importe=rand("uniform")*ranpoi(3,10000);
output;
end;end;
run;

Tenemos 3 registros por cada idcliente correspondientes a los meses de 200901 a 200903. La idea es transponer la tabla de forma que el importe que ahora está en filas pase a ser columnas y tengamos un solo registro para cada idcliente. El primer método que tenemos es el uso del TRANSPOSE:


proc transpose data=hc_mes_importe prefix=imp
out=t_mes_importe (drop=_name_);
id mes;
by idcliente;
quit;

Esta es la estructura más simple del TRANSPOSE. En prefix indicamos el prefijo que deseamos para la nueva variable. En el dataset de salida eliminamos la variable nombre que genera SAS por defecto. Evidentemente transponemos por idcliente y en la instrucción ID ponemos el campo que identifica las columnas. Esta es la sintaxis más habitual del TRANSPOSE, a continuación os planteo como transponer mediante un paso DATA:


data t_mes_importe;
merge hc_mes_importe (rename=importe=imp200901 where=(mes=200901))
hc_mes_importe (rename=importe=imp200902 where=(mes=200902))
hc_mes_importe (rename=importe=imp200903 where=(mes=200903));
by idcliente;
run;

Esta forma de transponer es la unión horizontal de una tabla consigo misma tantas veces como meses disponemos por idcliente. Necesitamos renombrar la variable para no quedarnos con sólo una columna. Otro modo de transponer tablas y que a mi me gusta particularmente es el uso del PROC SQL:


proc sql;
create table t_mes_importe as select
idcliente,
sum(importe*(mes=200901)) as imp200901,
sum(importe*(mes=200902)) as imp200902,
sum(importe*(mes=200903)) as imp200903
from hc_mes_importe
group by 1;
quit;

Lo que hacemos es sumarizar por el campo del que deseamos un registro único y operamos con los campos que vamos a transponer. Esta operación es el importe multiplicado por una condición.

Si ejecutáis los códigos que os propongo encontraréis que el paso DATA es el más eficiente, el PROC SQL tarda un 75% más y el TRANSPOSE un 250% más. Por otro lado el TRANSPOSE podría ser mejor en códigos automáticos ya que no necesitamos parámetros, pero si trabajamos con macros al final el paso DATA es más efectivo.  A futuro empezaremos a parametrizar este tipo de sentencias SAS.

Por supuesto si tenéis cualquier duda o sugerencia… rvaquerizo@analisisydecision.es

39 comentarios en “Truco SAS. Transponer tablas con PROC TRANSPOSE, DATA o PROC SQL

  1. HOLA! FELICITÁNDOLES DE ANTEMANO POR SU VIRTUOSA AYUDA!…
    QUISIERA PREGUNTAR CÓMO PUEDO IR ELIMINANDO TABLAS DESDE UN PROGRAMA SAS, YA QUE A VECES SOLO QEREMOS QUEDARNOS CON UNA SOLA AL FINAL Y CON ANTELACIÓN LA EJECUCIÓN NOS HA DEJADO UN MONTÓN QUE PUEDE DIFICULTARNOS IDENTIFICAR AQUELLA QUE NOS INTERESA, SALUDOS…

  2. Hola.
    En el caso de que disponga de la siguiente tabla:

    IDCLIENTE MES IMPORTE
    1 1 100
    1 1 100
    2 1 200
    2 2 200
    3 1 400
    3 2 400
    3 3 400
    Se supone que el cliente 1 se ha gastado 100, el 2 200 y el 3 400.(el importe esta repetido)

    Con estos datos. Como indicarías el gasto mensual por cliente? y la media mensual?

  3. Creo que me expresado mal. 1 se corresponde con enero.Así para el mes de enero el gasto sería de 800, para febrero de 400 y para marzo de 400.
    Al final he aplicado un select UNIQUE y he conseguido que no se repitan las observaciones.
    Muchas Gracias!

  4. ¿Sabeis como deshacer el proc traspose?

    Me explico: tengo varios ficheros del tipo,

    Cliente 01/09/2011 02/09/2011 03/09/2011
    Pablo 5 6 7
    Genaro 8 9 3

    Y me gustaría obtener:

    Cliente Fecha Altas
    Pablo 01/09/2011 5
    Pablo 02/09/2011 6
    Pablo 03/09/2011 7
    Genaro 01/09/2011 8
    Genaro 02/09/2011 9
    Genaro 03/09/2011 3

    MUCHAS GRACIAS!!!

  5. data uno;
    input cli $ f1 f2 f3;
    datalines;
    pablo 5 6 7
    genaro 8 9 3
    ;run;

    proc sort data = uno; by cli; quit;

    proc transpose data=uno
    out=dos (rename =(_name_=f col1=altas));
    by cli;
    var f1 f2 f3;
    quit;

    Saludos.

  6. Hola! Tengo un archivo que desde mi punto de vista es más complicado. el formato es así
    SNP_NAME ID ALLELE1 ALLELE2
    UNO 100 A G
    DOS 100 T C
    TRES 100 A A
    UNO 101 G G
    DOS 101 T C
    TRES 101 A G
    Lo que quiero es tener un rengón por animal pero que me ponga los dos alelos; por ejemplo:
    UNO DOS TRES
    100 A G T C A A
    101 G G T C A G

    Agradezco su ayuda!!

  7. Adriana espero que te sirva esto, si es que he entendido bien el problema:
    data original;
    input snp_name $4. id $3. allele1 $1. allele2 $1.;
    datalines;
    UNO 100AG
    DOS 100TC
    TRES100AA
    UNO 101GG
    DOS 101TC
    TRES101AG
    ;

    proc sort data=original;
    by id;
    run;

    data original2;
    set original;
    by id;
    length uno $2 dos $2 tres $2;
    retain id uno dos tres;
    if first.id then do;
    id=”;uno=”;dos=”;tres=”;
    end;
    if snp_name=’UNO’ then uno=allele1||allele2;
    if snp_name=’DOS’ then dos=allele1||allele2;
    if snp_name=’TRES’ then tres=allele1||allele2;
    if last.id then output;
    drop snp_name allele1 allele2;
    run;

    Saludos

  8. Buenas Raul antes de nada felicitarte. Tienes una página que es un lujo. Que haría yo sin analisis y decisión. Segundo comentarte el problema que me ha surgido hoy en el curro. Tengo un cliente que realiza tres consultas en tres fechas distintas. que sucede tengo tres registros y para hacer una comprobación en las fechas tengo que sumar a la fecha justo anterior treinta días. La movida es si fueran dos consultas (dos registros) no habria problema porque con un proc sql puedo seleccionar la minima y sumarle 30 dias a la fecha pero si tengo mas de dos consultas por cliente no puedo hacerlo así porque no puedo apuntar a la justo anterior. Como lo harías? Yo creo que con un proc transpose podría pero no estoy muy seguro. Por cierto he oido hablar maravillas tuyas de mi compañero Manuel Vazquez. Espero poder conocerte pronto.

    Un fuerte abrazo

    Alonso Ares.

  9. Hola,

    Crea una variable orden con las fechas. Mira este ejemplo:

    data fechas;
    do cliente=1 to 100;
    fecha=”31DEC2011″d; output;
    fecha=”31DEC2011″d + ranpoi(2,30); output;
    fecha=”31DEC2011″d + ranpoi(2,60); output;
    end;
    format fecha ddmmyy10.;
    run;

    proc sort data=fechas;
    by cliente fecha;
    run;

    data fechas;
    set fechas;
    by cliente;
    retain orden;
    if first.cliente then orden=1;
    else orden+1;
    run;

    proc sql;
    create table t_fechas as select
    cliente,
    sum(fecha*(orden=1)) as fecha1 format ddmmyy10.,
    sum(fecha*(orden=2)) as fecha2 format ddmmyy10.,
    calculated fecha2 – calculated fecha1 as dif1,
    sum(fecha*(orden=3)) as fecha3 format ddmmyy10.,
    calculated fecha3 – calculated fecha2 as dif2
    from fechas
    group by 1;
    quit;

    Abuso del uso del SQL pero bueno. Si pones un orden la diferencia entre 2 y 1 y 3 y 2 es lo que necesitas. Busca crear un orden para que lo entiendas mejor.

    Recientemente estuve por allí a ver a Manu y a Jesús. Si vuelvo que te lo comenten y nos conocemos. Saludos.

  10. Raul el código esta de lujo pero hay otro follón. Puede que un cliente tenga 2 3 4 5 6 etc consultas luego tendría que generar un bucle para poder solucionar la movida.

    Luego por otro lado aquí haces de identificador el cliente. Donde estoy trabajando el nivel minimo que hace unico un registro es ppp es decir id_plan,id_producto,id_poliza,id_colectivo,id_cliente,id_compania luego si quiero sacar el first en esta opcion tendría que poner firs.cliente and first.ic_poliza and first.id_colectivo pero luego cuando voy comparando con sql me pierdo no se como implementarlo. No me quedaría mas remedio que hacer un bucle que llegue hasta el final y no se si con sql puedo ya que pueden ser 2 3 4 5 consultas. Espero tu respuesta y tu opinión.

    gracias

  11. Raul he hecho un apaño y creo que voy adelante. He unido los ppp’s en un solo campo con catx y me funciona. Creo que estoy a punto de conseguirlo. De todos modos dejare el codigo en la page. Creo que puede ser interesante.

    Un saludo

  12. Raul solo me queda un paso pero no logro sacarlo.

    es este código no me funciona en sas

    rsubmit;
    data &libref..resultado;
    merge %do i = 1 %to &maximoto.;
    &libref..totalescon (rename=fecha_servicio=fecha_servicio&i. where=(orden=&i.));
    by identificador;
    %end;
    run;
    endrsubmit;

    el error es el siguiente

    ERROR: The %DO statement is not valid in open code.
    1776 &libref..totalescon (rename=fecha_servicio=fecha_servicio&i. where=(orden=&i.));

    214
    23

    79
    WARNING: Apparent symbolic reference I not resolved.
    1776 &libref..totalescon (rename=fecha_servicio=fecha_servicio&i. where=(orden=&i.));

    22
    ERROR 214-322: Variable name & is not valid.

    ERROR 23-7: Valor no válido para la opción RENAME.

    ERROR 79-322: Expecting a =.

    ERROR 22-322: Error de sintaxis, se espera uno de los siguientes: un nombre,
    una cadena entrecomillada, una constante numérica, una constante de fecha y hora,
    un valor ausente, (, *, +, -, :, INPUT, NOT, PUT, ^, ~.

    1776 &libref..totalescon (rename=fecha_servicio=fecha_servicio&i. where=(orden=&i.));

    76
    ERROR 76-322: Syntax error, statement will be ignored.

    WARNING: Apparent symbolic reference I not resolved.
    ERROR: Error de sintaxis al analizar la cláusula WHERE.
    1777 by identificador;
    1778 %end;
    ERROR: The %END statement is not valid in open code.
    1779 run;

    NOTE: El Sistema SAS ha terminado de procesar este paso debido a errores.
    WARNING: The data set AALONSO.RESULTADO may be incomplete. When this step was stopped there were
    0 observations and 0 variables.
    WARNING: El conj. datos AALONSO.RESULTADO no se ha reemplazado por este paso se ha parado.
    NOTE: La sentencia DATA ha utilizado (Tiempo de proceso total):
    tiempo real 0.07 segundos
    tiempo de cpu 0.00 segundos

    NOTE: Remote submit to NODO complete.

    Gracias.

  13. Raul ya lo he solucionado.

    Te envío la solucion. Me ha servido la funcion lag
    Te lo envío con el fin de que haya un feedback y aunque me des mil vueltas puedas valorar la solución.

    rsubmit;
    data &libref..cons_multi_rec_con;
    set &libref..cons_multi_rec;
    identificador=catx(“”,id_plan,
    id_producto,
    id_poliza,
    id_colectivo,
    id_cliente,
    id_compania,
    id_especialidad,
    id_perfil,
    id_secuencia);
    run;
    endrsubmit;

    rsubmit;
    proc sort data=&libref..cons_multi_rec_con;
    by identificador fecha_servicio;
    run;
    endrsubmit;

    rsubmit;
    data &libref..cons_multi_rec_or;
    set &libref..cons_multi_rec_con;
    fecha_anterior=lag(fecha_servicio);
    by identificador;
    if first.identificador then es_sucesiva=0;
    else
    if fecha_servicio<=fecha_anterior+45 then
    es_sucesiva=1;
    else
    es_sucesiva=0;
    ;
    format fecha_anterior ddmmyy10.;
    run;
    endrsubmit;

  14. Que tal tengo un problema y no se si me puedas ayudar

    tengo un archivo así de esta naturaleza

    edo name1 id alelo1 alelo2
    M rs2289311 1001 G G

    y 50000 filas por cada id diferente pero quiero trasponerlas de esta manera en sas y no se como

    edo id 1001 1001 1002 1002 1003 1003
    M rs2289311 A G G G A G
    M rs1248628 T T T C T T
    M rs10762764 G T T T G T

    tener tantos pares de columnas como id en mi archivo con sus 50000 filas nada mas.

    y ademas tengo dos archivos de dimensiones diferentes con id similares los junto en un data set pero al momento de trasponer quedan espacios en blanco en el archivo con menos menos información y quiero que esos espacios con en blanco se les asigne valor de 5 al momento de trasponer. saludos y muchas gracias de antemano

  15. por cada id igual tengo 50,000 filas pero de name diferente, tengo 50 id diferentes= 2500,000 filas en total y quiero trasponerlo para que me quede

    edo name1 id alelo1 alelo2
    M N…..=1 1001 t c
    M rs2289311 1001 G G
    M xxxxx560 1001 t c
    M XXX rwwr 1001 t g
    M N..50,000 1001 g t
    M N…..=1 1002 g g
    M rs2289311 1002 t G
    M xxxxx560 1002 c c
    M XXX rwwr 1002 t g
    M N.=50,000 1002 c t

    y requiero trasponerla de la forma que arriba te comente

    edo name 1001 1001 1002 1002 1003 1003
    M rs2289311 A G G G A G
    M rs1248628 T T T C T T
    M rs10762764 G T T T G T

    con dos columnas por id, mi archivo tiene 50 id diferentes, debe tener 100 columnas con id, pero de 50,000 filas que cada columna debe contener los 50,000 alelos , resultando dos columnas por id.
    Una disculpa si soy malo explicando estas cosas
    saludos.

  16. Buenas Raul. No quiero interrumpirte pero me estoy comientdo la cabeza con un caso del que no llego la conclusión. Arriba hemos hablado de transposicion. Pero es transponer filas por columnas y si quisiera hacer la inversa
    suponte tengo
    suministro consumo1 consumo2 consumo3
    1 12 12 15
    y me interesa que queda
    suminitro consumo
    1 12
    1 12
    1 15

    Que ideas me das.

    Un cordial saludo.

  17. Hola, imagino que estás trabajando en SAS. En ese caso deberías hacer esto:

    data total;
    set
    datos (keep=suministro consumo1 rename=consumo1=consumo)
    datos (keep=suministro consumo2 rename=consumo2=consumo)
    datos (keep=suministro consumo3 rename=consumo3=consumo)
    run;

    unir horizontalmente tu dataset tantas veces como tengas tu variable consumo y a la vez renombrar.

    Saludos.

  18. Hola tengo los siguientes datos:
    NUM GRUPO LETRA MES HAI
    91 E 1 1 64
    91 E 1 2 64
    91 E 1 3 64
    91 E 1 4 32
    91 E 1 5 32
    91 E 1 6 32
    91 E 1 7 128
    91 E 1 8 256
    91 E 1 9 64
    91 E 1 10 32
    96 E 1 1 64
    96 E 1 3 512
    96 E 1 4 128
    96 E 1 5 128
    96 E 1 6 128
    96 E 1 7 128
    103 E 2 1 0
    103 E 2 2 0
    103 E 2 3 0
    103 E 2 4 0
    103 E 2 5 64

    Y me quiero quedar con un sólo registro de acuerdo a un punto de corte de la varaible “HAI”, es decir voy mirando el paciente 91 y me quiero quedar sólo con el primer MES que el paciente 91 tenga un “HAI” < 32. Se entiende? Cómo puedo hacer?

  19. Hola Karen, para que sea más sencillo te recomiendo que hagas los mínimos por las variables que quieres y que después cruces. En el ejemplo que has mandado:

    data datos;
    input NUM GRUPO $ LETRA MES HAI;
    cards;
    91 E 1 1 64
    91 E 1 2 64
    91 E 1 3 64
    91 E 1 4 32
    91 E 1 5 32
    91 E 1 6 32
    91 E 1 7 128
    91 E 1 8 256
    91 E 1 9 64
    91 E 1 10 32
    96 E 1 1 64
    96 E 1 3 512
    96 E 1 4 128
    96 E 1 5 128
    96 E 1 6 128
    96 E 1 7 128
    103 E 2 1 0
    103 E 2 2 0
    103 E 2 3 0
    103 E 2 4 0
    103 E 2 5 64
    ;
    run;

    proc sql;
    create table minimos as select
    num,
    hai,
    min(mes) as mes
    from datos
    group by 1,2;
    quit;

    proc sql;
    create table datos2 as select
    a.*
    from datos a, minimos b
    where a.num = b.num and a.hai = b.hai and a.mes=b.mes;
    quit;

    Creo que se entiende perfectamente con el ejemplo. Un saludo.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

[bws_google_captcha]