programacao

SQL dentro do SAS

O SQL é uma linguagem utilizada comumente na manipulação de dados. É bastante intuitiva e fácil de utilizar. Para selecionar, por exemplo, uma coluna denominada Nome contendo os nomes de clientes da tabela XYZ, o comando a ser utilizado é praticamente a frase “selecionar nome da tabela XYZ”, mas em inglês:

SELECT Nome from tabela XYZ

Ou então, você pode selecionar todas as colunas da base XYZ com o comando ‘*’:

SELECT * from tabela XYZ

Como você pode ver, é bem intuitivo.

O SQL é uma das linguagens embutidas no SAS e você pode acioná-la utilizando o comando proc sql seguido pelo tradicional ponto e vírgula e finalizá-lo com o comando run do SAS. Para você ver como pouco muda, o comando de SQL utilizado acima ficaria da seguinte forma no SAS:

proc sql;
    select * from tabela XYZ;
run;

Caso você já acompanhe o blog, ou tenha lido alguns outros códigos, você deve ver muitas vezes ao invés do asterisco sozinho, algo como a.*. Isso ocorre porque quando temos duas tabelas, nós as denominamos de ‘a ‘e ‘b’ (ou t1, t2, etc.), sendo assim você precisa mencionar de qual tabela você está selecionando a coluna. O código acima, caso quiséssemos chamar a tabela XYZ de ‘a‘, ficaria da seguinte forma:

proc sql;
    select a.* from tabela XYZ as a;
run;

Veja que para uma tabela só não tem muita diferença, mas imagine com duas ou três como já complicaria escrever simplesmente select Nome. Se o SAS pudesse falar, ele diria: seleciono de onde? De XYZ ou das outras tabelas?

Veja que a linguagem de SQL embutida no SAS facilita bastante a manipulação dos dados e fornece aos usuários alternativas quando a lógica com o data step for mais complicada.

Como o código acima vai apenas mostrar para você a seleção feita, para utilizarmos a informação gerada dentro do próprio SAS podemos criar uma tabela. E, novamente, o código é uma mera tradução do inglês: Create Table:

proc sql;
    create table Tabela_Nomes as
    select Nome from tabela XYZ;
run;

Vamos utilizar a tabela abaixo com algumas pessoas, a renda que elas possuem e a origem dessa renda, para demonstrar outros comandos utilizados no proc sql:

exemplo_sql

Os principais comandos a serem lembrados no SQL, além do select, são:

  • Where: Um tipo de filtro, semelhante ao if. Vamos supor que a base contenha a coluna renda com o salário dos clientes e você queira apenas o nome de quem possui renda superior a mil reais. Agora, você terá que selecionar duas colunas, Nome e Renda, e a tabela com esses clientes seria criada da seguinte forma:
proc sql;
    create table Tabela_Renda as
    select id, Nome, Renda
    from tabela_exemplo
    where renda > 1000;
run;

tabela_renda_cliente

  • Group by: Serve para agrupar os dados por algum campo em comum. Vamos pensar agora nos clientes que possuem várias fontes de renda, aquela pessoa que além do salário da empresa também possui rendas com aluguéis ou trabalhos de freelancer. Sendo assim, a base terá várias linhas com rendas diferentes para esse cliente. Se você quiser a renda total dele, você terá que somar essas rendas diferentes e agrupar pelo nome:
proc sql;
    create table Tabela_Renda_Cliente as
    select id, Nome, sum(Renda) as Renda_Total
    from tabela_exemplo
    group by id, nome
    having Renda_Total > 1000;
run;

tabela_renda_cliente1

  • Having : Bem semelhante ao where, é utilizado para o filtro depois de alguma tratativa. Como queríamos selecionar os clientes com renda acima de mil reais, e com o where só considerávamos a renda de um emprego, agora podemos filtrar os clientes com renda total acima de mil reais utilizando o having:
proc sql;
    create table Tabela_Renda_Cliente_2 as
    select id, Nome, sum(Renda) as Renda_Total
    from tabela_exemplo
    group by id, nome
    having Renda_Total > 1000;
run;

tabela_renda_cliente2

Order By: Ordena a tabela de acordo com algum campo, na ordem crescente.

Poderíamos ter gerado a renda dos clientes, mas ordenando pela renda:

proc sql;
    create table Tabela_Renda_Cliente_3 as
    select id, Nome, sum(Renda) as Renda_Total
    from tabela_exemplo
    group by id, nome
    having Renda_Total > 1000
order by Renda_Total;
Run;

tabela_renda_cliente3

Anúncios

Pratique SQL !

Escrevi bastante sobre SQL esses dias, quem quiser praticar e não tiver em casa, ou não quiser instalar nenhuma versão, você pode utilizar no seu browser com o link abaixo:

SQL Fiddle

Veja que na tela da esquerda você já tem uma construção da tabela localizado ao final da tela e do lado direito você pode escrever diversas queries e executá-las com o botão RUN SQL. Vamos praticar!

Como obter a frequência de uma variável no SAS

Mencionei o comando proc freq do SAS no post Frequência no R utilizando o pacote Hmisc. Agora, vamos nos aprofundar um pouco e ver o que é possível fazer com este comando.

O proc freq traz a frequência das variáveis que você quer analisar ou fazer qualquer tipo de análise descritiva, ou seja, traz o número de vezes que determinado valor da variável aparece. A sintaxe mais simples do comando é:
proc freq data= base_de_dados; 
    table variavel1 variavel2 ... variaveln; 
run;
Vamos utilizar os dados de crédito german_credit_2 como exemplo.
Assim como já fizemos no R, no post Árvore de Decisão no R, vamos discretizar as variáveis Creditability e CreditAmount, para só depois gerarmos as frequências.
No caso de Creditability, a discretização serve para visualizar melhor quem é bom e quem é mau pagador. No caso de CreditAmount, não conseguimos gerar uma frequência que seja útil, pois a variável é contínua. Pior ainda, se a base for muito grande, o comando muito provavelmente não irá funcionar. Afinal de contas, você estará trazendo o número de vezes que cada valor aparece. Separar em intervalos é o ideal nesse caso.
Note que no meu caso, a base foi importada para o SAS com o nome german_credit_21.
**** discretiza variaveis creditability e amount ****;
data german_credit_21_v2;
    set german_credit_21;

    length d_creditability $4.;
    if creditability = 1 then
    d_creditability = 'good';
    else d_creditability = 'bad';

    length d_creditAmount $9.;
    if creditAmount <= 2500 then
    d_creditAmount = '0000-2500';
    else if creditAmount > 2500 and creditAmount <= 5000 then
    d_creditAmount = '2500-5000';
    else d_creditAmount = '+5000';

run;

**** Frequencia de Creditability e Amount ****;
proc freq data= german_credit_21_v2;
    table d_creditability d_creditAmount;
run;
unnamed (3)unnamed (4)
Na primeira tabela é possível ver a frequência e qual o percentual que representa do todo. Temos 300 clientes maus, sendo que isso representa 30% da nossa população. Além disso, temos também a frequência acumulada. Temos, na segunda tabela, 537 clientes que possuem dívida entre 0 e 2500, e 812 clientes que possuem entre 0 e 5000, sendo que esses clientes representam 81,20% da nossa população.
É possível também obter a frequência cruzada, ou seja, quantos clientes bons e ruins há em cada faixa de dívida:
**** Frequencia Cruzada de Creditability e Amount ****;
proc freq data= german_credit_21_v2 ;
    table d_creditability*d_creditAmount;
run;
unnamed (5)
E se você quiser deixar a tabela um pouco menos poluída, mantendo apenas a frequência, utilize o norow, nocol e/ou nopercent:
**** Nao traz frequencia por linha, coluna nem percentual ****;
proc freq data= german_credit_21_v2;
    table d_creditability*d_creditAmount / norow nocol nopercent;
run;
unnamed
Também é possível salvar a tabela de frequência utilizando o out:
**** Cria uma tabela de output chamada tabela_frequencia ****;
proc freq data= german_credit_21_v2;
    table d_creditability*d_creditAmount / out= tabela_frequencia norow nocol nopercent;
run;
E se você quiser ordenar a tabela de frequência, basta acrescentar o order = Freq:
**** Sem ordenar ****;
proc freq data= german_credit_21_v2 ;
    table Guarantors;
run;

unnamed (8)

**** Ordena pela frequencia ****;
proc freq data= german_credit_21_v2 order= freq;
    table Guarantors;
run;
unnamed (7)
Assim como fizemos para o Creditability, nós poderíamos ter atribuído nomes ao invés de índices ao Guarantors. Porém, para não alterarmos nossa base original, poderíamos simplesmente aplicar uma formatação:
**** formata o nome ****;
proc format;
   value formatacao
        1 = 'none'
        2 = 'co-applicant'
3 = 'guarantor';
run;

**** gera frequencia e apresenta com nome formatado ****;
proc freq data = german_credit_21;
   tables Guarantors;
   format Guarantors formatacao.; 
run;
unnamed (6)

Trabalhando com espaços e acentos no SAS

A pior coisa é utilizar campos com espaços em branco e acentos quando se está programando. Não é recomendável e muitas vezes resulta em erros. Porém, às vezes temos que lidar com isso porque alguém construiu uma base com campos dessa forma e não quer mudar.

No SAS é mais fácil lidar com isso, basta acrescentar aspas e a letra n. É bem semelhante quando você utiliza data, só que precisa trocar o ‘d’ por ‘n’.

Vamos supor que você receba os dados da turma de alunos do post Visualizando seus dados: histograma e queira criar uma marcação chamada Reprovação que receberá o número 1 para alunos que tiraram notas menor que 6, basta você criar o campo ‘Reprovação’n:

data dados_v2;
    set dados;
    if notas < 6 then 'Reprovação'n = 1;
    else 'Reprovação'n = 0;
run;

Poderíamos também cair em uma situação que seria necessário criar um campo Data de Atualização com a data do dia de hoje. Para isso, temos duas opções:

## primeira opcao:
data dados_v2;
    set dados;
    'Data de Atualização'n = today();
run;

## segunda opcao:
data dados_v2;
    set dados;
    'Data de Atualização'n = '13JUL2016'd;
run;

Contador e função oposta ao lag no SAS

Veja que exercício de lógica interessante:

Pense em uma base com clientes que possuem vários carros. Você tem linhas contendo o nome do cliente, o nome do carro que ele possui e a data de compra do veículo. Você precisa descobrir quais clientes possuem apenas um carro. A que eu vou demonstrar talvez não seja a mais eficiente, até porque eu precisei de alguns dados ao longo do estudo, como por exemplo qual a ordem em que o veículo foi comprado. Sem isso, não sei se eu teria pensado de outra forma. O legal da forma que foi feito o tratamento é que trabalha o raciocínio lógico e ainda apresenta alguns algoritmos úteis.

O passo a passo utilizado é o seguinte: ordene seus dados pelo nome e data de compra do veículo; crie um contador para representar se é o primeiro carro que aparece do cliente; crie uma coluna adicional para saber qual o contador da linha seguinte. Veja que se você tiver na linha 10 o valor 1 no contador, e na linha 11 você tiver o valor 2, é sinal de que o cliente possui pelo menos dois veículos. No entanto, se você tiver o valor 1 no contador da linha 10 e o valor 1 no contador da linha 11, o seu cliente da linha 10 possui apenas 1 carro.

Foi falado um pouco sobra a função lag() do SAS no post Média Móvel (Bônus: Código SAS e função lag). Ela simplesmente pega o valor da linha de cima da coluna que você quer. Por exemplo, se eu fizer lag(conta_carro), eu vou saber qual o valor da coluna conta_carro da linha anterior a que estou verificando. No caso apresentado nesse post, precisamos de uma função que faça o contrário da lag, ao invés de retornar o valor da linha anterior, deve retornar o valor da linha seguinte. Você verá que não existe uma função pronta para esse caso, mas o script é bem simples e se encontra na Parte 5 do código abaixo.

Veja o exemplo completo que você pode simular e acompanhar o passo a passo explicado acima:
**** Parte 1: Cria uma base de clientes ****;
data base_clientes;
    input nome $ carro $ DtCompra mmddyy8.;
    cards;
    Andre Onix 02/05/15
    Andre Palio 12/10/13
    Andre Uno 01/05/14
    Paulo Idea 10/06/15
    Jose Vectra 01/04/16
    Jose Eco 12/05/16 28
    Maria Corsa 10/25/14
    Maria Celta 02/01/16
    Renata Onix 02/09/12
    Renata Palio 06/05/11
    Renata Fit 02/11/16
    Renata Civic 05/22/14
    Michael Fit 07/13/15
    Bianca Clio 09/05/15
    Thais March 08/05/16
    Renan Palio 11/05/13
    Renan Gol 11/05/14
    ;
run;

**** Parte 2: Trata a variavel data ****;
data base_clientes_v2;
    set base_clientes;
    format DtCompra date9.;
run;

**** Parte 3: Ordena pelo nome do cliente e data da compra do veiculo ****;
proc sort data= base_clientes_v2; by nome DtCompra; run;

**** Parte 4: Cria um contador do numero de carros = conta qtas vezes o nome aparece****;
data base_clientes_v3;
    set base_clientes_v2;
    by nome;
    primeiro = first.nome;
    ultimo = last.nome;
    if primeiro = 1 then
    conta_carro = 1;
    else conta_carro+1;
run;

**** Parte 5: Cria uma coluna com o valor da linha seguinte da coluna conta_carro ****;
data base_clientes_v4;
    recno=_n_+1;
    set base_clientes_v3 end=last;
    if not last
    then set base_clientes_v3 (keep=conta_carro rename=(conta_carro=next_conta_carro)) point=recno;
    else call missing(next_conta_carro);
run;

**** Parte 6: Mantem apenas quem tem conta_carro = 1 e que a proxima linha eh 1 tb ****;
data clientes_com_um_carro;
    set base_clientes_v4;
    if conta_carro = 1 and next_conta_carro = 1;
run;

Seja mais ágil utilizando firstobs e inobs no SAS

Quando se trabalha com milhões de dados, qualquer tratamento vai consumir alguns bons minutos – ou até horas – do seu dia. Ou seja, se você escrever seu código, rodar e ele apresentar algum erro, lá se vai uma parte do seu dia. Como ninguém é perfeito, esse processo de escrever + executar + problemas se repete várias vezes ao longo do dia. Logo, você vai gastar horas para escrever um código, muitas vezes simples, e vai passar horas ocioso. Porque você vai escrever um trecho do código, vai colocar para rodar, vai esperar alguns minutos e aí sim vai resolver um problema. Para depois fazer isso de novo. E aí segue seu dia, chato e improdutivo.

Evitar o problema descrito é mais simples do que parece. Ao menos parte dele é solucionado testando em uma amostra. Por exemplo, você quer converter o formato de data da sua base. Não escreva o código e fique lá 20 minutos rodando enquanto você não faz nada, ou finge fazer. Teste essa conversão em 10 linhas da base de dados e veja o que ocorre. Se o processo for grande, envolver várias etapas, siga testando com essas 10 linhas fazendo todos os tratamentos, seja enriquecendo a base com dados de outras fontes, seja tratando os campos. Para pegar apenas 10 linhas, você tem as duas opções abaixo, uma para quem usa data step e outra para quem usa proc sql:

* primeira opcao via data step
* seleciona do primeiro ao decimo elemento
* util caso voce queira selecionar elementos do meio da tabela;
data seleciona_amostra;
    set base (firstobs = 1 obs = 10);
run;

* segunda opcao via proc sql;
proc sql inobs = 10;
    select * from base;
run;

Bem simples. Desta forma não precisa mais escrever um trecho, deixar rodando por 1h para só depois checar se deu certo.

Espero que tenha ajudado. Dúvidas, sugestões ou críticas é só escrever aí embaixo.

Machine Learning no Nerdcast

jovemnerd

Quem diria que meu podcast preferido faria um episódio de uma das minhas áreas de estudos preferida. Para ouvir é só clicar no link:

NerdTech 5 – Machine Learning

Para quem não conhece muito, a definição de aprendizado de máquina (Wikipedia):

A aprendizagem automática ou aprendizado de máquina (em inglês: “machine learning”) é um sub-campo da inteligência artificial dedicado ao desenvolvimento de algoritmos e técnicas que permitam ao computador aprender, isto é, que permitam ao computador aperfeiçoar seu desempenho em alguma tarefa.

Frequência no R utilizando o pacote Hmisc

Como estou acostumado com o proc freq do SAS para validar algumas informações e ver se faz sentido o que estou fazendo, procurei algo semelhante no R. Encontrei o describe() do pacote Hmisc. Claro que deve existir outro pacote que faz isso, mas esse foi o primeiro que encontrei.

O proc freq do SAS, como o nome já indica, calcula a frequência de seus dados. Por exemplo, se você tem uma base que contém homem e mulher, você pode utilizar um código parecido com esse:

proc freq data = base_de_dados; table sexo; run;

Onde base_de_dados seria o nome da sua base e sexo o nome da coluna contendo o sexo das pessoas.Esse código retorna para você o número e a porcentagem de pessoas de cada sexo. Analogamente, você poderia fazer o seguinte código no R:

install.packages("Hmisc");
library("Hmisc");
describe(base_de_dados$sexo);

E o resultado seria o mesmo.

Para não ficar muito abstrato, segue um exemplo com a base de dados german_credit_2 já utilizada no post Árvore de Decisão no R. Eu tinha discretizado algumas variáveis naquela época, e estava refazendo isso hoje. Apenas para garantir que tinha feito da forma correta, utilizei o describe(). Veja que ele me retornou que 30% da minha base possui Creditability ‘bad’ e 70% ‘good’. Além disso, pude ver também como foi dividido o CreditAmount. Bem simples, uma função só e você já tem uma bela descrição para trabalhar:

install.packages("Hmisc");
library("Hmisc");
describe(dados);

proc_freq

Macros e a expressão Let no SAS

No post As boas práticas de programação foi mencionado que o código deveria ser o menos estático possível? No SAS isso pode ser feito muito bem com a criação de Macros e com a expressão %LET.

CONCEITO DE MACROS

De acordo com o Wikipedia “Uma macro (abreviação para macroinstrução), em ciência da computação, é uma regra ou padrão que especifica como uma certa sequência de entrada (frequentemente uma sequência de caracteres) deve ser mapeada para uma substituição de sequência de saída (também frequentemente uma sequência de caracteres) de acordo com um procedimento definido.”

Ou seja, uma macroinstrução é uma sequência de regras que o programa deve seguir. Quando você cria uma macro, você cria um programinha que vai executar uma série de procedimentos.

MACROS NO SAS

No SAS, assim como em outros programas, você pode deixar uma série de nomes flexíveis dentro da sua sequência de procedimentos, para que eles rodem diversas vezes soltando saídas diferentes.

Por exemplo, suponha que a gente tenha a base FATURA_YYYYMM gerada mensalmente com as faturas dos clientes no mês corrente. Você está realizando um estudo com clientes que possuem faturas de valor acima de R$ 200,00. Porém, para seu estudo, você vai pegar clientes dos primeiros três meses do ano. Se a pessoa não conhece muito sobre macros, é esperado que ela faça um código como esse:

data faturas_jan;
     set fatura_201601;
     if vlr_fatura > 200;
run;

data faturas_fev;
     set fatura_201602;
     if vlr_fatura > 200;
run;

data faturas_mar;
     set fatura_201603;
     if vlr_fatura > 200;
run;

Não parece a melhor maneira. Alguém mais calejado já estaria quebrando a cabeça imaginando que existe um jeito mais prático. Existe, é utilizando as macros do SAS.

No começo parece até algo mais complicado, mas é bem simples. A sintaxe é da seguinte forma:

%macro nome_da_macro(input 1, input2, ...);
     comando 1;
     comando 2;
     .
     .
     .
%mend;
%nomedamacro(input 1, input2, ...);

Sendo assim, podemos criar a macro EXTRAI_FATURA na qual vamos colocar como inputs os nomes das bases que serão lidas e como output as bases de saída:

%macro extrai_fatura(input, output);
data &input;
     set &output;
     if fatura > 200;
run;
%mend;

%extrai_fatura(fatura_201601, extracao_janeiro);
%extrai_fatura(fatura_201602, extracao_fevereiro);
%extrai_fatura(fatura_201603, extracao_marco);

Veja que a macro está lendo as bases que declaramos como input e está soltando os resultados em bases com o nome que passamos como output (extracao + nome do mês).

Note que precisamos colocar o caractere ‘&’ antes do nome das variáveis que serão substituídas. Note também, que a macro pode ficar ainda mais flexível com você passando o valor que quiser em cada extração:

%macro extrai_fatura(input, output, valor);
data &input;
     set &output;
     if fatura > &valor;
run;
%mend;

%extrai_fatura(fatura_201601, extracao_janeiro, 100);
%extrai_fatura(fatura_201602, extracao_fevereiro, 200);
%extrai_fatura(fatura_201603, extracao_marco, 300);

Nessa segunda macro, podemos colocar o valor que quisermos em cada extração. A primeira execução vai ler a fatura_201601 e retornar a extracao_janeiro apenas com clientes que possuem fatura acima de R$ 100,00. A segunda execução lê a fatura_201602 e retorna a extracao_fevereiro apenas com os clientes de fatura acima de R$ 200,00.

%LET NO SAS

A expressão %LET é mais fácil ainda que a macro, embora não ache ela tão poderosa.

Essa expressão também serve para alterar parâmetros no meio do seu código, porém, diferentemente da macro, você declara a variável uma vez no início do código. Uma das facilidades que eu vejo no LET é que permite você rodar o código por partes, o que facilita bastante para pegar erros. Facilita para processos executados periodicamente em que se altera algumas variáveis de inputs. Vamos supor que agora você não vai fazer um estudo, mas você quer fazer uma campanha com os clientes de alta renda e todo mês, assim que a base de faturamento for gerada, você vai pegar os clientes que possuem faturas acima de R$ 500,00. Você faria o seguinte código no mês de janeiro:

%let base_fatura = fatura_201601;
data extracao_campanha;
     set &base_fatura;
     if fatura > 500;
run;

Novamente, a gente declara uma variável e insere ela no código com o comando ‘&’ seguido do nome do input. No mês seguinte, você precisaria apenas trocar o valor atribuído ao %let e executar o mesmo código:

%let base_fatura = fatura_201602;
data extracao_campanha;
     set &base_fatura;
     if fatura > 500;
run;

Pense agora na quantidade de possibilidades. Deixar o valor da fatura, o nome da base de saída e outras variáveis flexíveis também. Pense agora se você tivesse 50 condições ao invés dessa única. Facilitaria bastante, não?