Archivo de la etiqueta: proc sql

Trucos SAS. Trasponer con SQL para torpes

trasponer_sql1.png

Trasponer datos con SAS es un tema que genera un gran número de consultas en Google, por lo tanto genera un gran número de visitas a este blog. Ya hay un monográfico al respecto pero hoy quería volver a contar la trasposición de datos con SQL y SAS pero a un nivel más bajo para que sea lo más sencillo posible. En el ejemplo partimos de una tabla con 3 variables, un id_cliente, un campo tipo y un campo precio. Cada tipo tiene un precio y necesitamos que nuestro dataset tenga un registro por id_cliente y 3 precios, uno por cada tipo. El ejemplo en código SAS:

data datos;
input id_cliente $ tipo precio;
datalines;
A 1 100
A 2 150
A 3 120
B 1 200
B 2 250
B 3 220
C 1 300
C 2 350
C 3 320
D 1 400
D 2 450
D 3 420
;run;

Esta es nuestra tabla de partida, ahora vamos a generar tres variables en función de la variable tipo:

data datos2;
set datos;
precio_1 = (tipo = 1) * precio;
precio_2 = (tipo = 2) * precio;
precio_3 = (tipo = 3) * precio;
run;

trasponer_sql2.png

Si vemos la tabla resultante tiene una forma de matriz con precios y ceros en función de la variable tipo. Ahora si sumarizamos esas variables y agrupamos por el id_cliente la forma de la tabla resultante es el objetivo deseado:

proc sql;
create table tdatos as select
id_cliente,
sum(precio_1) as precio_1,
sum(precio_2) as precio_2,
sum(precio_3) as precio_3
from datos2
group by 1;
quit;

Bien, pues esta es la “filosofía” de la trasposición con SAS en SQL. Pero esto lo podemos hacer en un solo paso:

proc sql;
create table tdatos as select
id_cliente,
sum((tipo=1)*precio) as precio_1,
sum((tipo=2)*precio) as precio_2,
sum((tipo=3)*precio) as precio_3
from datos
group by 1;
quit;

Y así podemos trasponer de forma sencilla en SAS sin emplear el PROC TRASPOSE que tiene alguna que otra limitación. Y por supuesto nos sirve para trasponer siempre que utilicemos SQL, con ORACLE, POSTGRES,… Creo que esta vez es muy sencillo de entender. Saludos.

Trucos SAS. Validación de consultas con PROC SQL

Hay ocasiones en las que lanzamos consultas a las BBDD con SAS y necesitamos saber si son correctas. Quería plantearos un truco SAS para PROC SQL que valida las consultas antes de ser ejecutadas. Empiezo el truco en la línea habitual, creo un dataset de ejemplo y os presento como realizar la validación, de este modo vosotros podéis copiar y pegar el código en una sesión de SAS y comprobar su funcionamiento. Datos aleatorios de partida:

data datos;
array importe(5);
drop j;
do id_cliente=1 to 10000;
do j=1 to 5;
importe(j) = rand("uniform")*10000;
end;
output;
end;
run;

10.000 observaciones y 6 variables. Hemos de realizar una consulta sobre esta tabla y primero hemos de validarla, uno de los medios para realizar esta tarea es la opción NOEXEC dentro de PROC SQL:

proc sql noexec;
create table datos1 as select
id_cliente,
importe1,
importe2
fron datos
where importe6 >= 5000;
quit;

NOEXEC no ha ejecutado la consulta y nos devuelve un error, está escrito fron en vez de from. Pero NO HAY QUE EMPLEAR PARA VALIDAR LA OPCIÓN NOEXEC, lo demuestro con el siguiente código:

proc sql noexec;
create table datos1 as select
id_cliente,
importe1,
importe2
from datos
where importe6 >= 5000;
quit;

Aparentemente la consulta no tiene ningún error. Sin embargo en la cláusula where tenemos importe6, una variable que no existe. Es decir, NOEXEC sólo nos ha validado la sintaxis, no la consulta. Así pues nos olvidamos de NOEXEC. Para realizar validaciones emplearemos la sentencia VALIDATE:

proc sql ;
validate
/*create table datos1 as*/ select
id_cliente,
importe1,
importe2
from datos
where importe6 >= 5000;
quit;

VALIDATE siempre precede a SELECT y no sólo nos valida la sintaxis de nuestra consulta sino que además nos valida los campos que incluimos en ella. En el ejemplo que os he puesto lo que hacemos es comentar CREATE TABLE y poner VALIDATE. Así podemos dejar lanzadas consultas a servidores en horario de menor concurrencia con la seguridad de que la ejecución es correcta, algo que puede provocar cierta inquietud. Saludos.

Uso de CASE en PROC SQL

Vamos a estudiar como funciona CASE en un PROC SQL. Son palabras que aparecen en las búsquedas de Google y también he observado que el número de visitas al blog ha descendido en los últimos días y no sólo es debido a las vacaciones navideñas. El 60% de los clicks a AyD vienen por temas de SAS y en los últimos días tengo muy olvidados los mensajes de esta categoría. Además en el plazo de 2 días voy a dejar de trabajar con esta herramienta por lo que, es posible, que se reduzcan aun más. En fin, a lo que voy, CASE en el PROC SQL. Case nos permite crear campos condicionales dentro del bloque SELECT de una query de PROC SQL:


*DATASET ALEATORIO;
data aleatorio;
do i=1 to 200;
grupo1=1;
if mod(i,2)=0 then grupo1=2;
if mod(i,3)=0 then grupo1=3;
grupo2=rand("binomial",0.05,5);
normal=rand("normal");
uniforme=rand("uniform")*1000;
if grupo1=1 then uniforme=.;
poisson=ranpoi(34,25);
output;
end;
run;

Partimos de un dataset aleatorio de 200 observaciones con 2 variables de grupo y 3 variables aleatorias. Sigue leyendo Uso de CASE en PROC SQL

Trucos SAS. Unión de múltiples tablas SAS con un nombre parecido

Ha llegado una búsqueda y ponemos el truco. Creo que ya lo puse pero no está mal de recordarlo.

1. Creamos 20 datasets aleatorios con 10 observaciones cada uno:
%macro doit;
%do i=1 %to 20;
data zzdatos_&i.;
do i=1 to 10;
output;
end;
run;
%end;
%mend;

Un bucle fácil de macros ha generado 20 dataset que se llaman ZZDATOS_n. Recomiendo siempre emplear nombres "absurdos" para el trabajo con esta metodología. Sigue leyendo Trucos SAS. Unión de múltiples tablas SAS con un nombre parecido

Trucos SAS. Identificar registros duplicados

Muy rápido, para identificar registros duplicados existen múltiples formas. Seguramente haré un monográfico sobre este tema pero de momento dejo una píldora:


data aleatorio;
do i=1 to 100000;
id=ranpoi(23456,56781);
if ranuni(5)>=0.3 then output;
end;
run;
proc sql;
create table repes (where=(rep>1)) as select
id, count(id) as rep
from aleatorio
group by 1;
quit;
proc sql;
create table repes (where=(rep=1)) as select
id, count(id) as rep
from aleatorio
group by 1;
quit;

Contamos registros y empleamos where como opción de escritura. Muy fácil y perfectamente entendible. No puedo entretenerme más que mi hija me reclama...

Trucos SAS. Informes de valores missing

A continuación os planteo como truco SAS una duda que nos mandaba LILIANA. Ella necesitaba estudiar los valores perdidos de las tablas de una librería determinada. En este caso vamos a estudiar los missing de las variables numéricas de una librería, de forma análoga se puede hacer con las alfanuméricas. Como siempre vamos a trabajar con un ejemplo que parte de tablas generadas aleatoriamente. Comenzamos generando estas tablas: Sigue leyendo Trucos SAS. Informes de valores missing