1 Sumário executivo

A tomada de decisão com base em dados do mundo real do SUS requer processos validados de extração, transformação e carga. A consolidação de dados de dispensação de medicamentos no escopo dos Protocolos Clínicos e Diretrizes Terapêuticas (PCDT) requer a extração de bilhões de registros de dados ambulatoriais e conciliação com o código do usuário do SUS de modo a viabilizar estudos observacionais com dados administrativos. O objeto do presente produto foi descrever e avaliar o processo de Extração, Transformação e Carga dos dados de dispensação de medicamentos que compõe o repositório da Sala Aberta de Inteligência em Saúde - Sabeis contemplando doenças selecionadas (Comportamento Agressivo no Autismo; Esquizofrenia; Transtorno Afetivo Bipolar do tipo I; Transtorno Esquizoafetivo). Os arquivos DBC foram processados em linguagem Bash e projeto R, modelados relacionalmente e carregados em Sistema Gerenciador de Banco de Dados, PostgreSQL. O processo foi validado em relação aos dados oficiais do TabNet.
Na SABEIS, foram identificacos usuários dos medicamentos avaliados: LAMOTRIGINA 25 MG (POR COMPRIMIDO); CLOZAPINA 100 MG (POR COMPRIMIDO); CLOZAPINA 25 MG (POR COMPRIMIDO); LAMOTRIGINA 100 MG (POR COMPRIMIDO); LAMOTRIGINA 50 MG (POR COMPRIMIDO); OLANZAPINA 10 MG (POR COMPRIMIDO); OLANZAPINA 5 MG (POR COMPRIMIDO); QUETIAPINA 100 MG (POR COMPRIMIDO); QUETIAPINA 200 MG (POR COMPRIMIDO); QUETIAPINA 25 MG (POR COMPRIMIDO); QUETIAPINA 300 MG (POR COMPRIMIDO); RISPERIDONA 1 MG (POR COMPRIMIDO); RISPERIDONA 1,0 MG/ML SOLUÇÃO ORAL (POR FRASCO DE 30 ML); RISPERIDONA 2 MG (POR COMPRIMIDO); RISPERIDONA 3 MG (POR COMPRIMIDO); ZIPRASIDONA 40 MG (POR CAPSULA); ZIPRASIDONA 80 MG (POR CAPSULA). Entre 06/2016 e 03/2022, foram 731.923 usuários (14,7% do total de usuários de medicametnos presente na SABEIS), correspondente a 19.581.260 registros de dispensação (14,7% do total da SABEIS) de 17 medicamentos para 23 diagnósticos primários. Os usuários de medicamentos registrados no SIA foram atendidos em 214 estabelecimentos, sendo residentes de 5.339 municípios. Considerou-se o método válido para utilização pela gestão dO SUS e em estudos observacionais.

2 Introdução

O uso dos medicamentos disponibilizados pelo SUS via Componente Especializado da Assistência Farmacêutica (CEAF) é regido pelo respectivo documento de Protocolos Clínicos e Diretrizes Terapêuticas (PCDT). A Secretaria de Ciência, Tecnologia, Inovação e Insumos Estratégicos em Saúde do Ministério da Saúde (SCTIE) por meio do Departamento de Gestão e Incorporação de Tecnologias e Inovação em Saúde (DGITIS) coordena o processo de formulação do PCDT e oferece suporte para incorporação e desincorporação de tecnologias no âmbito da Comissão Nacional de Incorporação de Tecnologias no SUS (CONITEC). Os medicamentos são disponibilizados pela assistência farmacêutica com atuação conjunta das três esferas federadas (municipal, estadual e federal). Contudo, as informações de transparência, isto é, com dados abertos no âmbito nacional, restringem-se ao Sistema de Informações Ambulatoriais, com foco em terapias de alto custo, não havendo dados abertos individualizados por usuário para a maior parde dos medicamentos adotados na atenção primária à saúde (Ferré et al. 2020; Ferré 2021).

O acesso a medicamentos do Componente Especializado é regulado via Portarias do Ministério da Saúde. Nas respectivas portarias constam o PCDT, o qual enquadra a doença nos diagnósticos segundo a CID10. Trabalhar com dados de mundo real com foco na demanda aferida é essencial para o planejamento do Sistema Único de Saúde. A demanda baseada em prevalência, segundo identificado na literatura, pode não estar atrelada à capacidade do sistema em diagnosticar, disponibilizar e dar acesso ao tratamento em todo o território. Para além das variáveis apresentadas em ensaios clínicos e revisões sistemáticas, a operacionalização do sistema de saúde é essencial para o sucesso do tratamento. Apenas a padronização, isto é, incorporação do tratamento no elenco, não implica na efetiva prevenção ou remissão da saúde da população devido à relação comercial do gestor com o Complexo Econômico-Industrial da Saúde, incluindo a cadeia logística e demais prestadores; educacional com os profissionais de saúde envolvidos na terapêutica e com o compromisso com usuário visando sua adesão (Perini et al. 1999). A cada incorporação, a cadeia assistencial de especialistas e instalações deve avaliada em cada território visando eficiência dos gastos públicos (Meiners et al. 2017).

Os dados de mundo real para análise de dispensação de medicamentos regulada por PCDT são disponibilizados pela Secretaria de Atenção Especializada à Saúde - SAES, a qual dissemina mensalmente dados abertos com apoio do Departamento de Informática do SUS pelo diretório ftp://ftp.datasus.gov.br/dissemin/publicos/.

A Sala Aberta de Situação em Saúde é uma plataforma livre que viabiliza a gestão do conhecimento, análise e visualização de dados do SUS. A presente versão tabula dados de dispensação de medicamentos regulados por Protocolos Clínicos e Diretrizes Terapêuticas (PCDT) e classificados segundo a classificação mantida pelo SIGTAP - Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS. No presente relato são apresentados os processos para extração, transformação e carga dos dados que finalmente compõe o respositório para Avaliação de Tecnologias de Saúde (ATS).

O presente relato foi editado em R Markdown e está disponível em https://rpubs.com/labxss/Produto_2022_2.

2.1 Objetivos

Avaliar o percurso terapêutico dos usuários do SUS de medicamentos antipsicóticos com Protocolo Clínico e Diretriz Terapêutica apresentados na Sala Aberta Aberta de Inteligência em Saúde - Sabeis

2.1.1 Objetivos específicos

  • Descrever a extração dos arquivos DBC disponibilizados via ftp://ftp.datasus.gov.br/dissemin/publicos/.
  • Descrever a carga no Sistema Gerenciador de Banco de Dados (SGBD) PostgreSQL.
  • Descrever a transformação para recomposição da quantidade aprovada e número do usuário do SUS.
  • Descrever a exportação em dados tabulados CSV para uso como dado aberto sob a licença Open Database License (ODbL).
  • Apresentar o código-fonte disponível sob as licenças General Public License - GPL 3.014.
  • Apresentar métricas de avaliação e avaliar os resultados do processo de formação do respositório.
  • Extrair dados de dispensação dos medicamentos elencados.
  • Realizar estudo de utilização de medicamentos comparativo em relação aos demais medicamentos padronizados para os respectivos Protocolos Clínicos e Diretrizes Terapêuticas.

3 Método

3.1 Recursos de software e hardware

As agregações foram realizadas com Linguagem de Consulta Estruturada, (do inglês, Structured Query Language - SQL) sob os paradigmas da modelagem de processamento analítico online, do inglês, Online Analytical Processing (OLAP) e normas de nomenclatura do Departamento de Informática do SUS (DATASUS).

Os dados foram transpostos em tabelas de dimensão e fatos de modo que cada agregação constitui a soma simples da frequência e valores em Reais relativos a eventos discretos (procedimento, diagnóstico, território, características sociodemográficas). A performance de consultas foi otimizada com o recurso da desnormalização das tabelas fato, de modo a reduzir a demanda por cruzamentos com tabelas de dimensão.

A Extração, Transformação e Carga (extract, transform and load - ETL) de dados de dispensação de medicamentos de usuários com medicamentos biológicos em linguagem estruturada de consulta (structured query language - SQL) utilizando-se o Sistema Gerenciador de Banco de Dados (SGBD) PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1). Na wiki é disponibilizado o endereço para baixar o código-fonte e repositório de dados sob a licença General Public License - GPL 3.014 e Open Database License (ODbL).

Os dados foram processados em computador LG® Gram 7,6 GiB RAM, Intel® Core™ i7-7500U CPU @ 2.70GHz × 4, 256,1 GB SSD, Ubuntu 20.04.3 LTS e servidor em nuvem linux Ubuntu 20.04.1 LTS (Focal Fossa) 5.4.0-109-generic x86_64 Quad Core 2300 MHz Common KVM 64 bits L2 cache 16.0 MiB 150.00 GB SSS 12Gb ram.

3.2 Fonte de dados - repositório de dados abertos do DataSUS

Em 2022, as duas principais estratégias de disseminação de dados abertos realizadas pelo Ministério da Saúde são:

  • TabWin/TabNet: Arquivos de dados DBC do tipo DBF (database file) compactado, disponíveis em diretório FTP (protocolo de transferência de arquivos), com arquivos de descrição DEF do tipo texto (metadados) e domínios em CNV do tipo texto.
  • OpenDataSUS: API (Interface de Programação de Aplicações) e arquivos de dados em formato CSV, do tipo texto e dicionário de dados em PDF com ou sem metatados e domínios.

A única fonte federalizada de dados abertos com dispensação de medicamentos individualizada por usuário do SUS é publicizada desde 1994 via estratégia TabWin/TabNet.

A tabela 3.1 informa os endereços onde são disponibilizados os arquivos DBC segundo sistema de informação. Os links FTP não são mais acessíveis em navegadores, devendo ser utilizadas ferramentas específicas para listar os arquivos dos diretórios.

Table 3.1: Diretórios onde são disponibilizados os arquivos DBC pelo DataSUS.
sistema nome gestor url
SIA SIA - Sistema de Informações Ambulatoriais do SUS de 1994 a 2007 SAES/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/199407_200712/Dados/
SIA SIA - Sistema de Informações Ambulatoriais do SUS a partir de 2008 SAES/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/
SIH SIH - Sistema de Informações Hospitalares do SUS de 1992 a 2007 SAES/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIHSUS/199201_200712/Dados/
SIH SIH - Sistema de Informações Hospitalares do SUS a partir de 2008 SAES/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIHSUS/200801_/Dados/
SIM SIM - Sistema de Informações de Mortalidade - Declarações de Óbitos Fetais SVS/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIM/CID10/DOFET/
SIM SIM - Sistema de Informações de Mortalidade SVS/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SIM/CID10/DORES/
SINAN SINAN - Sistema de Informação de Agravos de Notificação SVS/MS ftp://ftp.datasus.gov.br/dissemin/publicos/SINAN/DADOS/FINAIS/
SISAB SISAB - Sistema de Informação em Saúde para a Atenção Básica SAPS/MS ftp://ftp.datasus.gov.br/dissemin/publicos/CMD/DadosSISAB/
PNI SIPNI - Programa Nacional de Imunizações SVS/MS ftp://ftp.datasus.gov.br/dissemin/publicos/PNI/DADOS/

A tabela 3.2 mostra exemplos de arquivos DBC dos diversos sistemas de informação disponíveis pela estratégia TabWin/TabNet.

Table 3.2: Amostra de arquivos DBC disponíveis no repositório do DataSUS
no dbc dt dbc sg sistema sg subsistema qt registros dbc local qt bytes dbc
EFAM1112.dbc 14-06-04 CNES EF 2 1.142
RJMS0710.dbc 13-10-31 SIH RJ 601 33.413
CRES0803.dbc 13-12-26 CIH CR 2.595 56.872
PSPR1411.dbc 16-01-07 SIA PS 42.341 729.055
EFAL0906.dbc 14-06-04 CNES EF 9 1.379
HBRJ1108.dbc 14-06-04 CNES HB 1.185 32.800
STAM1006.dbc 14-06-05 CNES ST 1.559 75.023
ERSP1512.dbc 16-10-04 SIH ER 10.847 244.385
BIPE1106.dbc 12-09-11 SIA BI 235.394 6.091.293
PATO9907.dbc 13-10-24 SIA PA 16.713 218.942
SADMG1312.dbc 14-09-29 SIA SAD 4.296 59.228
EEPB0903.dbc 14-06-04 CNES EE 2 1.179
PAPE0608.dbc 13-10-24 SIA PA 314.322 3.038.822
BIRS1101.dbc 12-09-11 SIA BI 269.929 7.330.742
AMAC1801.dbc 19-09-16 SIA AM 3.397 168.495
CIHAAL1105.dbc 21-11-08 CIHA CIHA 5.145 84.083
ARMA1909.dbc 20-12-10 SIA AR 201 13.725
RDCE1001.dbc 13-11-01 SIH RD 39.898 2.451.380
RCRN1811.dbc 18-12-14 CNES RC 264 11.802
LTPI0804.dbc 14-06-04 CNES LT 983 12.987
PNBA1212.dbc 14-05-22 SISPRENATAL PN 5.203 215.503
CRCE0906.dbc 17-07-06 CIH CR 1.740 37.717
CIHADF1911.dbc 22-04-20 CIHA CIHA 456 7.354
PNTO1203.dbc 14-05-22 SISPRENATAL PN 275 12.664
SADDF1501.dbc 16-09-12 SIA SAD 231 4.962
ADGO1607.dbc 17-10-09 SIA AD 5.613 300.732
ANRR1209.dbc 13-09-12 SIA AN 133 7.056
STMS1211.dbc 16-08-09 CNES ST 3.705 177.997
GMPI1805.dbc 18-06-15 CNES GM 74 3.367
PSRO2010.dbc 21-11-05 SIA PS 4.496 122.755

O dataSUS disponibilizou via ftp, em abril/2022, 13.362.072.156 registros em 143.960 arquivos DBC com dados de 01/1992 à 03/2022. A tabela 3.3 resume o conteúdo disponível.

Table 3.3: Sumário dos arquivos DBC segundo o sistema de informação do SUS
sistema subsistema arquivos bytes registros mes inicial mes final
CIH CR 868 165.114.178 7.676.888 01/2008 04/2011
CIHA CIHA 3.437 2.656.597.213 188.116.504 01/2011 03/2022
CMD CT PR 1.509 35.180.663.302 2.420.091.846 01/2017 04/2019
CNES DC EE EF EFUFAA EP EQ GM GMUFAA HB HBUFAA IN INUFAA LT PF RC RCUFAA SR ST 63.841 35.771.950.076 932.550.889 08/2005 03/2022
SIA AB ABO ACF AD AM AMP AN AQ AR ATD BI PA PS SAD 44.602 208.021.048.005 6.865.728.108 07/1994 03/2022
SIH CH CM ER RD RJ SP 26.383 63.236.637.236 2.770.559.298 01/1992 03/2022
SIM DO DOE DOF DOI DOM DORE 785 2.493.271.907 36.353.134 12/1996 12/2020
SINAN ACBI ACGR ANIM ANTR BOTU CANC CHAG CHIK COLE COQU DENG DERM DIFT ESQU FAMA FMAC FTIF HANS HANT IEXO LEIV LEPT LERD LTAN MALA MENI MENT PAIR PEST PFAN PNEU RAIV TETA TETN TUBE VIOL ZIKA 804 1.681.733.836 38.435.335 12/2000 12/2020
SINASC DN DNR 787 3.679.537.862 96.968.941 12/1994 12/2020
SISPRENATAL PN 944 232.410.320 5.591.213 01/2012 12/2014

Os dados são tripartite. Inicialmente são alimentados pelas Secretarias Municipais de Saúde e encaminhados às Secretarias Estaduais de Saúde as quais enviam ao Ministério da Saúde para consolidação no Sistema de Informações Ambulatoriais (SIA), sumarizados na tabela 3.4.

Table 3.4: Sumário dos arquivos DBC do sistema de informação do Sistema de Informações Ambulatoriais (SIA)
subsistema arquivos bytes registros mes inicial mes final
total 43.561 207.972.599.737 6.865.728.108 07/1994 03/2022
PA 9.139 113.376.861.663 4.606.248.786 07/1994 03/2022
BI 4.617 74.445.356.551 1.818.430.965 01/2008 03/2022
AM 4.571 11.288.441.988 237.557.759 01/2008 03/2022
PS 2.991 1.786.151.210 95.153.771 11/2012 03/2022
AD 4.588 2.225.690.893 42.624.440 01/2008 02/2022
AQ 4.582 3.377.763.465 42.112.653 01/2008 03/2022
ATD 2.453 627.783.911 9.707.800 08/2014 02/2022
AN 2.145 458.824.790 6.534.272 01/2008 10/2014
SAD 1.088 53.440.628 3.524.141 04/2012 10/2018
AR 4.159 295.985.455 3.353.191 01/2008 03/2022
ACF 2.357 19.948.428 249.500 08/2014 02/2022
AB 544 14.622.114 211.252 01/2008 04/2017
AMP 327 1.728.641 19.578 03/2016 02/2022
Note:
AB Laudo de Acompanhamento à Cirurgia Bariátrica, ABO Acompanhamento Pós Cirurgia Bariátrica, ACF Laudo de Confecção de Fístula, AD Laudos Diversos, AM Laudo de Medicamentos, AMP Laudo de Acompanhamento Multiprofissional, AN Laudo de Nefrologia, AQ Laudo de Quimioterapia, AR Laudo de Radioterapia, ATD Laudo de Tratamento Dialítico, BI Boletim Individual, PA Produção Ambulatorial, PS Psicossocial, SAD Atenção Domiciliar

O volume de dados passíveis de serem processados é sumarizado na tabela 3.5, sendo consideradas as seguintes características:

  • Arquivos disponíveis a partir de 01/1992, visando contemplar ao menos cinco anos completos;
  • Subsistemas PA, AM, AQ, AN, AR, BI e PS, devido à presença do Cartão Nacional de Saúde cripitografado.
Table 3.5: Sumário dos arquivos DBC do sistema de informação do Sistema de Informações Ambulatoriais (SIA) processados na Sala Aberta de Inteligência em Saúde - SABEIS por estado.
uf arquivos bytes registros meses mes inicial mes final gigabytes
AC 377 205.553.173 6.478.791 70 06/2016 03/2022 0.2 GB
AL 420 1.839.359.061 48.271.640 70 06/2016 03/2022 1.7 GB
AM 417 2.157.179.445 61.734.541 70 06/2016 03/2022 2.0 GB
AP 339 118.641.895 3.789.418 70 06/2016 03/2022 0.1 GB
BA 419 6.155.757.700 161.901.005 70 06/2016 03/2022 5.7 GB
CE 420 3.087.019.196 86.201.792 70 06/2016 03/2022 2.9 GB
DF 419 1.055.750.055 31.188.737 70 06/2016 03/2022 1.0 GB
ES 420 1.995.318.802 58.123.033 70 06/2016 03/2022 1.9 GB
GO 420 2.552.057.566 69.081.054 70 06/2016 03/2022 2.4 GB
MA 419 2.180.025.193 61.616.961 70 06/2016 03/2022 2.0 GB
MG 420 17.216.668.583 476.387.788 70 06/2016 03/2022 16.0 GB
MS 420 2.251.123.019 65.906.980 70 06/2016 03/2022 2.1 GB
MT 420 1.976.598.262 54.270.701 70 06/2016 03/2022 1.8 GB
PA 418 3.148.247.017 85.966.469 70 06/2016 03/2022 2.9 GB
PB 419 2.275.065.857 62.583.111 70 06/2016 03/2022 2.1 GB
PE 420 3.698.916.206 100.474.180 70 06/2016 03/2022 3.4 GB
PI 420 997.908.431 25.904.579 70 06/2016 03/2022 0.9 GB
PR 420 8.508.761.571 232.243.916 70 06/2016 03/2022 7.9 GB
RJ 419 14.057.994.669 428.502.701 70 06/2016 03/2022 13.1 GB
RN 419 1.638.739.698 42.672.101 70 06/2016 03/2022 1.5 GB
RO 416 775.478.339 20.458.964 70 06/2016 03/2022 0.7 GB
RR 348 122.915.807 3.486.551 70 06/2016 03/2022 0.1 GB
RS 418 12.295.315.664 327.088.892 70 06/2016 03/2022 11.5 GB
SC 420 6.642.121.751 174.814.798 70 06/2016 03/2022 6.2 GB
SE 406 2.399.381.033 69.287.603 70 06/2016 03/2022 2.2 GB
SP 527 28.617.584.552 841.230.224 70 06/2016 03/2022 26.7 GB
TO 393 552.725.796 15.303.871 70 06/2016 03/2022 0.5 GB
total 11.193 128.522.208.341 3.614.970.401 70 06/2016 03/2022 119.7 GB
Note:
Foram considerados: AM Laudo de Medicamentos, AN Laudo de Nefrologia, AQ Laudo de Quimioterapia, AR Laudo de Radioterapia, BI Boletim Individual, PA Produção Ambulatorial e PS Psicossocial.

Os 7.383 procedimentos do SUS, dentre ativos e inativos, são mantidos, desde 2008, via Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS (SIGTAP) coordenado pela Secretaria de Atenção Especializada à Saúde (SAES/MS) identificados por 12.934 códigos. Veja mais informações sobre o SIGTAP em https://wiki.saude.gov.br/sigtap/index.php/P%C3%A1gina_principal. A tabela 3.6 sumariza o quantitativo por grupo.

Table 3.6: Quantidade de procedimentos por grupo mantidos via Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS (SIGTAP)
grupo códigos procedimentos
1 Ações de promoção e prevenção em saúde 91 70
2 Procedimentos com finalidade diagnóstica 1.039 847
3 Procedimentos clínicos 753 581
4 Procedimentos cirúrgicos 1.681 1.191
5 Transplantes de orgãos, tecidos e células 135 105
6 Medicamentos 367 357
7 Órteses, próteses e materiais especiais 529 426
8 Ações complementares da atenção à saúde 46 25

Os códigos do grupo medicamentos iniciam com o dígito 6. A tabela 3.7 mostra o elenco considerado na presente avaliação. Observe que o mesmo medicamento pode apresentar historicamente mais de um código SIGTAP ou forma de organização.

# sumario dos dados abertos do DATASUS
query=paste0(" select case 
         when co_procedimento = 601110013 then 'CLOZ100'
         when co_procedimento in (601110021,604230079) then 'CLOZ025'
         when co_procedimento in (601110030,604230028) then 'OLAN10'
         when co_procedimento in (601110048,604230010) then 'OLAN05'
         when co_procedimento in (601110056,604230044) then 'QUET100'
         when co_procedimento in (601110064,604230036) then 'QUET025'
         when co_procedimento in (601110072,604230052) then 'QUET200'
         when co_procedimento in (604230060,604230052) then 'QUET300'
         when co_procedimento = 601110080 then 'RISP1'
         when co_procedimento = 601110099 then 'RISP2'
         when co_procedimento in (601110102,604210027) then 'ZIPR80'
         when co_procedimento in (601110110,604210019) then 'ZIPR40'
         when co_procedimento = 604510012 then 'RISP1'
         when co_procedimento = 604510020 then 'RISP2'
         when co_procedimento = 604510039 then 'RISP3'
         when co_procedimento = 604510047 then 'RISPSO1'
         when co_procedimento = 604500050 then 'LAMO100'
         when co_procedimento = 604500033 then 'LAMO025'
         when co_procedimento = 604500041 then 'LAMO050'
         when co_procedimento in (601110013, 604230087) then 'CLOZ100'
         else Sg_procedimento END sigla,
        string_agg(distinct trim(no_procedimento), '; ') procedimento,
        string_agg(distinct trim(no_forma_organizacao), '; ') forma,
        string_agg(distinct trim(co_procedimento::text), '; ') sigtap,
        min(nu_competencia) nu_competencia_min,
        max(nu_competencia) nu_competencia_max
   from bd_geral.tf_sigtap 
  where co_forma_organizacao in (60111, 60451)
  or co_procedimento in (SELECT distinct co_procedimento FROM bd_sabeis.tf_psi)
 group by 1
 order by 3,1")
  
# formata decimal, milhar e data
kbpsi=pg_roda_query(query)

kbpsi$nu_competencia_min=formata_mes(kbpsi$nu_competencia_min)
kbpsi$nu_competencia_max=formata_mes(kbpsi$nu_competencia_max)

colnames(kbpsi)=set_colnames(colnames(kbpsi))

x1=kbpsi$forma

kbpsi$forma=NULL

kbl(kbpsi, 
    caption = "Medicamentos e classificação informada via Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS (SIGTAP)") %>%
    kable_classic()   %>% 
  pack_rows(index = table(x1))
Table 3.7: Medicamentos e classificação informada via Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS (SIGTAP)
sigla procedimento sigtap nu competencia min nu competencia max
Antipsicóticos; Derivados do indol
ZIPR40 ZIPRASIDONA (G) 40 MG (POR CAPSULA); ZIPRASIDONA 40 MG (POR CAPSULA) 601110110; 604210019 01/2008 04/2022
ZIPR80 ZIPRASIDONA (G) 80 MG (POR CAPSULA); ZIPRASIDONA 80 MG (POR CAPSULA) 601110102; 604210027 01/2008 04/2022
Antipsicóticos; Diazepinas; oxazepinas e tiazepinas
CLOZ025 CLOZAPINA 25 MG (POR COMPRIMIDO) 601110021; 604230079 01/2008 04/2022
CLOZ100 CLOZAPINA 100 MG (POR COMPRIMIDO) 601110013; 604230087 01/2008 04/2022
OLAN05 OLANZAPINA (G) 5 MG (POR COMPRIMIDO); OLANZAPINA 5 MG (POR COMPRIMIDO) 601110048; 604230010 01/2008 04/2022
OLAN10 OLANZAPINA (G) 10 MG (POR COMPRIMIDO); OLANZAPINA 10 MG (POR COMPRIMIDO) 601110030; 604230028 01/2008 04/2022
QUET025 QUETIAPINA (G) 25 MG (POR COMPRIMIDO); QUETIAPINA 25 MG (POR COMPRIMIDO) 601110064; 604230036 01/2008 04/2022
QUET100 QUETIAPINA (G) 100 MG (POR COMPRIMIDO); QUETIAPINA 100 MG (POR COMPRIMIDO) 601110056; 604230044 01/2008 04/2022
QUET200 QUETIAPINA (G) 200 MG (POR COMPRIMIDO); QUETIAPINA 200 MG (POR COMPRIMIDO) 601110072; 604230052 01/2008 04/2022
Antipsicóticos; Outros antipsicóticos
RISP1 RISPERIDONA 1 MG (POR COMPRIMIDO) 601110080; 604510012 01/2008 04/2022
RISP2 RISPERIDONA 2 MG (POR COMPRIMIDO) 601110099; 604510020 01/2008 04/2022
Diazepinas; oxazepinas e tiazepinas
QUET300 QUETIAPINA 300 MG (POR COMPRIMIDO) 604230060 03/2010 04/2022
Outros antiepilépticos; Outros antiepiléticos
LAMO025 LAMOTRIGINA 25 MG (POR COMPRIMIDO) 604500033 03/2010 04/2022
LAMO050 LAMOTRIGINA 50 MG (POR COMPRIMIDO) 604500041 03/2010 04/2022
LAMO100 LAMOTRIGINA 100 MG (POR COMPRIMIDO) 604500050 03/2010 04/2022
Outros antipsicóticos
RISP3 RISPERIDONA 3 MG (POR COMPRIMIDO) 604510039 03/2010 04/2022
RISPSO1 RISPERIDONA 1,0 MG/ML SOLUÇÃO ORAL (POR FRASCO DE 30 ML) 604510047 05/2016 04/2022

A formação do repositório de dispensações de medicamentos requer o processamento completo do SIA no período, uma vez que as informações estão dispersas em subsistemas distintos, sobretudo nas tabelas do tipo AM e na do tipo PA.

Nas tabelas do tipo AM encontram-se os códigos do usuário do SUS, no formato criptografado do Cartão Nacional de Saúde, porém sem a quantidade aprovada de unidades farmacoténicas. Nas tabelas do tipo PA encontram-se as quantidades aprovadas de unidades farmacotécnicas, entretando, sem o código de identificação do usuário do SUS. Ambas devem ser correlacionadas segundo o

  • número da autorização da guia APAC - Autorização para Procedimentos de Alto Custo/Complexidade,
  • código da gestão (usualmente da Secretaria Estadual de Saúde) ou do estabelecimento e
  • mês de competência, considerando que uma APAC pode ter validade de três meses.

Adicionalmente, existem outras tabelas como a BI, as quais contém o CNS criptografado que podem enriquecer os arquivos PA.

A seguir, são descritas as etapas para extrair o arquivo DBC, recuperar o CNS criptografado nos arquivos PA e organizar os dados em repositório estruturado utilizando um Sistema Gerenciador de Banco de Dados (SGBD).

3.3 Processamento

A extração, transformação e carga (do inglês, Extract, Transform, Load - ETL) é realizada via algoritmos em linguagem GNU bash, versão 4.4.20 com carga no Sistema de Gerenciamento de Banco de Dados (SGBD) PostgreSQL 10.8 a partir dos dados, em formato DBC, disseminados pelo Departamento de Informática do SUS, Secretaria Executiva, Ministério da Saúde - DATASUS/SE/MS, disponível em ftp://ftp.datasus.gov.br.

3.3.1 incorporar a planilha mantida no google docs com valores de medicamentos

O script a seguir incorpora no PostgreSQL a planilha manualmente mantida pela equipe contendo valores obtidos do banco de preços de medicamentos.

url="https://docs.google.com/spreadsheets/d/1xw50Vlsdgko3T7JBG9Qm0Lc1X2PHNAQ4/edit?usp=sharing&ouid=109427081976027472278&rtpof=true&sd=true"

library(gsheet)
ds=as.data.frame(gsheet2tbl('$url', sheetid = 1))
library(RPostgreSQL)
con = DBI::dbConnect(
  dbDriver(drvName = 'PostgreSQL'),
  dbname = '$dbase',
  host = '$hs',
  port =$pt,
  user = '$us',
  password = '$pw'
)
dbWriteTable(con, c('bd_sabeis', 'tm_medicamento_preco'), ds, overwrite = TRUE)

A tabela 3.8 mostra como são coletados os valores de preço cujo código do banco de preços não é a SIGTAP, mas o CATMAT (Catálogo de Materiais do Ministério da Economia).

Table 3.8: Valores coletados a partir do banco de preços em saúde.
dt compra qt compra vl unitario
CLOZ100 CLOZAPINA 100 MG (POR COMPRIMIDO) sigtap 601110013 | 604230087 catmat 1A BR0272431
2014-08-11 21863520 1.48
2015-03-09 25919100 1.40
2016-05-18 34986420 1.26
2017-08-03 37144650 1.19
2018-08-17 30375300 1.17
2019-09-11 34695930 1.12
2021-10-13 39153180 1.15
CLOZ25 CLOZAPINA 25 MG (POR COMPRIMIDO) sigtap 601110021 | 604230079 catmat 1A BR0272429
2014-08-11 1042860 0.34
2015-03-09 77028 0.32
2016-05-18 2633520 0.29
2017-08-03 1355640 0.27
2018-08-17 1473450 0.27
2019-09-11 2340510 0.27
2021-10-13 2161620 0.32
OLAN10 OLANZAPINA 10 MG (POR COMPRIMIDO) sigtap 601110030 | 604230028 catmat 1A BR0271621, BR0271620
2014-12-31 31991318 3.05
2015-06-10 31289550 3.00
2016-12-15 30815460 2.85
2018-04-30 20114190 2.53
2018-12-04 1332960 2.53
2019-08-14 19231200 2.50
2020-10-16 22020870 1.89
OLAN5 OLANZAPINA 5 MG (POR COMPRIMIDO) sigtap 601110048 | 604230010 catmat 1A BR0271621, BR0271620
2014-12-31 19149046 1.96
2015-06-10 20642520 1.93
2016-12-15 21847470 1.83
2018-04-30 3649380 1.69
2018-12-04 8505210 1.69
2019-08-14 9577020 1.40
2020-10-16 13581450 0.83
QUET100 QUETIAPINA 100 MG (POR COMPRIMIDO) sigtap 601110056 | 604230044 catmat 1A BR0272832, BR0272833, BR0272831
2014-08-04 18105640 1.60
2015-03-26 19305870 1.52
2016-05-18 21897300 1.36
2017-08-31 23405490 1.29
2018-08-08 24285090 1.28
2019-09-19 33815970 1.06
2020-04-29 40188810 1.06
QUET200 QUETIAPINA 200 MG (POR COMPRIMIDO) sigtap 601110072 | 604230052 catmat 1A BR0272832, BR0272833, BR0272831
2014-08-04 17000424 3.61
2015-03-26 18292980 3.43
2016-05-18 15307860 3.08
2017-08-31 16717380 2.81
2018-08-08 21516180 2.75
2019-09-19 21957840 2.00
2020-04-29 27106890 1.99
QUET25 QUETIAPINA 25 MG (POR COMPRIMIDO) sigtap 601110064 | 604230036 catmat 1A BR0272832, BR0272833, BR0272831
2014-08-04 13819624 0.68
2015-03-26 14779380 0.64
2016-05-18 18140700 0.58
2017-08-31 14515080 0.51
2018-08-08 19864770 0.48
2019-09-19 17951520 0.46
2020-04-29 19343490 0.44
QUET300 QUETIAPINA 300 MG (POR COMPRIMIDO) sigtap 604230060 catmat 1A BR0449243
2018-12-11 659911 0.90
2018-12-11 1980934 0.75
2019-10-10 3537060 0.75
2020-09-14 4085726 0.70
2018-08-29 25431720 1.10

As datas, valores e quantidades são transformadas ainda na tabela provisória bd_sabeis.tm_medicamento_preco A tabela definitiva bd_sabeis.tf_medicamento_preco é criada em seguida.

-- SQL
delete 
  from bd_sabeis.tm_medicamento_preco
 where dt_compra is null or
       vl_unitario is null or
       qt_compra is null ;

UPDATE bd_sabeis.tm_medicamento_preco
         SET dt_compra = TO_DATE(dt_compra, 'dd/mm/yyyy'),
             vl_unitario = replace(replace(replace(vl_unitario ,'.',''),',','.'),'R$ ',''),
             qt_compra = replace(replace(qt_compra ,'.',''),',','.');

drop table if exists bd_sabeis.tf_medicamento_preco cascade;
create table bd_sabeis.tf_medicamento_preco as  
select *, DENSE_RANK () OVER (PARTITION BY sg_procedimento4 ORDER BY dt_compra) co_seq_compra
, DENSE_RANK () OVER (PARTITION BY sg_procedimento4 ORDER BY dt_compra DESC) co_seq_compra_desc
    from (
 select
       sg_procedimento4,
       dt_compra,
       co_procedimento3,
       sum(qt_compra::int) as qt_compra,
       round(sum(qt_compra::int*vl_unitario::numeric)::decimal/sum(qt_compra::int),2) vl_unitario
  FROM bd_sabeis.tm_medicamento_preco
 where vl_unitario::decimal > 0
 group by 1,2,3
) x order by 1,2;

alter table bd_sabeis.tf_medicamento_preco
  add st_ceaf varchar(10) default null;
 
 update bd_sabeis.tf_medicamento_preco A
    set st_ceaf = \"CEAF2020\"
  from bd_sabeis.tm_medicamento_preco B
 where A.sg_procedimento4 = B.sg_procedimento4;

 CREATE INDEX tf_medicamento_preco_sg_procedimento4_idx ON bd_sabeis.tf_medicamento_preco (sg_procedimento4);

A tabela bd_sabeis.vw_medicamento_preco_data estabelece os intervalos de data para cada compra, os quais serão utilizados para inserir o valor de cada dispensação segundo o mês de competência.

-- SQL
-- Valores por data
drop materialized view if exists bd_sabeis.vw_medicamento_preco_data;
 create materialized view bd_sabeis.vw_medicamento_preco_data as
 select A.sg_procedimento4 as sg_procedimento,
        ('''' || replace(A.co_procedimento3, ' | ', ''' ''') || '''')::tsvector as co_procedimento,
        A.dt_compra::DATE dt_compra_inicio,
        B.dt_compra::DATE-1 as dt_compra_fim,
        A.vl_unitario 
   from bd_sabeis.tf_medicamento_preco A,
        bd_sabeis.tf_medicamento_preco B
   where A.sg_procedimento4 = B.sg_procedimento4 
     and A.co_seq_compra = B.co_seq_compra -1
     
 UNION
     select sg_procedimento4 ,
            ('''' || replace(co_procedimento3, ' | ', ''' ''') || '''')::tsvector,
            dt_compra::DATE dt_compra_inicio,
            current_date as dt_compra_fim,
            vl_unitario 
       from ( 
     select 
      DENSE_RANK () OVER ( 
        PARTITION BY sg_procedimento4 
    ORDER BY dt_compra DESC 
     ) n, *
   from bd_sabeis.tf_medicamento_preco A
   ) x
   where n = 1
   order by 1,2;
  CREATE INDEX vw_medicamento_preco_data_sg_procedimento4_idx ON bd_sabeis.vw_medicamento_preco_data (sg_procedimento);

A tabela @ref(tab:vw_medicamento_preco_data) mostra o resultado do script SQL acima.

(#tab:vw_medicamento_preco_data)Formato final da tabela de valores coletados a partir do banco de preços em saúde com intervalos de data.
sg procedimento co procedimento dt compra inicio dt compra fim vl unitario
CLOZ100 ‘601110013’ ‘604230087’ 2014-08-11 2015-03-08 1.48
CLOZ100 ‘601110013’ ‘604230087’ 2015-03-09 2016-05-17 1.40
CLOZ100 ‘601110013’ ‘604230087’ 2016-05-18 2017-08-02 1.26
CLOZ100 ‘601110013’ ‘604230087’ 2017-08-03 2018-08-16 1.19
CLOZ100 ‘601110013’ ‘604230087’ 2018-08-17 2019-09-10 1.17
CLOZ100 ‘601110013’ ‘604230087’ 2019-09-11 2021-10-12 1.12
CLOZ100 ‘601110013’ ‘604230087’ 2021-10-13 2022-05-04 1.15
LAMO100 ‘601070038’ ‘604500050’ 2018-08-29 2022-05-04 1.10
OLAN10 ‘601110030’ ‘604230028’ 2014-12-31 2015-06-09 3.05
OLAN10 ‘601110030’ ‘604230028’ 2015-06-10 2016-12-14 3.00
OLAN10 ‘601110030’ ‘604230028’ 2016-12-15 2018-04-29 2.85
OLAN10 ‘601110030’ ‘604230028’ 2018-04-30 2018-12-03 2.53
OLAN10 ‘601110030’ ‘604230028’ 2018-12-04 2019-08-13 2.53
OLAN10 ‘601110030’ ‘604230028’ 2019-08-14 2020-10-15 2.50
OLAN10 ‘601110030’ ‘604230028’ 2020-10-16 2022-05-04 1.89
QUET100 ‘601110056’ ‘604230044’ 2014-08-04 2015-03-25 1.60
QUET100 ‘601110056’ ‘604230044’ 2015-03-26 2016-05-17 1.52
QUET100 ‘601110056’ ‘604230044’ 2016-05-18 2017-08-30 1.36
QUET100 ‘601110056’ ‘604230044’ 2017-08-31 2018-08-07 1.29
QUET100 ‘601110056’ ‘604230044’ 2018-08-08 2019-09-18 1.28
QUET100 ‘601110056’ ‘604230044’ 2019-09-19 2020-04-28 1.06
QUET100 ‘601110056’ ‘604230044’ 2020-04-29 2022-05-04 1.06
QUET200 ‘601110072’ ‘604230052’ 2014-08-04 2015-03-25 3.61
QUET200 ‘601110072’ ‘604230052’ 2015-03-26 2016-05-17 3.43
QUET200 ‘601110072’ ‘604230052’ 2016-05-18 2017-08-30 3.08
QUET200 ‘601110072’ ‘604230052’ 2017-08-31 2018-08-07 2.81
QUET200 ‘601110072’ ‘604230052’ 2018-08-08 2019-09-18 2.75
QUET200 ‘601110072’ ‘604230052’ 2019-09-19 2020-04-28 2.00
QUET200 ‘601110072’ ‘604230052’ 2020-04-29 2022-05-04 1.99
QUET300 ‘604230060’ 2018-12-11 2019-10-09 0.79
QUET300 ‘604230060’ 2019-10-10 2020-09-13 0.75
QUET300 ‘604230060’ 2020-09-14 2022-05-04 0.70

3.3.2 incorporar a planilha mantida no google docs com quantidade aprovada segundo o TabNet

O principal controle de qualidade da extração é a correspondência do total da quantidade aprovada registrada no arquivo DBC segundo UF, mês de competência e ano.

A coleta do arquivo csv gerado pelo tabnet é realizada com os seguintes parâmetros a partir do tabulador http://tabnet.datasus.gov.br/cgi/tabcgi.exe?sia/cnv/qauf.def.

  • ano e mes de processamento
  • por UF
  • quantidade aprovada
  • grupo de procedimento 06

A planilha contém o resultado da extração do sítio do TabNet por UF e mês. O código-fonte para a verificação junto ao tabnet é mostrado a seguir.

url="https://docs.google.com/spreadsheets/d/1lnZsswLNEe1_2YbV95MZLM8ECl_seP-f9xpgBptcxZI/edit?usp=sharing"
library(gsheet);ds=as.data.frame(gsheet2tbl('$url'))

library(RPostgreSQL)
con = DBI::dbConnect(
  dbDriver(drvName = 'PostgreSQL'),
  dbname = '$dbase',
  host = '$hs',
  port =$pt,
  user = '$us',
  password = '$pw'
)
dbWriteTable(con,
             c('bd_sabeis', 'tm_tabnet_medicamento_uf_mes'),
             ds,
             overwrite = TRUE)

Após a incorporação os meses são sustituídos pelo respectivo número com uma consulta SQL.

update bd_sabeis.tm_tabnet_medicamento_uf_mes 
    set nu_competencia = 
    case 
    when nu_competencia like '%Janeiro%' then right(nu_competencia,4) || '01' 
    when nu_competencia like '%Fevereiro%' then right(nu_competencia,4) || '02' 
    when nu_competencia like '%Março%' then right(nu_competencia,4) || '03' 
    when nu_competencia like '%Abril%' then right(nu_competencia,4) || '04' 
    when nu_competencia like '%Maio%' then right(nu_competencia,4) || '05' 
    when nu_competencia like '%Junho%' then right(nu_competencia,4) || '06' 
    when nu_competencia like '%Julho%' then right(nu_competencia,4) || '07' 
    when nu_competencia like '%Agosto%' then right(nu_competencia,4) || '08' 
    when nu_competencia like '%Setembro%' then right(nu_competencia,4) || '09' 
    when nu_competencia like '%Outubro%' then right(nu_competencia,4) || '10' 
    when nu_competencia like '%Novembro%' then right(nu_competencia,4) || '11' 
    when nu_competencia like '%Dezembro%' then right(nu_competencia,4) || '12'
    end;

delete from bd_sabeis.tm_tabnet_medicamento_uf_mes where nu_competencia is null;

Após, cada coluna relativa à UF é transposta em linhas na nova tabela bd_sabeis.tf_medicamento_qualidade_uf_mes.

drop table if exists bd_sabeis.tf_medicamento_qualidade_uf_mes;
create table bd_sabeis.tf_medicamento_qualidade_uf_mes as
select nu_competencia::int nu_competencia, 'AC' sg_uf, \"AC\" 
as qt_aprovada_tabnet from bd_sabeis.tm_tabnet_medicamento_uf_mes 
union
select nu_competencia::int nu_competencia, 'AM', "AM" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'RR', "RR" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'PA', "PA" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'AP', "AP" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'TO', "TO" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'MA', "MA" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'PI', "PI" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'CE', "CE" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'RN', "RN" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'PB', "PB" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'PE', "PE" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'AL', "AL" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'SE', "SE" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'BA', "BA" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'MG', "MG" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'ES', "ES" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'RJ', "RJ" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'SP', "SP" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'PR', "PR" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'SC', "SC" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'RS', "RS" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'MS', "MS" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'MT', "MT" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'GO', "GO" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes union
select nu_competencia::int nu_competencia, 'DF', "DF" as qt_aprovada 
from bd_sabeis.tm_tabnet_medicamento_uf_mes 
order by 1,2;
  
  ALTER TABLE bd_sabeis.tf_medicamento_qualidade_uf_mes ADD qt_aprovada_sabeis int8 default 0;

3.3.3 listar os arquivos dbc do ftp://ftp.datasus.gov.br/dissemin/publicos/

Trata-se da listagem dos arquivos DBC disponíveis no diretório FTP do Datasus via estratégia TabWin de disseminação.

Os comandos a seguir, em linguagem bash, nativa dos sistemas operalcionais Linux, são executados para listagem e tratamento dos arquivos diponíveis utilizando-se o comando curl URL.

#bash 

# elimina arquivo previamente obtido
touch /tmp/listadbc.sia.ftp.p1.csv ; rm /tmp/listadbc.sia.ftp.p1.csv

#lista o FTP
curl ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/ | egrep -i "dbc$" | awk '{print $4","$3","$1","$2}' | awk -F'[-,]' '{print $1","$2","$5"-"$3"-"$4","$6",SIA"}' >> /tmp/listadbc.sia.ftp.p1.csv 

# sistema e uf
cat /tmp/listadbc.sia.ftp.p1.csv | sed 's/a\.dbc/\.dbc/g' | sed 's/b\.dbc/\.dbc/g' | sed 's/c\.dbc/\.dbc/g' | awk -F'.' '{print toupper($1)}' | sed 's/[^A-Z]//g' | awk '{print substr($1,1,length($1)-2)","substr($1,length($1)-1,length($1))}' > /tmp/listadbc.ftp.p2.csv
cat /tmp/listadbc.sia.ftp.p1.csv | sed 's/a\.dbc/\.dbc/g' | sed 's/b\.dbc/\.dbc/g' | sed 's/c\.dbc/\.dbc/g' | awk -F'.' '{print $1}' | sed 's/[^0-9]//g' > /tmp/listadbc.ftp.p3.csv
paste -d, /tmp/listadbc.sia.ftp.p1.csv /tmp/listadbc.ftp.p2.csv /tmp/listadbc.ftp.p3.csv | awk -F',' -v OFS=',' '{ x=$8; if ( length($8) == 2 ) { if ( $8 > 80 ) $8="19"$8; else $8="20"$8}; print $0 }' | awk -F',' -v OFS=',' '{if ( $7 == "XT" ) $7 = "BR"; print $0}' | awk -F',' -v OFS=',' '{if ( $7 == "ET" ) $7 = "BR"; print $0}' | awk -F',' -v OFS=',' '{if ( $7 == "NF" ) $7 = "BR"; print $0}' | awk -F',' -v OFS=',' '{if ( $7 == "AT" ) $7 = "BR"; print $0}' > /tmp/listadbc.ftp.p4.csv

# competencia e ano
cat /tmp/listadbc.ftp.p4.csv | awk -F',' -v OFS=',' '{ if ( $5 == "CIH" || $5 == "CIHA" || $5 == "CNES" || $5 == "SIA" || $5 == "SIH" || $5 == "SISPRENATAL" || $5 == "CMD" ) { if ( substr($8,1,2) > 80 ) $8="19"$8; else $8="20"$8 } else $8=$8"12"; print $0","substr($8,1,4)  }'  > /tmp/listadbc.ftp.csv

##   % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
##                                  Dload  Upload   Total   Spent    Left  Speed
## 
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0
100 1153k    0 1153k    0     0   788k      0 --:--:--  0:00:01 --:--:--  787k
100 2089k    0 2089k    0     0  1025k      0 --:--:--  0:00:02 --:--:-- 1026k
Table 3.9: Exemplo de arquivos do Sistema de Informação Ambulatorial presentes na atual versão da SABEIS.
bytes data hora sistema subsistema UF competência
ARES1511.dbc 44.871 07/11/2016 02:37PM SIA AR ES 11/2015
PAGO2004.dbc 11.570.807 05/07/2021 07:31PM SIA PA GO 04/2020
AMPI2006.dbc 759.390 05/08/2021 12:04AM SIA AM PI 06/2020
ACFMT1705.dbc 4.203 06/07/2018 08:07AM SIA ACF MT 05/2017
PSDF2005.dbc 110.476 05/07/2021 07:33PM SIA PS DF 05/2020
PSES1512.dbc 98.952 07/11/2016 02:49PM SIA PS ES 12/2015
ARSE1806.dbc 15.678 10/01/2020 09:38AM SIA AR SE 06/2018
AQSE2107.dbc 228.876 05/05/2022 01:27PM SIA AQ SE 07/2021
AQPI1410.dbc 239.826 07/06/2019 09:31AM SIA AQ PI 10/2014
ABOPR2201.dbc 18.989 05/05/2022 01:26PM SIA ABO PR 01/2022
PAMA2112.dbc 21.578.947 05/05/2022 01:30PM SIA PA MA 12/2021
AMPRO1904.dbc 2.444 06/07/2020 02:10PM SIA AMP RO 04/2019
ADTO1912.dbc 27.656 05/02/2021 06:07PM SIA AD TO 12/2019
ACFMA1811.dbc 5.660 10/01/2020 09:36AM SIA ACF MA 11/2018
PSRR1610.dbc 11.658 11/12/2017 09:09AM SIA PS RR 10/2016
AMPB2108.dbc 2.116.529 05/05/2022 01:26PM SIA AM PB 08/2021
ABOPR1512.dbc 154.482 07/11/2016 02:31PM SIA ABO PR 12/2015
ACFPR1806.dbc 14.491 10/01/2020 09:36AM SIA ACF PR 06/2018
PSAL1711.dbc 455.551 10/12/2018 04:09PM SIA PS AL 11/2017
PAMS1603.dbc 8.686.493 05/06/2017 10:27AM SIA PA MS 03/2016

Dentre os 143.960 arquivos DBC disponíveis, foram processados 23.196 arquivos na presente versão do repositório SABEIS referentes ao Sistema de Informações Ambulatoriais SIA.

A partir do nome do arquivo DBC são derivados o subsistema, o estado (UF), ano e o mês de competência, conforme o exemplo PARO1506, segmentado em PA, RO, 15 e 06, cujas respectivas partes devem ser interpretadas conforme abaixo em “dados de produção ambulatorial de Rondônia de junho de 2015.

  • Subsistema
    • PA: Produção Ambulatorial a qual corresponde o arquivo do “corpo” da APAC, as demais siglas dos subsistemas são:
    • AB Laudo de Acompanhamento à Cirurgia Bariátrica,
    • ABO Acompanhamento Pós Cirurgia Bariátrica,
    • ACF Laudo de Confecção de Fístula,
    • AD Laudos Diversos,
    • AM Laudo de Medicamentos,
    • AMP Laudo de Acompanhamento Multiprofissional,
    • AN Laudo de Nefrologia,
    • AQ Laudo de Quimioterapia,
    • AR Laudo de Radioterapia,
    • ATD Laudo de Tratamento Dialítico,
    • BI Boletim Individual,
    • PS Psicossocial e
    • SAD Atenção Domiciliar.
  • Estado
    • Acre - AC (12),
    • Alagoas - AL (27),
    • Amazonas - AM (13),
    • Amapá - AP (16),
    • Bahia - BA (29),
    • Ceará - CE (23),
    • Distrito Federal - DF (53),
    • Espírito Santo - ES (32),
    • Goiás - GO (52),
    • Maranhão - MA (21),
    • Minas Gerais - MG (31),
    • Mato Grosso do Sul - MS (50),
    • Mato Grosso - MT (51),
    • Pará - PA (15),
    • Paraíba - PB (25),
    • Pernambuco - PE (26),
    • Piauí - PI (22),
    • Paraná - PR (41),
    • Rio de Janeiro - RJ (33),
    • Rio Grande do Norte - RN (24),
    • Rondônia - RO (11),
    • Roraima - RR (14),
    • Rio Grande do Sul - RS (43),
    • Santa Catarina - SC (42),
    • Sergipe - SE (28),
    • São Paulo - SP (35) e
    • Tocantins - TO (17).
  • Ano com dois dígidos e
  • Mês de competência.

3.3.4 baixar e descompactar os arquivo DCB para DBF e converter para CSV

A função dbc2csv, codificada em liguagem bash, baixa o DBC, descompacta e converte para CSV está disponível no apêndice.

Cada arquivo DBC deve ser baixado a partir do repositório, atualmente com 119.7 GB (11.193 arquivos DBC) do total de 328.9 GB disponível (143.960 arquivos DBC).

Uma vez processado e carregado no banco de dados estruturado, o arquivo DBC pode ser eliminado.

# codigo bash: 
# baixa o arquivos dbc PARO1506.dbc 
# ignorando a caixa (alta ou baixa) --ignore-case 
# e os arquivos baixados anteriormente --no-clobber
wget --ignore-case --no-clobber ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/PARO1506.dbc

3.3.5 descompactar dbc a dbf

O descompactador oficial, isto é, fornecido pelo DataSUS é o dbf2dbc.exe, o qual pode ser baixado em http://siab.datasus.gov.br/DATASUS/index.php?area=060805&item=3, ou diretamente pelo link ftp://ftp.datasus.gov.br/tabwin/tabwin/Tab415.zip.

Ele está contido no arquivo Tab415.zip, na versão 4.15 para Microsoft® Windows®. Infelizmente, em desacordo com os princípios da impessoalidade, eficiência e razoabilidade da administração pública, o datasus não disponibilizou o código-fonte, nem do TabWin, nem do dbf2dbc.exe nem refatoração em software livre do descompactador e compilador de arquivos de definção DEF e domínios CNV, o que viabilizaria maior colaboração da comunidade do SUS para o desenvolvimento de soluções comuns e independentes de plafatormas proprietárias (Lima, Gabriela Vasconcelos and Feitosa, Gustavo Raposo Pereira 2015). Dessa forma, é usado o artifício da aplicação wine, a qual emula o ambiente do MS-Windows.

Entretanto, existem iniciativas, implementadas em R, a saber:

A opção em utilizar a ferramenta original do DataSUS deve-se à velocidade e reprodutibilidade com que os arquivos são processados em linguagem bash, sobretudo no processamento de bilhões de registros em computadores domésticos.

# codigo bash: descompactador DBC para DBF
exe="Tab415/dbf2dbc.exe"
dbc="PABA1903" # ARQUIVO dbc APAC da Bahia, de marco/2019 

# descompacta dbc para dbf
wine $exe $fdbc

# identifica o arquivo dbf
fdbf=$(echo $fdbc  | awk -F'.' '{print "ls "$1"* | grep -i DBF"}' | sh)

3.3.6 contar número de registros do dbf

O reconhecimento da do sucesso da descompctação deve-se à contagem do número de registros do dbf e comparação com os do dbc.

# codigo bash: numero de registros do arquivo dbf
r=$(dbview -i -o $fdbc | grep recs | awk '{print $NF}')

3.3.7 converte a csv

O csv é um formato universal de arquivo tabulado, tornando dinâmico o processamento do arquivo cujo uso em dbf se torna restrito a ferramentas específicas capazes de lidar com o formato.

# codigo bash: converte dbf em csv
dbview -b -d"^" -t $fdbf | iconv -f "CP861" -t "UTF-8" | sed 's/"//g' | sed 's/\\//g' | awk -F'^' -v n=$lcol '{if (NF == n) print $0}'  > /tmp/tmp.$curdt.csv

3.3.8 padronizar o nome dos atributos

A partir da estrutura do arquivo dbf são selecionados os campos, os quais podem apresentar variações do nome ao longo dos diferentes subsistemas do SIA. A tabela 3.5 relaciona os campoo e os diferentes subsistemas onde podem ser apontados atributos correlados.

Table 3.10: Atributos relacionados no SIA e respectivo nome padronizado
atributo origem tipo descrição
co_cid_principal CIHA.CIHA.diag_princ; CIH.CR.diag_princ; SIA.AB.ap_cidpri; SIA.ACF.ap_cidpri; SIA.AD.ap_cidpri; SIA.AM.ap_cidpri; SIA.AMP.ap_cidpri; SIA.AN.ap_cidpri; SIA.AQ.ap_cidpri; SIA.AR.ap_cidpri; SIA.ATD.ap_cidpri; SIA.BI.cidpri; SIA.PA.pa_cidpri; SIA.PS.cidpri; SIA.SAD.cidpri; SIH.RD.diag_princ; SIH.RJ.diag_princ; SIH.SP.sp_cidpri; SIM.DO.causabas; SIM.DOE.causabas; SIM.DOF.causabas; SIM.DOI.causabas; SIM.DOM.causabas varchar(8) CID Principal;CID Principal (APAC ou BPA-I)
co_cid_secundario CIHA.CIHA.diag_secun; CIH.CR.diag_secun; SIA.AB.ap_cidsec; SIA.ACF.ap_cidsec; SIA.AD.ap_cidsec; SIA.AM.ap_cidsec; SIA.AMP.ap_cidsec; SIA.AN.ap_cidsec; SIA.AQ.ap_cidsec; SIA.AR.ap_cidsec; SIA.ATD.ap_cidsec; SIA.PA.pa_cidsec; SIA.PS.cidassoc; SIA.SAD.cidassoc; SIH.RD.cid_asso; SIH.RD.cid_morte; SIH.RD.cid_notif; SIH.RD.diag_sec; SIH.RD.diagsec1; SIH.RD.diagsec2; SIH.RD.diagsec3; SIH.RD.diagsec4; SIH.RD.diagsec5; SIH.RD.diagsec6; SIH.RD.diagsec7; SIH.RD.diagsec8; SIH.RD.diagsec9; SIH.RD.diag_secun; SIH.RJ.cid_asso; SIH.RJ.cid_morte; SIH.RJ.cid_notif; SIH.RJ.diag_secun; SIH.SP.sp_cidsec; SIM.DO.causabas_o; SIM.DO.causamat; SIM.DOE.causabas_o; SIM.DOE.causamat; SIM.DOE.linhaii; SIM.DOF.causabas_o; SIM.DOF.causamat; SIM.DOF.linhaii; SIM.DOI.causabas_o; SIM.DOI.causamat; SIM.DOI.linhaii; SIM.DO.linhaii; SIM.DOM.causabas_o; SIM.DOM.causamat; SIM.DOM.linhaii varchar(30) CID Causas Associadas;CID Secundário;CID Secundário (APAC)
co_ibge_municipio_estabelecimento CIHA.CIHA.munic_mov; CIH.CR.munic_mov; CNES.DC.codufmun; CNES.EE.codufmun; CNES.EF.codufmun; CNES.EP.codufmun; CNES.EQ.codufmun; CNES.GM.codufmun; CNES.HB.codufmun; CNES.IN.codufmun; CNES.LT.codufmun; CNES.PF.codufmun; CNES.RC.codufmun; CNES.SR.codufmun; CNES.ST.codufmun; SIA.AB.ap_ufmun; SIA.ACF.ap_ufmun; SIA.AD.ap_ufmun; SIA.AM.ap_ufmun; SIA.AMP.ap_ufmun; SIA.AN.ap_ufmun; SIA.AQ.ap_ufmun; SIA.AR.ap_ufmun; SIA.ATD.ap_ufmun; SIA.BI.ufmun; SIA.PA.pa_munat; SIA.PA.pa_ufmun; SIA.PS.ufmun; SIA.SAD.ufmun; SIH.RD.munic_mov; SIH.RJ.munic_mov; SIM.DO.codmunocor; SIM.DOE.codmunocor; SIM.DOF.codmunocor; SIM.DOI.codmunocor; SIM.DOM.codmunocor; SINAN.ANIM.id_municip; SINAN.BOTU.id_municip; SINAN.CHAG.id_municip; SINAN.CHIK.id_municip; SINAN.COLE.id_municip; SINAN.COQU.id_municip; SINAN.DENG.id_municip; SINAN.DIFT.id_municip; SINAN.ESQU.id_municip; SINAN.FAMA.id_municip; SINAN.FMAC.id_municip; SINAN.FTIF.id_municip; SINAN.HANS.id_municip; SINAN.HANT.id_municip; SINAN.IEXO.id_municip; SINAN.LEIV.id_municip; SINAN.LEPT.id_municip; SINAN.LTA.id_municip; SINAN.LTAN.id_municip; SINAN.MALA.id_municip; SINAN.MENI.id_municip; SINAN.PEST.id_municip; SINAN.PFA.id_municip; SINAN.RAIV.id_municip; SINAN.TETA.id_municip; SINAN.TETN.id_municip; SINAN.TUBE.id_municip; SINAN.VIOL.id_municip; SINAN.ZIKA.id_municip int4 Unidade da Federação + Município do Estabelecimento;Unidade da Federação + município onde está localizado o Estabelecimento;Unidade Federação do Brasil
co_ibge_municipio_residencia CIHA.CIHA.munic_res; CIH.CR.munic_res; CMD.CT.co_munres; CMD.PR.co_munres; CNES.PF.ufmunres; SIA.AB.ap_munpcn; SIA.ACF.ap_munpcn; SIA.AD.ap_munpcn; SIA.AM.ap_munpcn; SIA.AMP.ap_munpcn; SIA.AN.ap_munpcn; SIA.AQ.ap_munpcn; SIA.AR.ap_munpcn; SIA.ATD.ap_munpcn; SIA.BI.munpac; SIA.PA.pa_munpcn; SIA.PS.munpac; SIA.SAD.munpac; SIH.ER.mun_res; SIH.RD.munic_res; SIH.RJ.munic_res; SIM.DO.codmunres; SIM.DOE.codmunres; SIM.DOF.codmunres; SIM.DOI.codmunres; SIM.DOM.codmunres; SINASC.DN.codmunres int4 ;UF + Município de Residência do paciente;Unidade da Federação + município do município de residência do paciente;Unidade da Federação + município do município de residência do paciente ou do estabelecimento, caso não se tenha à identificação do paciente o que ocorre no (BPA)
co_procedimento CIHA.CIHA.proc_rea; CIH.CR.proc_rea; CMD.CT.co_procede; CMD.PR.co_procede; SIA.AB.ap_pripal; SIA.ACF.ap_pripal; SIA.AD.ap_pripal; SIA.AM.ap_pripal; SIA.AMP.ap_pripal; SIA.AN.ap_pripal; SIA.AQ.ap_pripal; SIA.AR.ap_pripal; SIA.ATD.ap_pripal; SIA.BI.proc_id; SIA.PA.pa_codpro; SIA.PA.pa_proc_id; SIA.PS.pa_proc_id; SIA.SAD.pa_proc_id; SIH.RD.proc_rea; SIH.RJ.proc_rea; SIH.SP.sp_procrea int8 Código de Procedimento Ambulatorial;Procedimento Principal da APAC
dt_nascimento CIHA.CIHA.nasc; CIH.CR.nasc; SIA.BI.dtnasc; SIA.PS.dtnasc; SIA.SAD.dtnasc; SIH.RD.nasc; SIH.RJ.nasc; SIM.DO.dtnasc; SIM.DOE.dtnasc; SIM.DOF.dtnasc; SIM.DOI.dtnasc; SIM.DOM.dtnasc; SINASC.DN.dtnasc; SINASC.DNR.data_nasc int8 Data de nascimento do Paciente
nu_autorizacao SIA.AB.ap_autoriz; SIA.ACF.ap_autoriz; SIA.AD.ap_autoriz; SIA.AM.ap_autoriz; SIA.AMP.ap_autoriz; SIA.AN.ap_autoriz; SIA.AQ.ap_autoriz; SIA.AR.ap_autoriz; SIA.ATD.ap_autoriz; SIA.BI.autoriz; SIA.PA.pa_autoriz; SIA.PA.pa_numapa int8 Número da APAC;Número da APAC ou número de autorização do BPA-I, conforme o caso. No BPA-I não é obrigatório, portanto, não é criticado.
nu_idade CIHA.CIHA.idade; CIH.CR.idade; SIA.AB.ap_nuidade; SIA.ACF.ap_nuidade; SIA.AD.ap_nuidade; SIA.AM.ap_nuidade; SIA.AMP.ap_nuidade; SIA.AN.ap_nuidade; SIA.AQ.ap_nuidade; SIA.AR.ap_nuidade; SIA.ATD.ap_nuidade; SIA.BI.idadepac; SIA.PA.pa_idade; SIA.PS.idadepac; SIA.SAD.idadepac; SIH.RD.idade; SIH.RJ.idade; SIM.DOE.idade; SIM.DOF.idade; SIM.DO.idade; SIM.DOI.idade; SIM.DOM.idade; SINAN.ANIM.nu_idade_n; SINAN.BOTU.nu_idade_n; SINAN.CHAG.nu_idade_n; SINAN.CHIK.nu_idade_n; SINAN.COLE.nu_idade_n; SINAN.COQU.nu_idade_n; SINAN.DENG.nu_idade_n; SINAN.DIFT.nu_idade_n; SINAN.ESQU.nu_idade_n; SINAN.FAMA.nu_idade_n; SINAN.FMAC.nu_idade_n; SINAN.FTIF.nu_idade_n; SINAN.HANS.nu_idade_n; SINAN.HANT.nu_idade_n; SINAN.IEXO.nu_idade_n; SINAN.LEIV.nu_idade_n; SINAN.LEPT.nu_idade_n; SINAN.LTAN.nu_idade_n; SINAN.LTA.nu_idade_n; SINAN.MALA.nu_idade_n; SINAN.MENI.nu_idade_n; SINAN.PEST.nu_idade_n; SINAN.PFA.nu_idade_n; SINAN.RAIV.nu_idade_n; SINAN.TETA.nu_idade_n; SINAN.TETN.nu_idade_n; SINAN.TUBE.nu_idade_n; SINAN.VIOL.nu_idade_n; SINAN.ZIKA.nu_idade_n; SISPRENATAL.PN.nu_idade int8 Idade do Paciente em anos;Numero da Idade
qt_aprovada CIHA.CIHA.dias_perm; CIH.CR.dias_perm; SIA.BI.qt_aprov; SIA.PA.pa_qtdapr; SIA.PS.pa_qtdapr; SIA.SAD.pa_qtdapr; SIH.RD.dias_perm; SIH.RD.qt_diarias; SIH.RJ.dias_perm; SIH.RJ.qt_diarias; SIH.SP.sp_qtd_ato int8 Quantidade Aprovada;Quantidade aprovada do procedimento
sg_sexo CIHA.CIHA.sexo; CIH.CR.sexo; CMD.CT.co_sexo; CMD.PR.co_sexo; SIA.AB.ap_sexo; SIA.ACF.ap_sexo; SIA.AD.ap_sexo; SIA.AM.ap_sexo; SIA.AMP.ap_sexo; SIA.AN.ap_sexo; SIA.AQ.ap_sexo; SIA.AR.ap_sexo; SIA.ATD.ap_sexo; SIA.BI.sexopac; SIA.PA.pa_sexo; SIA.PS.sexopac; SIA.SAD.sexopac; SIH.RD.sexo; SIH.RJ.sexo; SIM.DOE.sexo; SIM.DOF.sexo; SIM.DOI.sexo; SIM.DOM.sexo; SIM.DO.sexo; SINAN.ANIM.cs_sexo; SINAN.BOTU.cs_sexo; SINAN.CHAG.cs_sexo; SINAN.CHIK.cs_sexo; SINAN.COLE.cs_sexo; SINAN.COQU.cs_sexo; SINAN.DENG.cs_sexo; SINAN.DIFT.cs_sexo; SINAN.ESQU.cs_sexo; SINAN.FAMA.cs_sexo; SINAN.FMAC.cs_sexo; SINAN.FTIF.cs_sexo; SINAN.HANS.cs_sexo; SINAN.HANT.cs_sexo; SINAN.IEXO.cs_sexo; SINAN.LEIV.cs_sexo; SINAN.LEPT.cs_sexo; SINAN.LTA.cs_sexo; SINAN.LTAN.cs_sexo; SINAN.MALA.cs_sexo; SINAN.MENI.cs_sexo; SINAN.PEST.cs_sexo; SINAN.PFA.cs_sexo; SINAN.RAIV.cs_sexo; SINAN.TETA.cs_sexo; SINAN.TUBE.cs_sexo; SINAN.VIOL.cs_sexo; SINAN.ZIKA.cs_sexo; SINASC.DNR.sexo; SINASC.DN.sexo varchar(1) Sexo;Sexo (0=3, M=1, F=2);Sexo do Paciente

Entretanto, não são carregados todos os atributos do SIA, apenas os adotados na Avaliação de Tecnologias em Saúde até o presente momento.

# codigo bash: estrutura do dbf
dbview -e -o $fdbf | 
    awk -F'\t' '{ gsub(/[ \t]+$/,"",$1);gsub(" ","_",$1); print tolower($1)","$2","$3}' | 
    sed 1d | sed 's/ //g' | nl | awk '{print $1","$2}' > $fstr

# numero de colunas 
lcol=$(tail -1 $fstr | awk -F',' '{print $1+1}')

# posicao das colunas
nu_apac=$(cat $fstr | egrep -i ",ap_autoriz,|,autoriz,|,nu_autorizacao,|,pa_autoriz,|,pa_numapa,|,ap_autoriz," | head -1 | awk -F','  '{print $1}');
nu_apac_anterior=$(cat $fstr | egrep -i "apacant," | head -1 | awk -F','  '{print $1}')
nu_cnspcn=$(cat $fstr | egrep -i ",ap_cnspcn,|,cns_pac,|,nu_cnspcn," | head -1 | awk -F','  '{print $1}')
co_procedimento=$(cat $fstr | egrep -i "ap_pripal,|,co_procede,|,pa_proc_id,|,proc_id,|,proc_rea,|,sp_procrea,|,pa_codpro," | head -1 | awk -F','  '{print $1}')
co_gestao=$(cat $fstr | egrep -i ",ap_gestao,|,gestao,|,pa_gestao,|,uf_zi,|,sp_gestor," | head -1 | awk -F','  '{print $1}')
co_ibge_municipio_residencia=$(cat $fstr | egrep -i ",ap_munpcn,|,codmunres,|,co_munres,|,munic_res,|,munpac,|,mun_res,|,pa_munpcn,|,ufmunres," | head -1 | awk -F','  '{print $1}')
co_cep_residencia=$(cat $fstr | egrep -i ",cep,|,ap_ceppcn," | head -1 | awk -F','  '{print $1}')
co_carater_atendimento=$(cat $fstr | egrep -i "catend," | head -1 | awk -F','  '{print $1}')
sg_sexo=$(cat $fstr | egrep -i "ap_sexo,|,co_sexo,|,cs_sexo,|,pa_sexo,|,sexo,|,sexopac," | head -1 | awk -F','  '{print $1}')
nu_idade=$(cat $fstr | egrep -i "ap_nuidade,|,idade,|,idadepac,|,nu_idade_n,|,pa_idade," | head -1 | awk -F','  '{print $1}')
co_motivo_saida=$(cat $fstr | egrep -i "motsai," | head -1 | awk -F','  '{print $1}')
co_ibge_municipio_evento=$(cat $fstr | egrep -i ",ap_ufmun,|,codufmun,|,id_municip,|,munic_mov,|,pa_ufmun,|,ufmun,|,codmunocor,|,pa_munat," | head -1 | awk -F','  '{print $1}')
dt_inicio=$(cat $fstr | egrep -i ",ap_dtinic,|,dt_inter,|,sp_dtinter,|,ap_dtocor,|,dt_atend,|,inicio,|,ant_dt_aci,|,dt_inter," | head -1 | awk -F','  '{print $1}')
dt_fim=$(cat $fstr | egrep -i ",dt_saida,|,sp_dtsaida,|,ap_dtfim,|,fim," | head -1 | awk -F','  '{print $1}')
co_cnes_estabelecimento=$(cat $fstr | egrep -i ",ap_coduni,|,cnes,|,cnes_exec,|,co_cnescon,|,coduni,|,id_unidade,|,pa_coduni,|,sp_cnes,|,sp_cnes," | head -1 | awk -F','  '{print $1}')
co_cidpri=$(cat $fstr | egrep -i ",ap_cidpri,|,causabas,|,cidpri,|,diag_princ,|,pa_cidpri,|,sp_cidpri," | head -1 | awk -F','  '{print $1}')
co_cidsec=$(cat $fstr | egrep -i ",ap_cidsec,|,diag_sec,|,diagsec1,|,diagsec2,|,diagsec3,|,diagsec4,|,diagsec5,|,diagsec6,|,diagsec7,|,diagsec8,|,diagsec9,|,diag_secun,|cidsec,|,sp_cidsec," | head -1 | awk -F','  '{print $1}')
qt_aprovada=$(cat $fstr | egrep -i ",pa_qtdapr,|,qt_aprov," | head -1 | awk -F','  '{print $1}')
vl_aprovado=$(cat $fstr | egrep -i ",pa_valapr,|,qt_proced,|,val_tot,|,vl_aprov," | head -1 | awk -F','  '{print $1}')
pa_condic=$(cat $fstr | egrep -i ",pa_condic,|,ap_condic,|,condic," | head -1 | awk -F','  '{print $1}')

3.3.9 transpor o caractere criptografado do CNS para número e consolidar o arquivo csv do SIA com a mesma estrutura para diferentes subsistemas (AQ, AR, AN, AM, BI, PA, PS)

Nessa etapa são transpostos os caracteres especiais contidos na chave criptofráfica do CNS para números escolhidos aleatoreamente. Dessa forma, não há referência direta ao CNS verdadeiro do usuário do SUS.

# codigo bash: obtem csv com campos selecionados e converte caracteres do CNS criptografado
  paste -d, <(cat /tmp/tmp.$curdt.csv | sed 's/[^0-9\^]//g' | awk -F'^' -v uf=$uf -v nu_apac=$nu_apac -v nu_apac_anterior=$nu_apac_anterior -v co_procedimento=$co_procedimento -v co_gestao=$co_gestao -v co_ibge_municipio_residencia=$co_ibge_municipio_residencia -v co_cep_residencia=$co_cep_residencia -v co_carater_atendimento=$co_carater_atendimento -v nu_idade=$nu_idade -v co_motivo_saida=$co_motivo_saida -v co_ibge_municipio_evento=$co_ibge_municipio_evento -v dt_inicio=$dt_inicio -v dt_fim=$dt_fim -v co_cnes_estabelecimento=$co_cnes_estabelecimento -v qt_aprovada=$qt_aprovada -v d=$cmp '{print d","uf"," $nu_apac"," $nu_apac_anterior"," $co_procedimento","$co_gestao","$co_ibge_municipio_residencia"," $co_cep_residencia","$co_carater_atendimento"," $nu_idade"," $co_motivo_saida"," $co_ibge_municipio_evento"," $dt_inicio","$dt_fim"," $co_cnes_estabelecimento"," $qt_aprovada}'   ) <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v vl_aprovado=$vl_aprovado '{print $vl_aprovado}' | iconv -f "CP861" -t "UTF-8" | sed 's/[^0-9\.]//g' ) <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v nu_cnspcn=$nu_cnspcn '{print $nu_cnspcn}' | sed 's/{/0/g' | sed 's/}/9/g' | sed 's/~/8/g' | sed 's/ /7/g' | sed 's/Ç/6/g' | sed 's/ä/5/g' | sed 's/ü/4/g' | sed 's/é/3/g' | sed 's/|/2/g' | sed 's/â/1/g' | sed 's/[^0-9]/ /g'  | sed 's/ /4/g') <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v sg_sexo=$sg_sexo -v co_cidpri=$co_cidpri -v co_cidsec=$co_cidsec -v pa_condic=$pa_condic -v o=$origem '{print substr($sg_sexo,1,1)"," substr($co_cidpri,1,4)"," substr($co_cidsec,1,50)","substr($pa_condic,1,2)","o}' | sed 's/0000//g') | sort -h > /tmp/tmp.$curdt.x.csv 

mv /tmp/tmp.$curdt.x.csv /tmp/tmp.$curdt.csv

3.3.10 Remoção de registros da tabela bd_sabeis.tf_medicamento

As origens; segundo uf, mês e ano; cujas quantidades aprovadas não foram correspondentes ao tabnet são eliminadas antes de realizar nova carga.

delete from bd_sabeis.tf_medicamento 
where sg_uf || nu_competencia::text in (select sg_uf || nu_competencia::text as co_ufcompetencia
   from bd_sabeis.tf_medicamento_qualidade_uf_mes 
 where qt_aprovada_tabnet > 0
   and qt_aprovada_sabeis <> qt_aprovada_tabnet
   and to_date(nu_competencia::text || '01', 'YYYYMMDD')::date > current_date - ( 365*6 ) order by 1)

3.3.11 criar estrutura das tabelas temporárias

Para cada UF, e em janela de três meses respectivos são realizadas cargas nas tabelas abaixo.

drop table if exists sia_pa, sia_am, sia_an, sia_ar, sia_bi, sia_ps, sia_aq cascade;

  create table if not exists sia_pa (
 nu_competencia int4 NULL,
 sg_uf char(2) default null,
 nu_apac int8 NULL,
 nu_apac_anterior int8 NULL,
 co_procedimento int8 NULL,
 co_gestao int8 NULL,
 co_ibge_municipio_residencia int4 NULL,
 co_cep_residencia int4 NULL,
 co_carater_atendimento int8 NULL,
 nu_idade int4 NULL,
 co_motivo_saida int2 NULL,
 co_ibge_municipio_evento int4 NULL,
 dt_inicio int8 NULL,
 dt_fim int8 NULL,
 co_cnes_estabelecimento int4 NULL,
 qt_aprovada int8 NULL,
 vl_aprovado float8 NULL,
 nu_cnspcn int8 NULL,
 sg_sexo bpchar(1) NULL,
 co_cidpri bpchar(4) NULL,
 co_cidsec varchar(50) NULL,
 sg_tipo_gestao char(2) default null,
 no_origem varchar(20) NULL,
 vl_aprovado_ipca decimal(12,4) default null
);

CREATE INDEX sia_pa_co_cnes_estabelecimento_idx ON sia_pa (co_cnes_estabelecimento);

CREATE TABLE sia_am (LIKE sia_pa INCLUDING ALL);
CREATE TABLE sia_an (LIKE sia_pa INCLUDING ALL);
CREATE TABLE sia_ar (LIKE sia_pa INCLUDING ALL);
CREATE TABLE sia_bi (LIKE sia_pa INCLUDING ALL);
CREATE TABLE sia_ps (LIKE sia_pa INCLUDING ALL);
CREATE TABLE sia_aq (LIKE sia_pa INCLUDING ALL);

As competências contíguas da mesma UF são listadas com a consulta SQL a seguir.

   select sg_uf, 
          left(replace((TO_DATE(nu_competencia::text || '15', 'YYYYMMDD') - 20)::text,'-',''),6) cmp1,
          nu_competencia cmp2,
          left(replace((TO_DATE(nu_competencia::text || '15', 'YYYYMMDD') + 20)::text,'-',''),6) cmp3,
          DENSE_RANK () OVER ( PARTITION BY sg_uf 
          ORDER BY nu_competencia ) as co_seq_competencia
  from bd_sabeis.tf_medicamento_qualidade_uf_mes 
 where qt_aprovada_tabnet > 0
   and qt_aprovada_sabeis <> qt_aprovada_tabnet
   and to_date(nu_competencia::text || '01', 'YYYYMMDD')::date > current_date - ( 365*6)
   order by sg_uf , cmp2

3.3.12 carga, incorporação do CNS e inserção no repositório SABEIS

A tabela 3.11 exemplifica a sequência da janela deslizante de dados que são incorporados na tabela provisória.

Table 3.11: Lista com a fila de arquivos a serem incorporados
sg_uf cmp1 cmp2 cmp3 co_seq_competencia
AC 201605 201606 201607 1
AC 201605 201606 201607 1
AC 201606 201607 201608 2
AC 201606 201607 201608 2
AC 201607 201608 201609 3
AC 201607 201608 201609 3
AC 201608 201609 201610 4
AC 201608 201609 201610 4
AC 201609 201610 201611 5
AC 201609 201610 201611 5
AC 201610 201611 201612 6
AC 201610 201611 201612 6
AC 201611 201612 201701 7
AC 201611 201612 201701 7
AC 201612 201701 201702 8
AC 201612 201701 201702 8
AC 201701 201702 201703 9
AC 201701 201702 201703 9
AC 201702 201703 201704 10
AC 201702 201703 201704 10

O algoritmo abaixo faz para cada UF e janela de três mêses a incorporação dos registros PA, a atualização do CNS a partir dos demais subsistemas do SIA segundo o número de autorização da APAC.

Ao final de cada iteração, dados de uma competência e um estado são incorporados.

O processo é repetido até que todas os dados por UF e competência tenham o total de quantidade aprovada identico ao publicizado via transparência ativa pelo mecanismo do TabNet.

# lista competencias verus uf e enumera para a janela deslizante de 3 competencias
echo "
   select sg_uf, 
          left(replace((TO_DATE(nu_competencia::text || '15', 'YYYYMMDD') - 20)::text,'-',''),6) cmp1,
          nu_competencia cmp2,
          left(replace((TO_DATE(nu_competencia::text || '15', 'YYYYMMDD') + 20)::text,'-',''),6) cmp3,
          DENSE_RANK () OVER ( PARTITION BY sg_uf 
          ORDER BY nu_competencia ) as co_seq_competencia
  from bd_sabeis.tf_medicamento_qualidade_uf_mes 
 where qt_aprovada_tabnet > 0
   and qt_aprovada_sabeis <> qt_aprovada_tabnet
   and to_date(nu_competencia::text || '01', 'YYYYMMDD')::date > current_date - ( 365*6)
   order by sg_uf , cmp2

"| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -t --csv | 
nl | sed 's/\t/,/g' | sed 's/ //g' > /tmp/$curdt.cmp.uf.lst

# para cada UF e mes, mes anterior e mes posterior
for line in $(cat /tmp/$curdt.cmp.uf.lst );do
   uf=$(echo $line | awk -F',' '{print $2}')
   cmp1=$(echo $line | awk -F',' '{print $3}')
   cmp2=$(echo $line | awk -F',' '{print $4}')
   cmp3=$(echo $line | awk -F',' '{print $5}')

  # elimina registros de UF diferente e insere um registro falso para o egrep a seguir
   echo "DELETE FROM sia_pa where sg_uf <> '$uf'; 
         DELETE FROM sia_am where sg_uf <> '$uf'; 
         DELETE FROM sia_aq where sg_uf <> '$uf'; 
         DELETE FROM sia_ar where sg_uf <> '$uf'; 
         DELETE FROM sia_an where sg_uf <> '$uf'; 
         DELETE FROM sia_bi where sg_uf <> '$uf'; 
         INSERT INTO sia_pa (nu_competencia, qt_aprovada) VALUES (198403,0);" |
     PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e
   
   # obtem competencias ja contidas em rodada anterior
   ncmp=$(echo "select string_agg(distinct nu_competencia::text,',|,')  
          from sia_pa"| 
            PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -t --csv | 
            sed 's/"//g')

   # carrega competencias nao listadas
   for line2 in $(cat /tmp/$log.csv | 
                    awk -F',' -v uf=$uf -v cmp1=$cmp1 -v cmp2=$cmp2 
                  -v cmp3=$cmp3 '{if ( $7 == uf && ($8 == cmp2 || $8 == cmp1 ||
                  $8 == cmp3) && ( $6 == "AM" || $6 == "AN" || $6 == "AR" || 
                  $6 == "BI" || $6 == "PS" || $6 == "AQ" || $6 == "PA" ) ) 
                  print $0}' | sort -u | egrep -v "$ncmp" );do
      origem=$(echo $line2 | awk -F'.' '{print tolower($1)}')
      sg_sistema=$(echo $line2 | awk -F',' '{print $5}')
      sg_subsistema=$(echo $line2 | awk -F'[, ]' '{print $6}')
      cmp=$(echo $line2 | awk -F'[, ]' '{print $8}')
      schema=$(echo "$sg_sistema" | awk '{print tolower($0)}')
      tb=$(echo "$sg_subsistema" | awk '{print tolower($0)}')

      # converte DBC para CSV (vide apendice)   
      dbc2csv $origem $exe $curdt $cmp

      # persiste 
      if [ -f "/tmp/tmp.$curdt.csv" ]; then

        # filtra apenas medicamentos se o arquivo for PA
        if [ "$sg_subsistema" = "PA" ]; then 
          awk -F',' '{ if ( substr($5,1,2) == "06" && $16 > 0 ) print $0}' /tmp/tmp.$curdt.csv > /tmp/tmp.$curdt.tmp ;
          mv /tmp/tmp.$curdt.tmp /tmp/tmp.$curdt.csv 
        fi

       # carrega CSV no POSTGRESQL
       PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt  -c \
      "COPY ${schema}_${tb} (nu_competencia, sg_uf, nu_apac, nu_apac_anterior, co_procedimento, co_gestao, co_ibge_municipio_residencia, co_cep_residencia, co_carater_atendimento,  nu_idade, co_motivo_saida, co_ibge_municipio_evento, dt_inicio, dt_fim, co_cnes_estabelecimento, qt_aprovada, vl_aprovado, nu_cnspcn, sg_sexo, co_cidpri, co_cidsec, sg_tipo_gestao, no_origem) FROM STDIN quote '\"' delimiter ',' csv " < /tmp/tmp.$curdt.csv
    
      rm $fdbc; rm $fdbf; rm $fdbf.str

     fi
         
   done #line2
   
   # atualiza valor
   echo "ALTER TABLE public.sia_pa ALTER COLUMN co_procedimento TYPE varchar(13) USING co_procedimento::varchar;
ALTER TABLE public.sia_pa ALTER COLUMN co_procedimento TYPE tsquery USING co_procedimento::tsquery;

update sia_pa A
set vl_aprovado = A.qt_aprovada::decimal * B.vl_unitario
from (select * from bd_sabeis.vw_medicamento_preco_data
  where (date_trunc('month', TO_DATE('$cmp2'::text || '15', 'YYYYMMDD')) + interval '1 month' - interval '1 day')::date
  between dt_compra_inicio and dt_compra_fim
  order by 2) B
where A.co_procedimento @@ B.co_procedimento and nu_competencia = $cmp2;

ALTER TABLE public.sia_pa ALTER COLUMN co_procedimento TYPE varchar(13) USING co_procedimento::varchar;
ALTER TABLE public.sia_pa ALTER COLUMN co_procedimento TYPE int8 USING replace(co_procedimento::text, '''', '')::int8;
"| 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e
   
   
   query3="select table_name 
  from information_schema.tables 
 where table_schema = 'public'
  and table_name like 'sia_%'
  and table_name <> 'sia_pa'"

  # para cada tabela da lista sia_am, sia_an, sia_ar, sia_bi, sia_ps e sia_aq
for line3 in $(echo $query3 | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -t --csv);do
      tb2=$(echo $line3 | awk -F',' '{print $1}')

    echo "
      -- atualiza por CNES e autorizacao APAC
      update sia_pa A 
         set nu_cnspcn = B.nu_cnspcn
        from $tb2 B
       where A.co_cnes_estabelecimento = B.co_cnes_estabelecimento 
         and A.nu_apac = B.nu_apac 
         and B.nu_cnspcn > 0
         and A.nu_apac > 0;
      
      -- atualiza por gestao e autorizacao APAC   
      update sia_pa A 
         set nu_cnspcn = B.nu_cnspcn
        from $tb2 B
       where A.co_gestao = B.co_gestao 
         and A.nu_apac = B.nu_apac 
         and A.nu_cnspcn is null
         and B.nu_cnspcn > 0
         and A.nu_apac > 0;         
       
      -- atualiza por autorizacao APAC 
      update sia_pa A 
         set nu_cnspcn = B.nu_cnspcn
        from $tb2 B
       where A.nu_apac = B.nu_apac 
         and A.nu_cnspcn is null
         and B.nu_cnspcn > 0
         and A.nu_apac > 0;           
         "| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -q
done


# carga na tabela da sabeis
  echo "insert into bd_sabeis.tf_medicamento
select $cmp2, '$uf', nu_cnspcn, sg_sexo, co_procedimento, co_cidpri, co_cidsec, 
co_ibge_municipio_residencia, nu_idade, co_ibge_municipio_evento, 
co_cnes_estabelecimento, co_gestao, qt_aprovada, vl_aprovado, nu_apac, 
no_origem, null 
from sia_pa where substr(co_procedimento::text,1,1)='6' and qt_aprovada > 0 
and nu_competencia = $cmp2 and sg_uf='$uf' order by co_procedimento, co_cidpri, 
co_ibge_municipio_evento, co_cnes_estabelecimento
"| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

# elimina registros do mes anterior  
   echo "DELETE FROM sia_pa where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_am where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_aq where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_an where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_ar where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_bi where nu_competencia = $cmp1 AND sg_uf ='$uf';
         DELETE FROM sia_ps where nu_competencia = $cmp1 AND sg_uf ='$uf';         
         " | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt
done

3.3.13 atualiza controle de qualidade

Ao final, a tabela bd_sabeis.tf_medicamento_qualidade_uf_mes referente ao controle de qualidade da quantidade aprovada deve ser atualizada.

  update bd_sabeis.tf_medicamento_qualidade_uf_mes A
    set qt_aprovada_sabeis = B.qt_aprovada
   from (
 select sg_uf,
        nu_competencia,
        sum(qt_aprovada) qt_aprovada
   from bd_sabeis.tf_medicamento M
  group by 1,2
  order by 1,2
  ) B
  where A.nu_competencia = B.nu_competencia
  and A.sg_uf = B.sg_uf;

3.4 Extração das dispensações dos medicamentos em estudo

Os atributos selecionados estão listados no script a seguir.

-- cria tabela para realizar a extracao
drop table if exists bd_sabeis.tf_psi;

create table bd_sabeis.tf_psi (
       nu_usuariosus int8,
       nu_competencia int4,
       co_seq_mes int2,   
       sg_procedimento varchar(10) default null,
       co_seq_medicamento_mes int2,
       co_procedimento int8,       
       nu_idade int2,        
       sg_sexo char(1),       
       sg_uf char(2), 
       co_cidpri char(4), 
       co_seq_cidpri_mes int2,
       co_ibge_municipio_evento int4, 
       co_ibge_municipio_residencia int4, 
       co_cnes_estabelecimento int8, 
       qt_aprovada int2 default 0,
       vl_aprovado decimal(21,2) default 0
);

O critério de seleção foram os CID-10 (F200, F201, F202, F203, F204, F205’, ‘F206, F208, F250, F251, F252, F311,’ ‘F312, F313, F314, F315, F316, F317,’ ’F840, F841, F843, F845 e F848) no período contemplado pelo repositório SABEIS (06/2016 a 03/2022. A extração SQL é mostrada a seguir.

insert into bd_sabeis.tf_psi
select nu_usuariosus,
       nu_competencia,
       RANK () OVER ( 
        PARTITION BY nu_usuariosus
        ORDER BY nu_competencia 
       ) co_seq_mes,
       case 
         when co_procedimento = 601110013 then 'CLOZ100'
         when co_procedimento in (601110021,604230079) then 'CLOZ025'
         when co_procedimento in (601110030,604230028) then 'OLAN10'
         when co_procedimento in (601110048,604230010) then 'OLAN05'
         when co_procedimento in (601110056,604230044) then 'QUET100'
         when co_procedimento in (601110064,604230036) then 'QUET025'
         when co_procedimento in (601110072,604230052) then 'QUET200'
         when co_procedimento in (604230060,604230052) then 'QUET300'
         when co_procedimento = 601110080 then 'RISP1'
         when co_procedimento = 601110099 then 'RISP2'
         when co_procedimento in (601110102,604210027) then 'ZIPR80'
         when co_procedimento in (601110110,604210019) then 'ZIPR40'
         when co_procedimento = 604510012 then 'RISP1'
         when co_procedimento = 604510020 then 'RISP2'
         when co_procedimento = 604510039 then 'RISP3'
         when co_procedimento = 604510047 then 'RISPSO1'
         when co_procedimento = 604500050 then 'LAMO100'
         when co_procedimento = 604500033 then 'LAMO025'
         when co_procedimento = 604500041 then 'LAMO050'
         when co_procedimento in (601110013, 604230087) then 'CLOZ100'
         else null end sg_procedimento,
              DENSE_RANK () OVER ( 
        PARTITION BY nu_usuariosus,
               case 
         when co_procedimento = 601110013 then 'CLOZ100'
         when co_procedimento in (601110021,604230079) then 'CLOZ025'
         when co_procedimento in (601110030,604230028) then 'OLAN10'
         when co_procedimento in (601110048,604230010) then 'OLAN05'
         when co_procedimento in (601110056,604230044) then 'QUET100'
         when co_procedimento in (601110064,604230036) then 'QUET025'
         when co_procedimento in (601110072,604230052) then 'QUET200'
         when co_procedimento in (604230060,604230052) then 'QUET300'
         when co_procedimento = 601110080 then 'RISP1'
         when co_procedimento = 601110099 then 'RISP2'
         when co_procedimento in (601110102,604210027) then 'ZIPR80'
         when co_procedimento in (601110110,604210019) then 'ZIPR40'
         when co_procedimento = 604510012 then 'RISP1'
         when co_procedimento = 604510020 then 'RISP2'
         when co_procedimento = 604510039 then 'RISP3'
         when co_procedimento = 604510047 then 'RISPSO1'
         when co_procedimento = 604500050 then 'LAMO100'
         when co_procedimento = 604500033 then 'LAMO025'
         when co_procedimento = 604500041 then 'LAMO050'
         when co_procedimento in (601110013, 604230087) then 'CLOZ100'
         else null end
        ORDER BY nu_competencia 
       ) co_seq_medicamento_mes,
       co_procedimento,
       nu_idade,
       sg_sexo,
       sg_uf,
       co_cidpri,
       DENSE_RANK () OVER ( 
       PARTITION BY nu_usuariosus, co_cidpri 
        ORDER BY nu_competencia 
       ) co_seq_cidpri_mes,
       co_ibge_municipio_evento,
       co_ibge_municipio_residencia,
       co_cnes_estabelecimento,
       qt_aprovada,
       vl_aprovado
  from bd_sabeis.tf_medicamento  
 where co_cidpri in (
       'F200', 'F201', 'F202', 'F203', 'F204', 'F205', 
       'F206', 'F208', 'F250', 'F251', 'F252', 'F311', 
       'F312', 'F313', 'F314', 'F315', 'F316', 'F317', 
       'F840', 'F841', 'F843', 'F845', 'F848'
       )
order by nu_usuariosus, nu_competencia, co_procedimento;

4 Resultados

A formação do repositório SABEIS processa 11.193 arquivos DBC contendo 3.614.970.401 registros.

4.1 Qualidade da extração

A tabela 4.1 indica o quantitativo de arquivos e o valor em bytes do presente no diretório FTP em relação ao presente no disco local.

# sumario dos dados abertos do DATASUS
query=paste0("select ROUND(nu_competencia/100) as ano,
       sum(qt_aprovada_tabnet) qt_aprovada_tabnet,
       sum(qt_aprovada_sabeis) qt_aprovada_sabeis,
       sum(qt_aprovada_tabnet)-sum(qt_aprovada_sabeis) as diferença,
       min(nu_competencia) nu_competencia_min,
       max(nu_competencia) nu_competencia_max
  from bd_sabeis.tf_medicamento_qualidade_uf_mes
 where nu_competencia >= (
    select MIN(nu_competencia)
      from bd_sabeis.tf_medicamento_qualidade_uf_mes
     where qt_aprovada_sabeis > 0
 )
 group by 1
 order by 1")
  
kbprocessamento=pg_roda_query(query)

totaissabeis=pg_roda_query("select * from bd_sabeis.vw_medicamento_totais")
totaissabeisestudo=pg_roda_query(
  " select count(*) registros,
        count(distinct nu_usuariosus) usuarios,
        count(distinct co_procedimento) medicamentos,
        count(distinct co_cidpri) diagnosticos ,
        count(distinct co_ibge_municipio_residencia) co_ibge_municipio_residencia ,
        count(distinct co_cnes_estabelecimento) co_cnes_estabelecimento ,
       min(nu_competencia) nu_competencia_min,
       max(nu_competencia) nu_competencia_max
   from bd_sabeis.tf_psi")

# formata decimal, milhar e data
kbprocessamento[2:4]=formata(kbprocessamento[2:4])
kbprocessamento$nu_competencia_min=formata_mes(kbprocessamento$nu_competencia_min)
kbprocessamento$nu_competencia_max=formata_mes(kbprocessamento$nu_competencia_max)
Table 4.1: Quantidade aprovada no Sistema de Informações Ambulatoriais (SIA) divulgada via TabNet e incorporada na Sala Aberta de Inteligência em Saúde - SABEIS
ano qt aprovada tabnet qt aprovada sabeis diferença nu competencia min nu competencia max
2016 1.036.448.056 1.036.448.056 0 06/2016 12/2016
2017 1.844.097.374 1.844.097.374 0 01/2017 12/2017
2018 1.940.359.144 1.940.359.144 0 01/2018 12/2018
2019 2.036.156.112 2.036.156.112 0 01/2019 12/2019
2020 2.245.796.604 2.245.796.604 0 01/2020 12/2020
2021 2.339.383.662 2.339.383.662 0 01/2021 12/2021
2022 596.832.036 596.832.036 0 01/2022 03/2022

Não houve divergência em relação ao númerode bytes dos arquivos listados do FTP em relação ao baixado.

Na SABEIS são disponibilizados dados entre 06/2016 e 03/2022, contemplando o atendimento a 4.963.729 usuários do SUS, correspondente a 132.851.148 registros de dispensação de 316 medicamentos segundo o código SIGTAP para 388 diagnósticos primários. Os usuários de medicamentos registrados no SIA foram atendidos em 270 estabelecimentos.

Table 4.2: Competencias com divergência na quantidade aprovada de dispensação de medicamento registrada no Sistema de Informações Ambulatoriais (SIA) e incorporada na Sala Aberta de Inteligência em Saúde - SABEIS e divulgadas via Tabnet relativa ao intervalo de confiança da média de mais ou menos dois meses da competência e variação superior a 10%.
sg uf nu competencia qt aprovada sabeis qt aprovada tabnet media desvio ic min ic max n
AC 201808 0 0 128.868 105.503 74.587 183.150 12
AC 201809 246.686 246.686 82.229 121.460 19.737 144.720 12
AC 201810 0 0 122.770 105.191 68.649 176.891 12
AC 201812 222.259 222.259 122.426 84.798 78.797 166.054 12
AC 201901 23.395 23.395 133.125 73.579 101.010 165.240 16
AC 201904 137.560 137.560 186.344 62.644 154.113 218.574 12
AC 201905 270.827 270.827 181.960 65.633 148.192 215.728 12
AC 201908 5.814 5.814 101.832 70.914 65.347 138.317 12
AC 201911 272.202 272.202 141.917 113.603 83.468 200.366 12
AC 201912 5.960 5.960 186.541 133.426 117.893 255.189 12
AC 202001 281.461 281.461 145.922 117.519 85.458 206.386 12
AL 201807 739.819 739.819 435.218 329.880 265.494 604.943 12
AL 201808 0 0 771.950 672.441 425.977 1.117.923 12
AL 201809 1.576.031 1.576.031 803.112 672.392 457.165 1.149.059 12
AL 202012 642.295 642.295 793.425 111.780 735.913 850.936 12
AL 202101 861.897 861.897 738.485 89.448 699.444 777.527 16
AL 202106 547.668 547.668 678.249 116.369 618.377 738.121 12
AM 202012 0 0 479.071 391.508 277.639 680.503 12
AM 202101 915.143 915.143 437.080 337.713 289.678 584.482 16
AM 202201 0 0 477.557 339.964 329.173 625.941 16
AM 202202 930.372 930.372 473.253 396.891 269.052 677.454 12
AP 201606 33.080 33.080 20.161 15.085 12.400 27.922 12
AP 201707 0 0 39.645 35.215 21.527 57.764 12
AP 201708 82.410 82.410 27.470 40.576 6.594 48.346 12
AP 201709 0 0 54.512 40.264 33.796 75.228 12
AP 201710 81.126 81.126 39.875 34.607 22.070 57.680 12
AP 202005 0 0 32.453 28.592 17.742 47.164 12
AP 202006 66.960 66.960 34.172 28.570 19.472 48.871 12
AP 202201 58.073 58.073 40.932 24.875 30.074 51.789 16
AP 202203 0 0 23.494 25.116 6.981 40.007 8
BA 201708 3.388.298 3.388.298 2.812.319 436.870 2.587.548 3.037.090 12
BA 201810 1.752.501 1.752.501 2.201.695 340.279 2.026.620 2.376.770 12
BA 201811 2.515.060 2.515.060 1.753.118 649.523 1.418.937 2.087.300 12
BA 201812 991.794 991.794 1.691.164 656.002 1.353.650 2.028.679 12
BA 201901 1.566.639 1.566.639 2.312.948 1.092.675 1.836.028 2.789.868 16
BA 201905 2.199.254 2.199.254 2.753.559 445.980 2.524.101 2.983.017 12
BA 201912 2.967.193 2.967.193 4.150.557 1.476.468 3.390.910 4.910.204 12
BA 202001 6.137.653 6.137.653 4.168.194 1.466.189 3.413.835 4.922.552 12
DF 201806 227.400 227.400 620.224 502.647 361.611 878.838 12
DF 201907 1.019.721 1.019.721 1.307.190 300.261 1.152.705 1.461.675 12
DF 201910 2.135.964 2.135.964 1.798.581 249.454 1.670.236 1.926.925 12
DF 202001 2.188.158 2.188.158 1.804.222 284.648 1.657.770 1.950.674 12
ES 202202 4.045.367 4.045.367 3.408.237 476.807 3.162.918 3.653.556 12
GO 202106 48.378 48.378 1.811.098 1.304.574 1.139.891 2.482.305 12
GO 202107 2.791.175 2.791.175 1.752.044 1.268.338 1.099.480 2.404.607 12
MA 201610 491.769 491.769 402.095 67.874 367.174 437.017 12
MA 201701 0 0 370.751 295.293 241.864 499.637 16
MA 201702 800.743 800.743 416.460 295.449 287.505 545.414 16
MA 201703 379.129 379.129 555.280 186.923 459.107 651.452 12
MG 201606 8.004.123 8.004.123 5.064.440 3.756.443 3.131.739 6.997.141 12
MG 201802 5.764.070 5.764.070 7.091.306 837.808 6.725.628 7.456.983 16
MG 201902 11.772.573 11.772.573 7.582.042 2.656.488 6.422.564 8.741.519 16
MG 201903 4.884.609 4.884.609 7.662.218 3.097.585 6.068.502 9.255.934 12
MG 202001 9.583.592 9.583.592 8.117.144 1.085.115 7.558.850 8.675.439 12
MG 202002 7.462.508 7.462.508 8.814.914 1.001.927 8.299.420 9.330.408 12
MT 201606 543.789 543.789 330.799 247.660 203.378 458.221 12
MT 201612 577.458 577.458 423.686 193.523 324.118 523.254 12
MT 201701 163.058 163.058 386.607 152.270 320.146 453.068 16
MT 201704 541.471 541.471 458.609 62.897 426.249 490.970 12
MT 201710 113.686 113.686 245.097 129.386 178.528 311.666 12
MT 201712 737.094 737.094 586.114 140.114 514.024 658.203 12
PA 201902 389.689 389.689 278.502 170.870 203.922 353.082 16
PA 201904 0 0 331.246 292.242 180.887 481.605 12
PA 201905 684.324 684.324 339.896 291.816 189.756 490.036 12
PA 201910 350.377 350.377 269.387 60.061 238.485 300.288 12
PA 202004 137 137 190.452 140.587 118.120 262.785 12
PA 202006 558.149 558.149 377.458 133.459 308.792 446.123 12
PA 202012 595.410 595.410 349.826 235.103 228.864 470.787 12
PA 202104 239.569 239.569 329.411 70.123 293.333 365.490 12
PA 202107 203.035 203.035 299.634 72.909 262.123 337.146 12
PB 201606 1.062.619 1.062.619 631.468 476.643 386.233 876.702 12
PB 201607 831.784 831.784 1.028.257 154.993 948.513 1.108.002 12
PB 201810 585.289 585.289 1.077.289 422.443 859.941 1.294.637 12
PB 201811 1.575.938 1.575.938 720.409 679.352 370.881 1.069.937 12
PB 201812 0 0 1.260.903 969.309 762.191 1.759.615 12
PB 201901 2.206.770 2.206.770 1.235.393 819.771 877.587 1.593.198 16
PB 201904 625.486 625.486 974.763 280.789 830.296 1.119.229 12
PB 202012 1.114.056 1.114.056 1.459.130 293.560 1.308.093 1.610.168 12
PR 202005 5.551.761 5.551.761 7.637.575 1.579.491 6.824.923 8.450.228 12
RJ 201707 0 0 1.203.833 1.279.510 545.522 1.862.144 12
RJ 201709 8.780.087 8.780.087 4.299.436 3.498.807 2.499.290 6.099.582 12
RJ 201906 1.080.511 1.080.511 2.195.371 957.013 1.702.985 2.687.757 12
RJ 201908 2.769.469 2.769.469 2.193.342 486.079 1.943.253 2.443.431 12
RJ 201909 1.629.718 1.629.718 2.306.568 511.034 2.043.640 2.569.497 12
RJ 202007 2.038.855 2.038.855 3.639.577 1.347.054 2.946.514 4.332.639 12
RJ 202008 5.197.089 5.197.089 3.255.299 1.449.315 2.509.623 4.000.976 12
RJ 202009 2.529.954 2.529.954 3.539.809 1.233.700 2.905.067 4.174.551 12
RJ 202101 2.301.532 2.301.532 3.648.861 966.524 3.227.002 4.070.719 16
RJ 202102 4.941.038 4.941.038 3.684.129 966.111 3.262.450 4.105.807 16
RJ 202111 9.752.746 9.752.746 5.376.578 3.280.593 3.688.704 7.064.453 12
RN 201606 2.227.544 2.227.544 1.095.723 950.202 606.841 1.584.605 12
RN 201610 0 0 990.610 893.625 530.837 1.450.383 12
RN 201611 2.087.618 2.087.618 1.032.269 890.327 574.193 1.490.344 12
RN 201708 907.906 907.906 1.182.439 203.761 1.077.604 1.287.275 12
RN 201804 3.434 3.434 1.048.861 884.533 593.767 1.503.956 12
RN 201805 2.077.648 2.077.648 1.017.476 885.104 562.088 1.472.865 12
RR 201805 4.655 4.655 55.263 42.892 33.195 77.331 12
RR 201806 105.240 105.240 54.360 42.898 32.289 76.431 12
RR 201807 53.185 53.185 72.685 24.200 60.234 85.136 12
RR 201901 56.843 56.843 43.725 14.215 37.521 49.930 16
RR 201902 24.979 24.979 39.936 11.865 34.757 45.115 16
RR 202012 5.946 5.946 60.136 46.356 36.286 83.986 12
RR 202101 114.659 114.659 61.592 39.721 44.255 78.929 16
RS 201606 2.923.985 2.923.985 1.799.223 1.342.615 1.108.444 2.490.002 12
RS 202011 0 0 3.954.887 3.188.760 2.314.262 5.595.513 12
RS 202012 7.432.467 7.432.467 3.852.596 3.175.609 2.218.737 5.486.456 12
RS 202104 0 0 2.983.284 2.204.894 1.848.860 4.117.708 12
SC 201610 5.056.732 5.056.732 4.185.371 678.192 3.836.439 4.534.302 12
SC 202102 3.079.200 3.079.200 5.623.400 1.810.445 4.833.195 6.413.605 16
SC 202103 8.033.194 8.033.194 5.612.362 2.114.019 4.524.694 6.700.030 12
SE 201905 143.554 143.554 460.875 263.267 325.423 596.327 12
SE 201906 760.181 760.181 458.638 263.121 323.262 594.014 12
TO 201606 159.394 159.394 91.115 70.027 55.086 127.145 12
TO 201905 152.306 152.306 124.576 21.592 113.467 135.685 12
Note:
Intervalo de confiânça calculado por meio da distribuição de student.

A tabela 4.2 demonstra a oscilação mensal em dado estado, com intervalo de confiança \(IC=\mu_0 \pm t \times s\div \sqrt{n}\), calculado a partir da distribuição \(t\) de student a 0,1% de significância, \(\mu_0\) a média, \(s\) o desvio padrão e \(n\) o número de meses considerados. As oscilações sugerem falhas no envio dos dados para consolidação nacional as quais podem ser investigadas com o gestor do dado nas esferas federal e estadual.

4.2 Perfil de utilização de medicamentos

Na SABEIS, os usuários dos medicamentos avaliados entre 06/2016 e 03/2022, somaram a 731.923 (14,7%), correspondente a 19.581.260 registros de dispensação (14,7%) de 17 medicamentos para 23 diagnósticos primários. Os usuários de medicamentos registrados no SIA foram atendidos em 214 estabelecimentos, sendo residentes de 5.339 municípios.

4.2.1 Utilização segundo o diagnóstico

A tabela 4.3 mostra o histórico de dispensações e usuários para as doenças elencadas segundo o diagnóstico primário.

# sumario dos dados abertos do DATASUS
perfildoenca=pg_roda_query(
  " select B.no_diretriz50 ,
        B.co_cid || ' ' || B.no_cid as diagnóstico,
        usuários, 
        registros, 
        medicamentos,
        co_ibge_municipio_residencia municípios, 
        co_cnes_estabelecimento estabelecimentos
 from (
  select co_cidpri,
         count(*) registros,
         count(distinct nu_usuariosus) usuários,
         count(distinct co_procedimento) medicamentos,
         count(distinct co_ibge_municipio_residencia) co_ibge_municipio_residencia ,
         count(distinct co_cnes_estabelecimento) co_cnes_estabelecimento ,
         min(nu_competencia) nu_competencia_min,
         max(nu_competencia) nu_competencia_max
   from bd_sabeis.tf_psi 
  group by 1
  ) A
  left join bd_sabeis.vw_pcdt_atualizado_03_2022 B
  on A.co_cidpri = B.co_cid ")

perfildoenca$registros=formata(perfildoenca$registros)
perfildoenca$usuários=formata(perfildoenca$usuários)
perfildoenca$municípios=formata(perfildoenca$municípios)
x1=perfildoenca$no_diretriz50
perfildoenca$no_diretriz50=NULL
# formata os títulos
colnames(perfildoenca)=str_replace_all(colnames(perfildoenca),'_',' ')

kbl(perfildoenca,
    align = c("l",rep("r",ncol(perfildoenca)-1)),
    caption = "Perfil de utilização segundo o diagnóstico primário.") %>%
    kable_classic()%>% 
  pack_rows(index = table(x1))
Table 4.3: Perfil de utilização segundo o diagnóstico primário.
diagnóstico usuários registros medicamentos municípios estabelecimentos
Comportamento Agressivo no Autismo
F200 Esquizofrenia paranoide 461.797 12.746.788 13 5.194 210
F201 Esquizofrenia hebefrenica 22.853 537.203 13 2.665 202
F202 Esquizofrenia catatonica 3.312 66.856 13 1.048 179
F203 Esquizofrenia indiferenciada 20.331 470.842 13 2.367 201
F204 Depressao pos-esquizofrenica 3.769 80.282 13 954 168
Esquizofrenia
F205 Esquizofrenia residual 20.146 529.501 13 2.180 196
F206 Esquizofrenia simples 9.630 187.658 13 1.728 188
F208 Outras esquizofrenias 83.419 1.639.458 13 3.326 204
F250 Transtorno esquizoafetivo do tipo maniaco 11.636 174.265 13 1.795 193
F251 Transtorno esquizoafetivo do tipo depressivo 10.096 156.441 13 1.705 195
F252 Transtorno esquizoafetivo do tipo misto 7.424 130.780 13 1.472 183
F311 Transtorno afetivo bipolar, episodio atual maniaco sem sintomas psicoticos 42.163 557.229 14 2.738 199
F312 Transtorno afetivo bipolar, episodio atual maniaco com sintomas psicoticos 25.595 351.081 14 2.634 204
Transtorno Afetivo Bipolar do tipo I
F313 Transtorno afetivo bipolar, episodio atual depressivo leve ou moderado 20.126 286.190 14 2.174 193
F314 Transtorno afetivo bipolar, episodio atual depressivo grave sem sintomas psicoticos 16.138 258.572 14 2.013 189
F315 Transtorno afetivo bipolar, episodio atual depressivo grave com sintomas psicoticos 14.424 227.720 14 2.124 194
F316 Transtorno afetivo bipolar, episodio atual misto 22.707 361.980 14 2.201 193
F317 Transtorno afetivo bipolar, atualmente em remissao 30.050 444.071 14 2.300 198
F840 Autismo infantil 22.209 293.047 4 2.243 201
F841 Autismo atipico 2.230 28.821 4 813 166
Transtorno Esquizoafetivo
F843 Outro transtorno desintegrativo da infancia 575 7.333 4 324 110
F845 Sindrome de Asperger 539 7.353 4 299 114
F848 Outros transtornos globais do desenvolvimento 2.757 37.789 4 880 158 
# sumario dos dados abertos do DATASUS
perfildoenca=pg_roda_query(
  "    SELECT 
          co_cidpri doença,
          round(nu_competencia/100) ano, 
          count(*) dispensações,
          count(distinct nu_usuariosus) usuários,
          count(distinct case when co_seq_cidpri_mes = 1 then nu_usuariosus else null end) usuários_primeiro_diagnostico
     FROM bd_sabeis.tf_psi
     where nu_competencia >= 201701
     group by 1,2;")

perfildoenca$dispensações=formata(perfildoenca$dispensações)
perfildoenca$usuários=formata(perfildoenca$usuários)
perfildoenca$usuários_primeiro_diagnostico=formata(perfildoenca$usuários_primeiro_diagnostico)

# formata os títulos
colnames(perfildoenca)=str_replace_all(colnames(perfildoenca),'_',' ')

A tabela 4.4 mostra o quantitativo de dispensações ao ano segundo o diagnóstico primário.

Table 4.4: Dispensações ao ano, segundo o diagnóstico primário, até 03/2022
2017 2018 2019 2020 2021 2022
F200 2.147.021 2.173.705 2.180.485 2.162.526 2.295.501 544.404
F201 87.292 91.060 95.159 92.064 98.397 24.160
F202 10.650 11.713 11.810 11.391 12.118 3.032
F203 81.692 81.486 83.099 78.959 80.622 19.145
F204 13.665 13.862 13.894 13.924 13.896 3.200
F205 92.996 92.386 92.829 86.983 87.566 21.596
F206 28.310 29.930 33.960 35.076 35.681 8.309
F208 289.859 288.142 275.117 271.150 283.264 65.510
F250 18.479 24.869 30.497 36.760 45.207 11.050
F251 15.041 21.568 29.261 34.017 40.366 9.738
F252 14.465 19.056 23.777 27.272 31.836 7.852
F311 30.101 63.032 96.247 137.054 179.787 46.852
F312 18.979 41.317 64.366 84.894 109.775 28.853
F313 13.938 29.999 50.130 72.121 93.134 24.884
F314 14.492 29.184 46.577 64.193 80.377 21.195
F315 13.977 27.369 42.235 55.541 68.186 17.882
F316 20.012 41.928 67.053 89.238 110.965 29.313
F317 19.978 45.541 80.280 110.008 146.250 38.879
F840 12.647 26.879 47.581 75.313 100.511 28.216
F841 1.353 2.861 5.168 7.452 9.252 2.492
F843 387 860 1.297 1.899 2.283 580
F845 542 972 1.283 1.806 2.160 530
F848 2.175 3.960 6.699 9.634 11.791 3.142

A tabela 4.5 mostra o quantitativo de usuários ao ano segundo o diagnóstico primário.

Table 4.5: Usuários atendidos ao ano, segundo o diagnóstico primário, até 03/2022
2017 2018 2019 2020 2021 2022
F200 240.679 251.281 251.975 244.017 248.330 204.204
F201 10.431 11.276 11.364 10.656 10.803 9.031
F202 1.413 1.588 1.493 1.387 1.391 1.134
F203 9.722 10.031 9.966 9.340 9.058 7.350
F204 1.754 1.806 1.834 1.636 1.614 1.216
F205 10.375 10.652 10.622 9.800 9.155 7.655
F206 3.716 4.077 4.677 4.483 4.342 3.326
F208 38.946 40.344 36.634 32.895 32.910 25.680
F250 2.944 3.879 4.609 4.978 5.842 4.476
F251 2.214 3.396 4.401 4.695 5.361 4.001
F252 1.994 2.716 3.227 3.419 3.834 3.010
F311 5.602 10.729 15.424 18.566 24.230 19.212
F312 3.593 6.954 9.960 11.558 14.260 11.235
F313 2.489 4.749 7.593 9.257 11.863 9.376
F314 2.327 4.389 6.445 7.593 9.474 7.629
F315 2.323 4.181 6.037 6.791 8.203 6.588
F316 3.382 6.378 9.267 10.801 13.166 10.505
F317 3.347 7.339 12.049 14.401 18.490 14.850
F840 2.659 5.193 8.643 10.795 15.042 12.722
F841 292 598 912 1.065 1.337 1.091
F843 86 157 230 287 330 255
F845 112 171 221 264 311 226
F848 446 712 1.174 1.345 1.685 1.379

A tabela 4.6 mostra o quantitativo novos usuários ao ano segundo o diagnóstico primário.

Table 4.6: Novos usários ao ano segundo o diagnóstico primário até 03/2022 . Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
F200 59.269 54.729 50.658 38.405 39.102 9.743
F201 3.352 3.321 3.079 1.953 2.016 526
F202 534 559 394 307 303 91
F203 2.967 2.674 2.501 1.861 1.652 412
F204 574 518 530 316 299 82
F205 2.645 2.583 2.384 1.693 1.275 316
F206 1.267 1.310 1.660 1.147 920 230
F208 13.867 12.261 9.815 6.929 6.870 1.798
F250 1.666 1.883 2.048 1.854 2.015 439
F251 1.100 1.810 2.007 1.616 1.762 377
F252 960 1.246 1.303 1.099 1.195 259
F311 4.377 6.853 8.440 8.049 10.096 2.789
F312 2.853 4.557 5.395 4.942 5.534 1.400
F313 2.011 3.001 4.300 4.016 4.830 1.371
F314 1.758 2.688 3.449 3.111 3.528 935
F315 1.768 2.527 3.147 2.664 2.945 698
F316 2.573 3.942 4.876 4.288 4.802 1.266
F317 2.566 4.880 6.674 5.864 7.173 1.966
F840 2.107 3.125 4.695 4.123 5.916 1.599
F841 223 389 503 434 482 116
F843 74 102 122 134 100 31
F845 98 88 111 107 91 25
F848 342 385 656 495 596 147

4.2.2 Utilização segundo o medicamento

A tabela 4.7 mostra o histórico de dispensações e usuários para as doenças elencadas segundo o diagnóstico primário.

# sumario dos dados abertos do DATASUS
perfilmedicamento=pg_roda_query(
  "  select B.no_procedimento procedimento,
         A.*
 from (
  select sg_procedimento,
         count(distinct nu_usuariosus) usuários,
         sum(qt_aprovada) qt_aprovada,
         round(sum(vl_aprovado)) vl_aprovado,
         sum(qt_aprovada)/count(distinct nu_usuariosus) as utilização_média,
         count(*) registros,
         count(distinct co_ibge_municipio_residencia) municípios,
         count(distinct co_cnes_estabelecimento) estabelecimentos,
         min(nu_competencia) nu_competencia_min,
         max(nu_competencia) nu_competencia_max,
         count(distinct co_cidpri) diagnósticos
   from bd_sabeis.tf_psi 
  group by 1
  ) A
  left join bd_geral.tf_sigtap B
  on A.sg_procedimento=B.sg_procedimento
  and A.nu_competencia_max=B.nu_competencia")
perfilmedicamento$nu_competencia_min=NULL
perfilmedicamento$nu_competencia_max=NULL
perfilmedicamento$registros=formata(perfilmedicamento$registros)
perfilmedicamento$usuários=formata(perfilmedicamento$usuários)
perfilmedicamento$municípios=formata(perfilmedicamento$municípios)
perfilmedicamento$qt_aprovada=formata(perfilmedicamento$qt_aprovada)

colnames(perfilmedicamento)=str_replace_all(colnames(perfilmedicamento),'_',' ')

kbl(perfilmedicamento,
    align = c("l",rep("r",ncol(perfilmedicamento)-1)),
    caption = "Perfil de utilização segundo o medicamento.") %>%
    kable_classic()
Table 4.7: Perfil de utilização segundo o medicamento.
procedimento sg procedimento usuários qt aprovada vl aprovado utilização média registros municípios estabelecimentos diagnósticos
CLOZAPINA 25 MG (POR COMPRIMIDO) CLOZ025 10.541 9.048.919 2520851 858 118.105 1.645 184 18
CLOZAPINA 100 MG (POR COMPRIMIDO) CLOZ100 49.704 169.329.707 197446990 3406 1.458.655 3.664 206 18
LAMOTRIGINA 25 MG (POR COMPRIMIDO) LAMO025 4.782 2.641.246 0 552 35.373 862 148 7
LAMOTRIGINA 50 MG (POR COMPRIMIDO) LAMO050 3.189 1.702.769 0 533 28.192 786 118 7
LAMOTRIGINA 100 MG (POR COMPRIMIDO) LAMO100 16.718 15.928.377 15485608 952 246.962 1.919 183 7
OLANZAPINA 5 MG (POR COMPRIMIDO) OLAN05 134.986 116.952.137 167477011 866 2.491.804 4.514 206 18
OLANZAPINA 10 MG (POR COMPRIMIDO) OLAN10 184.817 189.015.459 461554647 1022 4.344.931 4.931 208 18
QUETIAPINA 25 MG (POR COMPRIMIDO) QUET025 127.253 105.479.936 50934298 828 1.643.082 3.615 207 18
QUETIAPINA 100 MG (POR COMPRIMIDO) QUET100 160.937 162.802.993 191492611 1011 2.589.460 4.452 210 18
QUETIAPINA 200 MG (POR COMPRIMIDO) QUET200 92.741 123.442.996 299988034 1331 1.888.849 3.992 205 18
QUETIAPINA 300 MG (POR COMPRIMIDO) QUET300 17.746 15.558.989 77989450 876 352.704 1.483 156 18
RISPERIDONA 1 MG (POR COMPRIMIDO) RISP1 69.056 49.072.036 4097563 710 924.244 2.932 190 23
RISPERIDONA 2 MG (POR COMPRIMIDO) RISP2 136.011 146.929.575 13847446 1080 2.469.436 3.578 178 23
RISPERIDONA 3 MG (POR COMPRIMIDO) RISP3 22.837 13.670.578 2062938 598 284.241 1.593 123 23
RISPERIDONA 1,0 MG/ML SOLUÇÃO ORAL (POR FRASCO DE 30 ML) RISPSO1 13.313 372.464 7976790 27 146.158 1.952 186 5
ZIPRASIDONA 40 MG (POR CAPSULA) ZIPR40 11.587 11.321.953 9798718 977 188.491 1.566 184 11
ZIPRASIDONA 80 MG (POR CAPSULA) ZIPR80 15.206 18.518.261 28237220 1217 370.573 1.880 188 11

A tabela 4.8 mostra o histórico de dispensações segundo os usuários para as doenças elencadas segundo o diagnóstico primário.

# sumario dos dados abertos do DATASUS
periodomedicamento=pg_roda_query(
  "select A.sg_procedimento || ' ' || B.no_procedimento procedimento,
         diretriz, 
      usuários, 
      média_qt_aprovada, 
      desvio_qt_aprovada, 
      média_dispensações, 
      desvio_dispensações, 
      média_unidades_dispensadas, 
      desvio_unidades_dispensadas, 
      média_unidades_dia, 
      desvio_unidades_dia
 from (
select sg_procedimento,
       case
         when co_cidpri in ('F200', 'F201', 'F202', 'F203', 'F204', 'F205', 'F206', 'F208') then 'Esquizofrenia'
         when co_cidpri in ('F250', 'F251', 'F252') then 'Transtorno Esquizoafetivo'
         when co_cidpri in ('F311', 'F312', 'F313', 'F314', 'F315', 'F316', 'F317') then 'Transtorno Afetivo Bipolar do tipo I'
         when co_cidpri in ('F840', 'F841', 'F843', 'F845', 'F848') then 'Comportamento Agressivo no Autismo'
         else null end as diretriz,
       count(distinct nu_usuariosus) usuários,
       round(avg(qt_aprovada)) média_qt_aprovada,
       round(stddev(qt_aprovada)) desvio_qt_aprovada,
       round(avg(dispensações)) média_dispensações,
       round(stddev(dispensações)) desvio_dispensações,
       round(avg(qt_aprovada/dispensações)) média_unidades_dispensadas,
       round(stddev(qt_aprovada/dispensações)) desvio_unidades_dispensadas,
       round(avg(qt_aprovada/dias),1) média_unidades_dia,
       round(stddev(qt_aprovada/dias),1) desvio_unidades_dia
from (
select nu_usuariosus ,
       sg_procedimento,
       co_cidpri,
       sum(qt_aprovada) qt_aprovada,
       count(*) dispensações,
       (date_trunc('month', TO_DATE(max(nu_competencia)::text || '15', 'YYYYMMDD')) + interval '1 month' - interval '1 day')::date-
       TO_DATE(min(nu_competencia)::text || '01', 'YYYYMMDD') dias
  from bd_sabeis.tf_psi
 group by 1,2,3
  ) A
  group by 1,2
  ) A
   left join bd_geral.tf_sigtap B
  on A.sg_procedimento=B.sg_procedimento
  and B.nu_competencia=202204
  order by 1,2")

periodomedicamento$usuários=formata(periodomedicamento$usuários)
periodomedicamento$média_qt_aprovada=formata(periodomedicamento$média_qt_aprovada)
periodomedicamento$desvio_qt_aprovada=formata(periodomedicamento$desvio_qt_aprovada)
x1=periodomedicamento$procedimento
periodomedicamento$procedimento=NULL
colnames(periodomedicamento)=str_replace_all(colnames(periodomedicamento),'_',' ')

kbl(periodomedicamento,
    align = c("l",rep("r",ncol(periodomedicamento)-1)),
    caption = "Perfil de utilização por usuário do SUS segundo o medicamento.") %>%
    kable_classic()%>% 
  pack_rows(index = table(x1))
Table 4.8: Perfil de utilização por usuário do SUS segundo o medicamento.
diretriz usuários média qt aprovada desvio qt aprovada média dispensações desvio dispensações média unidades dispensadas desvio unidades dispensadas média unidades dia desvio unidades dia
CLOZ025 CLOZAPINA 25 MG (POR COMPRIMIDO)
Esquizofrenia 9.551 842 1.248 11 14 89 86 2.0 2.8
Transtorno Afetivo Bipolar do tipo I 706 565 790 8 10 71 49 1.6 1.7
Transtorno Esquizoafetivo 416 594 859 8 11 79 71 1.9 2.5
CLOZ100 CLOZAPINA 100 MG (POR COMPRIMIDO)
Esquizofrenia 46.838 3.239 3.322 28 22 111 54 2.5 1.8
Transtorno Afetivo Bipolar do tipo I 2.264 1.031 1.367 12 12 85 49 1.9 1.6
Transtorno Esquizoafetivo 1.526 1.544 1.901 14 14 104 53 2.5 1.8
LAMO025 LAMOTRIGINA 25 MG (POR COMPRIMIDO)
Transtorno Afetivo Bipolar do tipo I 4.782 519 734 7 9 79 49 1.8 1.7
LAMO050 LAMOTRIGINA 50 MG (POR COMPRIMIDO)
Transtorno Afetivo Bipolar do tipo I 3.189 502 643 8 9 63 40 1.2 1.3
LAMO100 LAMOTRIGINA 100 MG (POR COMPRIMIDO)
Transtorno Afetivo Bipolar do tipo I 16.718 872 992 14 13 62 25 1.2 0.9
OLAN05 OLANZAPINA 5 MG (POR COMPRIMIDO)
Esquizofrenia 115.952 857 1.024 18 19 51 28 0.8 1.0
Transtorno Afetivo Bipolar do tipo I 19.037 413 515 9 10 46 25 0.8 1.0
Transtorno Esquizoafetivo 4.140 537 689 11 13 51 29 0.9 1.1
OLAN10 OLANZAPINA 10 MG (POR COMPRIMIDO)
Esquizofrenia 162.956 985 976 23 20 44 17 0.6 0.7
Transtorno Afetivo Bipolar do tipo I 22.667 443 525 10 11 42 16 0.7 0.7
Transtorno Esquizoafetivo 6.037 581 665 13 13 46 18 0.7 0.7
QUET025 QUETIAPINA 25 MG (POR COMPRIMIDO)
Esquizofrenia 95.099 839 1.154 13 14 64 40 1.2 1.3
Transtorno Afetivo Bipolar do tipo I 31.082 502 687 8 9 60 37 1.2 1.3
Transtorno Esquizoafetivo 5.228 645 896 11 12 59 35 1.2 1.2
QUET100 QUETIAPINA 100 MG (POR COMPRIMIDO)
Esquizofrenia 107.983 999 1.408 16 16 62 39 1.1 1.3
Transtorno Afetivo Bipolar do tipo I 54.986 674 910 11 11 62 38 1.2 1.3
Transtorno Esquizoafetivo 6.424 848 1.164 13 14 64 42 1.3 1.4
QUET200 QUETIAPINA 200 MG (POR COMPRIMIDO)
Esquizofrenia 64.437 1.373 1.652 20 20 63 30 1.2 1.0
Transtorno Afetivo Bipolar do tipo I 30.386 702 893 12 13 55 27 1.1 1.0
Transtorno Esquizoafetivo 3.725 949 1.207 15 15 63 30 1.3 1.1
QUET300 QUETIAPINA 300 MG (POR COMPRIMIDO)
Esquizofrenia 12.785 927 1.016 20 20 44 14 0.6 0.6
Transtorno Afetivo Bipolar do tipo I 5.315 443 526 11 12 38 12 0.5 0.6
Transtorno Esquizoafetivo 599 673 812 16 17 42 14 0.7 0.7
RISP1 RISPERIDONA 1 MG (POR COMPRIMIDO)
Comportamento Agressivo no Autismo 9.694 668 833 11 11 56 31 1.0 1.0
Esquizofrenia 50.870 716 1.001 13 15 55 35 1.0 1.2
Transtorno Afetivo Bipolar do tipo I 8.377 390 511 8 9 47 28 0.9 1.0
Transtorno Esquizoafetivo 1.902 510 657 10 12 52 33 0.9 1.2
RISP2 RISPERIDONA 2 MG (POR COMPRIMIDO)
Comportamento Agressivo no Autismo 7.173 776 891 13 12 57 25 1.0 0.9
Esquizofrenia 112.524 1.102 1.293 18 18 57 24 1.0 0.9
Transtorno Afetivo Bipolar do tipo I 16.422 477 626 9 10 49 23 0.9 0.9
Transtorno Esquizoafetivo 3.795 647 826 12 13 54 24 1.0 0.9
RISP3 RISPERIDONA 3 MG (POR COMPRIMIDO)
Comportamento Agressivo no Autismo 1.653 604 678 12 12 49 19 0.8 0.8
Esquizofrenia 17.799 592 698 12 13 47 16 0.8 0.7
Transtorno Afetivo Bipolar do tipo I 3.130 363 454 8 9 42 16 0.7 0.7
Transtorno Esquizoafetivo 916 474 583 10 11 45 16 0.7 0.7
RISPSO1 RISPERIDONA 1,0 MG/ML SOLUÇÃO ORAL (POR FRASCO DE 30 ML)
Comportamento Agressivo no Autismo 13.313 28 36 11 10 2 1 0.0 0.0
ZIPR40 ZIPRASIDONA 40 MG (POR CAPSULA)
Esquizofrenia 10.816 946 1.252 16 18 63 29 1.2 1.1
Transtorno Esquizoafetivo 883 603 852 10 12 58 27 1.3 1.0
ZIPR80 ZIPRASIDONA 80 MG (POR CAPSULA)
Esquizofrenia 14.517 1.152 1.160 23 22 49 13 0.8 0.6
Transtorno Esquizoafetivo 853 594 719 12 14 46 14 0.9 0.6 
# sumario dos dados abertos do DATASUS
perfilmedic=pg_roda_query(
  "SELECT 
          sg_procedimento medicamento,
          round(nu_competencia/100) ano, 
          count(*) dispensações,
          count(distinct nu_usuariosus) usuários,
          sum(qt_aprovada) as unidades,
          sum(vl_aprovado) as valor,
          count(distinct case when co_seq_medicamento_mes = 1 
          then nu_usuariosus else null end) usuários_primeiro_diagnostico
     FROM bd_sabeis.tf_psi
     where nu_competencia >= 201701
     group by 1,2;")

perfilmedic$dispensações=formata(perfilmedic$dispensações)
perfilmedic$usuários=formata(perfilmedic$usuários)
perfilmedic$unidades=formata(perfilmedic$unidades)
perfilmedic$valor=formata(perfilmedic$valor)
perfilmedic$usuários_primeiro_diagnostico=formata(perfilmedic$usuários_primeiro_diagnostico)

# formata os títulos
colnames(perfilmedic)=str_replace_all(colnames(perfilmedic),'_',' ')

A tabela 4.9 mostra o quantitativo de dispensações ao ano segundo o medicamento

Table 4.9: Dispensações ao ano segundo o medicamento até 03/2022
2017 2018 2019 2020 2021 2022
CLOZ025 15.910 17.991 20.100 23.610 26.032 7.462
CLOZ100 209.752 241.849 255.596 278.390 279.432 75.964
LAMO025 2.074 4.406 6.713 8.473 10.644 2.736
LAMO050 1.798 3.210 4.041 7.012 9.247 2.737
LAMO100 13.607 27.068 44.874 61.223 76.353 21.547
OLAN05 403.548 424.233 440.636 415.505 466.397 111.335
OLAN10 704.774 736.792 797.220 664.702 829.869 210.055
QUET025 239.981 270.793 268.134 337.203 333.688 65.974
QUET100 345.015 404.902 423.565 525.157 578.359 135.739
QUET200 268.650 294.848 329.621 366.615 388.097 97.515
QUET300 53.742 56.265 58.663 63.728 68.848 17.077
RISP1 138.934 144.950 161.160 169.181 182.078 45.962
RISP2 412.602 378.077 415.267 443.089 470.842 110.533
RISP3 34.868 42.050 42.492 59.955 72.040 17.577
RISPSO1 3.497 10.369 20.817 40.092 55.441 15.808
ZIPR40 32.506 33.778 30.490 32.883 32.778 8.182
ZIPR80 66.793 70.098 59.415 62.457 58.780 14.611

A tabela 4.10 mostra o quantitativo de usuários ao ano segundo o medicamento.

Table 4.10: Usuários atendidos ao ano segundo o medicamento até 03/2022
2017 2018 2019 2020 2021 2022
CLOZ025 2.928 3.362 3.682 3.742 4.421 3.335
CLOZ100 25.612 28.219 30.564 32.216 33.943 30.657
LAMO025 582 1.116 1.645 1.726 2.076 1.280
LAMO050 480 797 1.108 1.244 1.605 1.222
LAMO100 2.734 4.715 7.136 8.611 10.869 9.120
OLAN05 57.759 62.932 63.833 64.405 62.586 47.610
OLAN10 90.834 99.132 103.827 101.479 102.361 87.088
QUET025 38.458 44.501 48.873 50.393 55.966 37.557
QUET100 53.747 64.066 71.589 73.229 81.256 63.021
QUET200 37.042 41.894 46.811 46.893 49.774 41.657
QUET300 7.691 7.527 8.055 7.817 8.600 7.274
RISP1 24.082 25.468 28.153 25.485 28.180 20.249
RISP2 60.346 61.295 62.300 61.262 64.814 49.032
RISP3 6.060 8.082 9.505 9.349 10.925 8.161
RISPSO1 920 2.312 4.410 6.141 8.980 7.450
ZIPR40 5.239 4.838 4.833 4.353 4.374 3.414
ZIPR80 8.935 8.412 7.889 7.029 6.737 5.651

A tabela 4.11 mostra o quantitativo novos usuários ao ano segundo o medicamento.

Table 4.11: Novos usários ao ano segundo o medicamento até 03/2022. Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
CLOZ025 1.551 1.647 1.682 1.480 1.727 484
CLOZ100 5.051 5.339 5.396 5.160 5.102 1.426
LAMO025 493 813 1.074 927 1.066 257
LAMO050 420 528 596 628 754 186
LAMO100 2.011 2.646 3.468 3.100 3.717 928
OLAN05 18.828 19.645 17.304 15.397 13.334 3.130
OLAN10 23.346 23.047 22.505 15.913 17.944 4.418
QUET025 16.909 18.725 19.907 17.765 19.349 5.917
QUET100 22.304 24.753 25.128 21.337 22.827 5.491
QUET200 12.423 12.763 14.205 10.767 10.670 3.089
QUET300 2.155 1.911 2.283 1.892 2.249 527
RISP1 9.837 9.910 11.352 7.556 9.178 2.001
RISP2 19.016 16.229 17.383 15.000 14.514 3.472
RISP3 3.140 3.914 3.983 3.408 3.763 780
RISPSO1 851 1.640 2.757 2.864 4.005 1.113
ZIPR40 1.871 1.365 1.282 1.064 899 222
ZIPR80 2.029 1.510 1.275 982 760 168

4.2.3 Novos usuários ao ano por medicamento e diretriz

# sumario dos dados abertos do DATASUS
perfiltrat=pg_roda_query(
  "SELECT 
          B.no_diretriz50 diretriz,
          sg_procedimento as medicamento,
          round(nu_competencia/100) ano, 
          count(*) dispensações,
          sum(qt_aprovada) as unidades,
          round(sum(vl_aprovado)) as valor,
          count(distinct nu_usuariosus) usuários,
          count(distinct case when co_seq_cidpri_mes = 1 then nu_usuariosus else null end) usuários_primeiro_diagnostico
     FROM bd_sabeis.tf_psi A
     left join bd_sabeis.vw_pcdt_atualizado_03_2022 B
   on B.co_cid = A.co_cidpri
     where nu_competencia >= 201701
     group by 1,2,3
     order by 1,2,3;")

perfiltrat$dispensações=formata(perfiltrat$dispensações)
perfiltrat$usuários=formata(perfiltrat$usuários)
perfiltrat$unidades=formata(perfiltrat$unidades)
perfiltrat$valor=formata(perfiltrat$valor)
perfiltrat$usuários_primeiro_diagnostico=formata(perfiltrat$usuários_primeiro_diagnostico)

# formata os títulos
colnames(perfiltrat)=str_replace_all(colnames(perfiltrat),'_',' ')
Table 4.12: Novos usários com Comportamento Agressivo no Autismo, ao ano, segundo o medicamento até 03/2022. Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
RISP1 1.103 1.451 1.945 1.345 1.811 465
RISP2 830 869 1.211 996 1.189 319
RISP3 123 224 270 194 275 61
RISPSO1 834 1.604 2.702 2.840 3.983 1.098

A tabela 4.12 mostra o quantitativo novos usuários ao ano segundo o medicamento e sob a diretriz de Comportamento Agressivo no Autismo.

Table 4.13: Novos usários com Esquizofrenia, ao ano, segundo o medicamento até 03/2022. Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
CLOZ025 581 672 587 537 599 151
CLOZ100 3.988 3.873 3.729 3.240 3.088 866
OLAN05 10.978 9.945 8.685 6.335 6.414 1.498
OLAN10 18.063 17.246 15.614 10.301 10.682 2.534
QUET025 13.147 13.223 12.253 10.165 10.258 3.179
QUET100 12.393 12.131 10.689 7.833 7.846 1.763
QUET200 6.077 5.634 4.929 3.515 3.052 802
QUET300 859 557 515 513 467 99
RISP1 6.161 4.992 4.816 3.305 3.640 817
RISP2 14.227 11.299 10.652 8.452 7.970 1.849
RISP3 1.759 1.827 1.785 1.474 1.565 329
ZIPR40 928 707 655 518 419 113
ZIPR80 1.291 1.031 784 550 380 84

A tabela 4.13 mostra o quantitativo novos usuários ao ano segundo o medicamento e sob a diretriz de Esquizofrenia.

Table 4.14: Novos usários com Transtorno Afetivo Bipolar do tipo I, ao ano, segundo o medicamento até 03/2022. Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
CLOZ025 38 70 89 102 121 36
CLOZ100 164 280 327 372 478 141
LAMO025 388 615 791 684 770 183
LAMO050 281 364 350 425 552 139
LAMO100 1.722 2.218 2.846 2.611 3.135 841
OLAN05 1.726 2.772 3.479 2.856 3.551 943
OLAN10 2.451 3.596 4.497 3.557 4.353 1.221
QUET025 2.253 4.255 5.687 6.147 7.449 2.143
QUET100 5.265 8.628 10.559 9.971 11.333 2.921
QUET200 2.812 4.382 5.373 4.873 5.336 1.465
QUET300 440 557 672 667 909 245
RISP1 688 1.058 1.537 1.314 1.782 391
RISP2 1.735 2.217 3.048 3.099 3.334 832
RISP3 221 444 553 492 612 128

A tabela 4.14 mostra o quantitativo novos usuários ao ano segundo o medicamento e sob a diretriz de Transtorno Afetivo Bipolar do tipo I.

Table 4.15: Novos usários com Transtorno Esquizoafetivo, ao ano, segundo o medicamento até 03/2022. Usuários com primeiro registro em 2016 a partir de 06/2016 foram desconsiderados.
2017 2018 2019 2020 2021 2022
CLOZ025 35 54 46 47 62 17
CLOZ100 164 253 212 213 259 69
OLAN05 443 596 653 449 552 108
OLAN10 748 960 1.071 773 838 188
QUET025 492 777 896 913 1.022 205
QUET100 748 923 918 818 881 218
QUET200 379 520 513 444 425 90
QUET300 57 65 40 55 67 24
RISP1 227 278 282 245 305 50
RISP2 493 539 672 504 563 107
RISP3 96 139 142 143 138 22
ZIPR40 69 108 139 170 179 34
ZIPR80 84 117 136 118 102 26

A tabela 4.15 mostra o quantitativo novos usuários ao ano segundo o medicamento e sob a diretriz de Transtorno Esquizoafetivo.

4.2.4 Unidades dispensadas por medicamento segundo a diretriz, ao ano

As quantidades aprovadas, em unidades farmacotécnicas, são mostradas a seguir.

Table 4.16: Quantidade aprovada para Comportamento Agressivo no Autismo, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
RISP1 394.308 770.429 1.225.941 1.612.192 1.983.534 539.484
RISP2 354.474 598.427 982.165 1.381.569 1.834.605 486.027
RISP3 30.842 90.871 168.881 280.051 366.158 90.084
RISPSO1 9.353 26.685 53.019 101.224 141.514 40.369

A tabela 4.16 mostra unidades dispensadas ao ano segundo o medicamento e sob a diretriz de Comportamento Agressivo no Autismo.

Table 4.17: Quantidade aprovada para Esquizofrenia, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 1.201.057 1.280.938 1.465.544 1.592.175 1.748.819 509.745
CLOZ100 24.198.860 27.448.630 28.944.575 31.175.764 30.765.598 8.272.090
OLAN05 17.404.976 18.224.305 18.812.350 17.792.824 19.472.330 4.481.539
OLAN10 28.032.455 29.467.288 32.109.972 26.853.810 33.586.326 8.444.212
QUET025 14.518.061 15.208.803 13.704.523 16.482.031 14.692.319 2.720.267
QUET100 19.016.743 19.998.904 18.622.243 21.326.820 21.487.357 4.780.217
QUET200 16.400.664 16.228.253 16.355.232 16.921.163 16.598.750 3.958.539
QUET300 2.241.037 2.162.003 2.100.456 2.078.918 2.035.819 465.212
RISP1 6.593.596 6.387.659 6.627.508 6.427.440 6.386.439 1.513.215
RISP2 23.261.716 20.614.111 21.844.243 22.462.923 23.203.199 5.158.200
RISP3 1.544.464 1.741.054 1.602.759 2.194.491 2.584.286 627.810
ZIPR40 1.923.215 1.927.049 1.754.641 1.870.804 1.790.345 444.637
ZIPR80 3.289.196 3.428.212 2.884.805 3.027.896 2.787.279 686.770

A tabela 4.17 mostra unidades dispensadas ao ano segundo o medicamento e sob a diretriz de Esquizofrenia.

Table 4.18: Quantidade aprovada para Transtorno Afetivo Bipolar do tipo I, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 18.247 44.610 62.207 110.286 141.206 43.487
CLOZ100 113.620 248.508 426.228 598.884 803.233 253.709
LAMO025 151.992 334.960 514.557 638.349 779.694 195.447
LAMO050 106.383 191.769 264.449 418.631 555.716 157.916
LAMO100 837.870 1.693.969 2.845.829 3.970.654 5.036.154 1.406.035
OLAN05 454.829 1.007.730 1.613.330 1.943.102 2.738.773 713.775
OLAN10 616.523 1.275.449 2.193.263 2.311.065 3.531.121 992.132
QUET025 745.189 1.792.218 2.864.202 4.556.942 5.453.797 1.150.081
QUET100 2.137.012 4.604.825 7.020.004 10.569.025 13.346.782 3.410.593
QUET200 1.207.951 2.624.409 4.334.319 6.052.570 7.497.889 2.029.079
QUET300 132.857 282.032 429.874 626.437 826.899 232.202
RISP1 159.100 365.571 640.625 826.704 1.077.610 284.786
RISP2 479.929 828.875 1.424.547 2.039.097 2.694.699 706.362
RISP3 49.205 124.080 193.305 302.602 415.821 104.756

A tabela 4.18 mostra o o unidades em reais ao ano segundo o medicamento e sob a diretriz de Transtorno Afetivo Bipolar do tipo I.

Table 4.19: Quantidade aprovada para Transtorno Esquizoafetivo, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 19.754 33.424 40.668 54.460 76.358 21.791
CLOZ100 191.429 336.152 446.619 570.462 700.101 195.348
OLAN05 218.130 329.396 452.763 469.278 601.700 145.959
OLAN10 377.994 528.991 735.479 699.122 953.004 246.567
QUET025 332.692 447.606 583.181 803.111 929.106 182.998
QUET100 652.996 836.323 945.577 1.204.273 1.374.260 325.759
QUET200 404.767 529.046 672.051 801.418 884.369 216.314
QUET300 45.786 63.862 69.057 83.818 99.460 27.420
RISP1 98.896 137.716 181.938 203.197 259.506 63.754
RISP2 286.122 320.164 441.368 537.757 658.477 154.090
RISP3 29.134 53.265 74.575 110.345 142.302 31.086
ZIPR40 34.536 65.802 85.072 134.841 168.971 43.269
ZIPR80 42.773 67.317 87.318 122.555 142.628 37.233

A tabela 4.19 mostra unidades dispensadas ao ano segundo o medicamento e sob a diretriz de Transtorno Esquizoafetivo.

4.2.5 Valor gasto por medicamento segundo a diretriz, ao ano

Estimou-se o valor gasto utilizando-se os valores coletados do banco de preços em saúde e multiplicados pela respectiva quantidade aprovada.

Table 4.20: Valor em reais estimados para Comportamento Agressivo no Autismo, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
RISP1 11.829 76.897 122.594 161.219 198.353 53.948
RISP2 17.724 65.732 108.038 151.973 201.807 53.463
RISP3 1.851 15.448 28.710 47.609 62.247 15.314
RISPSO1 202.492 571.345 1.135.137 2.167.206 3.029.815 864.300

A tabela 4.20 mostra o valor em reais ao ano segundo o medicamento e sob a diretriz de Comportamento Agressivo no Autismo.

Table 4.21: Valor em reais estimados para Esquizofrenia, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 337.969 345.853 395.697 429.887 492.742 163.118
CLOZ100 29.772.639 32.428.518 33.373.858 34.916.856 34.666.075 9.512.904
OLAN05 31.851.106 31.401.810 29.528.026 22.294.144 16.162.034 3.719.677
OLAN10 79.892.497 76.991.159 80.818.948 63.747.518 63.478.156 15.959.561
QUET025 7.985.306 7.563.293 6.472.849 7.334.190 6.464.620 1.196.917
QUET100 25.302.816 25.715.787 22.551.938 22.606.429 22.776.598 5.067.030
QUET200 48.652.006 45.207.590 40.881.039 33.715.256 33.031.513 7.877.493
QUET300 25.413.360 22.689.348 1.637.470 1.526.143 1.425.073 325.648
RISP1 197.808 638.282 662.751 642.744 638.644 151.322
RISP2 1.163.086 2.266.428 2.402.867 2.470.922 2.552.352 567.402
RISP3 92.668 295.855 272.469 373.063 439.329 106.728
ZIPR40 4.901.480 0 0 0 0 0
ZIPR80 13.604.311 0 0 0 0 0

A tabela 4.21 mostra o valor em reais ao ano segundo o medicamento e sob a diretriz de Esquizofrenia.

Table 4.22: Valor em reais estimados para Transtorno Afetivo Bipolar do tipo I, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 5.088 12.045 16.796 29.777 39.779 13.916
CLOZ100 138.923 293.268 490.567 670.750 905.496 291.765
LAMO025 0 0 0 0 0 0
LAMO050 0 0 0 0 0 0
LAMO100 0 901.068 3.130.412 4.367.719 5.539.769 1.546.639
OLAN05 832.337 1.729.126 2.502.141 2.422.356 2.273.182 592.433
OLAN10 1.757.091 3.311.633 5.517.872 5.453.226 6.673.819 1.875.129
QUET025 402.673 886.069 1.350.478 2.024.835 2.399.671 506.036
QUET100 2.826.983 5.918.000 8.440.188 11.203.167 14.147.589 3.615.229
QUET200 3.543.177 7.297.551 10.681.019 12.058.123 14.920.799 4.037.867
QUET300 1.506.598 2.895.553 334.439 458.670 578.829 162.541
RISP1 4.773 36.533 64.063 82.670 107.761 28.479
RISP2 23.996 91.137 156.700 224.301 296.417 77.700
RISP3 2.952 21.077 32.862 51.442 70.690 17.809

A tabela 4.22 mostra o valor em reais ao ano segundo o medicamento e sob a diretriz de Transtorno Afetivo Bipolar do tipo I.

Table 4.23: Valor em reais estimados para Transtorno Esquizoafetivo, ao ano, segundo o medicamento até 03/2022.
2017 2018 2019 2020 2021 2022
CLOZ025 5.559 9.024 10.980 14.704 21.542 6.973
CLOZ100 234.994 396.829 514.582 638.917 789.129 224.650
OLAN05 399.178 565.838 706.159 585.695 499.411 121.146
OLAN10 1.077.283 1.378.566 1.850.858 1.654.146 1.801.178 466.012
QUET025 182.384 222.031 275.251 356.939 408.807 80.519
QUET100 867.263 1.075.261 1.144.924 1.276.529 1.456.716 345.305
QUET200 1.199.376 1.472.323 1.669.092 1.596.717 1.759.894 430.465
QUET300 519.213 669.346 53.770 61.407 69.622 19.194
RISP1 2.967 13.761 18.194 20.320 25.951 6.375
RISP2 14.306 35.189 48.550 59.153 72.432 16.950
RISP3 1.748 9.055 12.678 18.759 24.191 5.285
ZIPR40 75.132 0 0 0 0 0
ZIPR80 160.960 0 0 0 0 0

A tabela 4.23 mostra o valor em reais ao ano segundo o medicamento e sob a diretriz de Transtorno Esquizoafetivo.

5 Considerações

Bases consolidadas a partir de dados administrativos devem ser validadas com padrões amplamente aceitos. A comparação da quantidade aprovada ao mês com os dados disponíveis do tabnet se mostrou como o mais importante controle de qualidade da extração, transformação e carga (ETL), por se tratar do insumo mais amplamente utilizado e amplamente conhecidos na gestão do SUS.

A avaliação com técnicas iterativas de extração da série histórica de usuários, quantidade aprovada e do produto do valor pago pela quantidade aprovada contribui para o conhecimento de cada procedimento do SUS e, consequentemente para o cálculo do impacto orçamentário.

As variações observadas foram consideradas baixas e adequadas para prosseguimento de estudos específicos. Os quantitativos finais dependem dos respectivos critérios de inclusão e exclusão de cada estudo, sendo a base de dados constituída com a ferramenta sabeis adequada para Avaliação de Tecnologias de Saúde.

Alguns valores não foram coletados resultando zerados na tabela. Recomenda-se ao Ministério da Saúde disponibilizar os valores do Banco de Preços em Saúde para coleta por máquina, de modo a viabilizar a aplicação automática dos valores.

6 References

Ferré, Felipe. 2021. “Coleção Covid-19 - Reflexões e Futuro.” In, edited by Alethele de Oliveira Santos and Luciana Toledo Lopes, 114–27. Conass. https://bit.ly/35D0k0j.
Ferré, Felipe, Gustavo de Oliveira, Mariana Queiroz, and Flávio Gonçalves. 2020. “Sala de Situação Aberta Com Dados Administrativos Para Gestão de Protocolos Clínicos e Diretrizes Terapêuticas de Tecnologias Providas Pelo SUS.” SBC.
Lima, Gabriela Vasconcelos and Feitosa, Gustavo Raposo Pereira. 2015. “POLÍTICAS PÚBLICAS PARA o SOFTWARE LIVRE, PRÁTICAS COLABORATIVAS e OS PRINCÍPIOS DA ADMINISTRAÇÃO PÚBLICA.” Publica Direito.

Appendix

função bash dbc2csv()

# localiza arquivo dbf2dbc.exe
exe=$(locate Tab415/dbf2dbc.exe | grep Dropbox | head -1)

# data atual
curdt=$(date '+%Y%m%d')

dbc2csv () {
      origem=$1
      exe=$2
      curdt=$3
      cmp=$4
      
      cd /tmp/

      # baixa o arquivo dbc      
      wget --ignore-case --no-clobber ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/${origem}.dbc
      fdbc=$(ls | grep -i $origem | grep -i dbc | head -1)      
      
      # descompacta dbc para dbf
      wine $exe $fdbc

      # identifica o arquivo dbf
      fdbf=$(ls | grep -i $origem | grep -i dbf | head -1)
      fstr=$fdbf.str

      # transforma
      if [ -f "$fdbf" ]; then

      # estrutura do dbf
      dbview -e -o $fdbf | 
      awk -F'\t' '{ gsub(/[ \t]+$/,"",$1);gsub(" ","_",$1); print tolower($1)","$2","$3}' | 
      sed 1d | sed 's/ //g' | nl | awk '{print $1","$2}' > $fstr

      # numero de colunas 
      lcol=$(tail -1 $fstr | awk -F',' '{print $1+1}')

      # posicao das colunas
      nu_apac=$(cat $fstr | egrep -i ",ap_autoriz,|,autoriz,|,nu_autorizacao,|,pa_autoriz,|,pa_numapa,|,ap_autoriz," | head -1 | awk -F','  '{print $1}');
      nu_apac_anterior=$(cat $fstr | egrep -i "apacant," | head -1 | awk -F','  '{print $1}')
      nu_cnspcn=$(cat $fstr | egrep -i ",ap_cnspcn,|,cns_pac,|,nu_cnspcn," | head -1 | awk -F','  '{print $1}')
      co_procedimento=$(cat $fstr | egrep -i "ap_pripal,|,co_procede,|,pa_proc_id,|,proc_id,|,proc_rea,|,sp_procrea,|,pa_codpro," | head -1 | awk -F','  '{print $1}')
      co_gestao=$(cat $fstr | egrep -i ",ap_gestao,|,gestao,|,pa_gestao,|,uf_zi,|,sp_gestor," | head -1 | awk -F','  '{print $1}')
      co_ibge_municipio_residencia=$(cat $fstr | egrep -i ",ap_munpcn,|,codmunres,|,co_munres,|,munic_res,|,munpac,|,mun_res,|,pa_munpcn,|,ufmunres," | head -1 | awk -F','  '{print $1}')
      co_cep_residencia=$(cat $fstr | egrep -i ",cep,|,ap_ceppcn," | head -1 | awk -F','  '{print $1}')
      co_carater_atendimento=$(cat $fstr | egrep -i "catend," | head -1 | awk -F','  '{print $1}')
      sg_sexo=$(cat $fstr | egrep -i "ap_sexo,|,co_sexo,|,cs_sexo,|,pa_sexo,|,sexo,|,sexopac," | head -1 | awk -F','  '{print $1}')
      nu_idade=$(cat $fstr | egrep -i "ap_nuidade,|,idade,|,idadepac,|,nu_idade_n,|,pa_idade," | head -1 | awk -F','  '{print $1}')
      co_motivo_saida=$(cat $fstr | egrep -i "motsai," | head -1 | awk -F','  '{print $1}')
      co_ibge_municipio_evento=$(cat $fstr | egrep -i ",ap_ufmun,|,codufmun,|,id_municip,|,munic_mov,|,pa_ufmun,|,ufmun,|,codmunocor,|,pa_munat," | head -1 | awk -F','  '{print $1}')
      dt_inicio=$(cat $fstr | egrep -i ",ap_dtinic,|,dt_inter,|,sp_dtinter,|,ap_dtocor,|,dt_atend,|,inicio,|,ant_dt_aci,|,dt_inter," | head -1 | awk -F','  '{print $1}')
      dt_fim=$(cat $fstr | egrep -i ",dt_saida,|,sp_dtsaida,|,ap_dtfim,|,fim," | head -1 | awk -F','  '{print $1}')
      co_cnes_estabelecimento=$(cat $fstr | egrep -i ",ap_coduni,|,cnes,|,cnes_exec,|,co_cnescon,|,coduni,|,id_unidade,|,pa_coduni,|,sp_cnes,|,sp_cnes," | head -1 | awk -F','  '{print $1}')
      co_cidpri=$(cat $fstr | egrep -i ",ap_cidpri,|,causabas,|,cidpri,|,diag_princ,|,pa_cidpri,|,sp_cidpri," | head -1 | awk -F','  '{print $1}')
      co_cidsec=$(cat $fstr | egrep -i ",ap_cidsec,|,diag_sec,|,diagsec1,|,diagsec2,|,diagsec3,|,diagsec4,|,diagsec5,|,diagsec6,|,diagsec7,|,diagsec8,|,diagsec9,|,diag_secun,|cidsec,|,sp_cidsec," | head -1 | awk -F','  '{print $1}')
      qt_aprovada=$(cat $fstr | egrep -i ",pa_qtdapr,|,qt_aprov," | head -1 | awk -F','  '{print $1}')
      vl_aprovado=$(cat $fstr | egrep -i ",pa_valapr,|,qt_proced,|,val_tot,|,vl_aprov," | head -1 | awk -F','  '{print $1}')
      pa_condic=$(cat $fstr | egrep -i ",pa_condic,|,ap_condic,|,condic," | head -1 | awk -F','  '{print $1}')

      if [ "$nu_apac" = "" ]; then nu_apac=$lcol; fi
      if [ "$nu_apac_anterior" = "" ]; then nu_apac_anterior=$lcol; fi
      if [ "$nu_cnspcn" = "" ]; then nu_cnspcn=$lcol; fi
      if [ "$co_procedimento" = "" ]; then co_procedimento=$lcol; fi
      if [ "$co_gestao" = "" ]; then co_gestao=$lcol; fi
      if [ "$co_ibge_municipio_residencia" = "" ]; then co_ibge_municipio_residencia=$lcol; fi
      if [ "$co_cep_residencia" = "" ]; then co_cep_residencia=$lcol; fi
      if [ "$co_carater_atendimento" = "" ]; then co_carater_atendimento=$lcol; fi
      if [ "$sg_sexo" = "" ]; then sg_sexo=$lcol; fi
      if [ "$nu_idade" = "" ]; then nu_idade=$lcol; fi
      if [ "$co_motivo_saida" = "" ]; then co_motivo_saida=$lcol; fi
      if [ "$co_ibge_municipio_evento" = "" ]; then co_ibge_municipio_evento=$lcol; fi
      if [ "$dt_inicio" = "" ]; then dt_inicio=$lcol; fi
      if [ "$dt_fim" = "" ]; then dt_fim=$lcol; fi
      if [ "$co_cnes_estabelecimento" = "" ]; then co_cnes_estabelecimento=$lcol; fi
      if [ "$co_cidpri" = "" ]; then co_cidpri=$lcol; fi
      if [ "$co_cidsec" = "" ]; then co_cidsec=$lcol; fi
      if [ "$qt_aprovada" = "" ]; then qt_aprovada=$lcol; fi
      if [ "$vl_aprovado" = "" ]; then vl_aprovado=$lcol; fi
      if [ "$pa_condic" = "" ]; then pa_condic=$lcol; fi

      # dbf em csv
      dbview -b -d"^" -t $fdbf | iconv -f "CP861" -t "UTF-8" | sed 's/"//g' | sed 's/\\//g' | awk -F'^' -v n=$lcol '{if (NF == n) print $0}'  > /tmp/tmp.$curdt.csv

      # obtem csv com campos selecionados
      paste -d, <(cat /tmp/tmp.$curdt.csv | sed 's/[^0-9\^]//g' | awk -F'^' -v uf=$uf -v nu_apac=$nu_apac -v nu_apac_anterior=$nu_apac_anterior -v co_procedimento=$co_procedimento -v co_gestao=$co_gestao -v co_ibge_municipio_residencia=$co_ibge_municipio_residencia -v co_cep_residencia=$co_cep_residencia -v co_carater_atendimento=$co_carater_atendimento -v nu_idade=$nu_idade -v co_motivo_saida=$co_motivo_saida -v co_ibge_municipio_evento=$co_ibge_municipio_evento -v dt_inicio=$dt_inicio -v dt_fim=$dt_fim -v co_cnes_estabelecimento=$co_cnes_estabelecimento -v qt_aprovada=$qt_aprovada -v d=$cmp '{print d","uf"," $nu_apac"," $nu_apac_anterior"," $co_procedimento","$co_gestao","$co_ibge_municipio_residencia"," $co_cep_residencia","$co_carater_atendimento"," $nu_idade"," $co_motivo_saida"," $co_ibge_municipio_evento"," $dt_inicio","$dt_fim"," $co_cnes_estabelecimento"," $qt_aprovada}'   ) <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v vl_aprovado=$vl_aprovado '{print $vl_aprovado}' | iconv -f "CP861" -t "UTF-8" | sed 's/[^0-9\.]//g' ) <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v nu_cnspcn=$nu_cnspcn '{print $nu_cnspcn}' | sed 's/{/0/g' | sed 's/}/9/g' | sed 's/~/8/g' | sed 's/ /7/g' | sed 's/Ç/6/g' | sed 's/ä/5/g' | sed 's/ü/4/g' | sed 's/é/3/g' | sed 's/|/2/g' | sed 's/â/1/g' | sed 's/[^0-9]/ /g'  | sed 's/ /4/g') <(cat /tmp/tmp.$curdt.csv | awk -F'^' -v sg_sexo=$sg_sexo -v co_cidpri=$co_cidpri -v co_cidsec=$co_cidsec -v pa_condic=$pa_condic -v o=$origem '{print substr($sg_sexo,1,1)"," substr($co_cidpri,1,4)"," substr($co_cidsec,1,50)","substr($pa_condic,1,2)","o}' | sed 's/0000//g') | sort -h > /tmp/tmp.$curdt.x.csv 

mv /tmp/tmp.$curdt.x.csv /tmp/tmp.$curdt.csv

   fi # -f "$fdbf"

}

Funções auxiliares

# OBTEM ARQUIVOS DO REPOSITORIO DO DATASUS E CARREGA NO POSTGRESQL
# Baixa os arquivos DBC do ftp do datasus para o Brasil.
# Recupera a quantidade aprovada do arquivo AM para o PA com procedimento 06
# Incorpora no PostgreSQL

# voce deve rodar no linux tendo instalado:
# wine
# dbview
# postgresql cliente

library("RCurl") # funcao getURL
library("downloader") # funcao download
library("RPostgreSQL") # conexao com SGBF
# library("read.dbc") # leitura de arquivo dbc
# library(foreign)
library(data.table)
library(gsubfn)
library(dplyr)
library(stringr) # para str_replace
library(read.dbc)
library(googledrive)
library(scales)

library(knitr)
library(lubridate)
library(png)  
library(R.utils)
library(kableExtra)

options(scipen = 100) # Evita notacao cientifica

dirdbc="/home/ferre/Downloads/dbc/"
# setwd(dirdbc)
url="ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/" # filezilla
# http://tabnet.datasus.gov.br/cgi/menu_baixa_tabwin.htm
exe="/home/ferre/Downloads/dbc/Tab415/dbf2dbc.exe"
tabnetcsv="/home/ferre/Dropbox/sabeis_pcdt29/tabnet.csv"

listaftp_diretriz="/home/ferre/Dropbox/sabeis_pcdt29/dados/listaftp_diretriz.csv.gz"


# CONEXAO COM O SGBD
pg_conecta = function(
  dbname,
  host,
  port,
  user,
  password
){
  con = DBI::dbConnect(
    dbDriver(drvName = "PostgreSQL"),
    dbname = dbname,
    host = host,
    port = port,
    user = user,
    password = password
  )
  return(con)
}

conexao = pg_conecta(dbname, host, port, user, password)

pg_roda_query = function(query){
  con = pg_conecta(dbname, host, port, user, password)
  x=dbGetQuery(
    con,
    query
  )
  
  dbDisconnect(con)
  rm(con)
  
  return(x)
}

# adiciona coisa no banco de dados
pg_persiste = function(dataframe, no_banco, no_tabela, overwrite, append){
  
  con = pg_conecta(dbname, host, port, user, password)
  x=dbWriteTable(
    con, 
    c(no_banco,no_tabela), 
    dataframe, 
    overwrite = overwrite,
    append = append
  )
  
  dbDisconnect(con)
  rm(con)
  
  return(x)
}

# retorna o none da ultima tabela de log
pg_obtem_mapa_ftp = function(){
  query="SELECT table_schema || '.' || table_name as log
           FROM information_schema.tables 
          WHERE table_schema = 'public' 
            and table_name like 'tf_coleta%' 
            and table_name not like '%tabnet%'
            and table_name not like '%procedimento%' 
            and table_name not like '%medicamento%' 
          order by 1"
  x=tail(pg_roda_query(query),1)$log[1]
  return(x)
}

pg_get_lista_tabela_medicamento = function(){
  query="select no_schema || '.' || no_tabela as tabela 
  from bd_medicamento.tf_coleta_medicamento  
  where no_schema || '.' || no_tabela in (  
  SELECT table_schema || '.' || table_name as tabela
           FROM information_schema.tables 
          WHERE table_schema like 'bd_medicamento_%')"
  tabelas=pg_roda_query(query)$tabela
  
  return(tabelas)
}

library(stringr)
set_colnames = function(vetor){
  x=str_replace_all(
    str_replace_all(
      str_replace_all(
        str_replace_all(
        str_replace_all(iconv(tolower(vetor), from = 'UTF-8', 
                              to = 'ASCII//TRANSLIT'),'[^a-z0-9]','_')
        ,'__','_')
      ,'__','_')
    ,'_$','')
,'_',' ')
  
  return(x)
}

formata <- function(x) {
  y = format(x, big.mark = ".", decimal.mark = ",", scientific = F)
  return(y)
}

formata_mes <- function(x) {
  y = format(as.Date(paste0(x,"15"),"%Y%m%d"),"%m/%Y")
  return(y)
}


firstup <- function(x) {
  substr(x, 1, 1) <- toupper(substr(x, 1, 1))
  x
}

library(gsheet)
get_gsheet = function(url,planilha){
  ds=as.data.frame(gsheet2tbl(url, sheetid = planiha))
  colnames(ds)=set_colnames(colnames(ds))
  # ds[is.na(ds)] <- 0
  return(ds)
}

# atualiza um registro de bd_medicamento.tf_coleta_medicamento
pg_update_tf_coleta_medicamento = function(tabela){
  query=paste0("update bd_medicamento.tf_coleta_medicamento A
   set qt_registros_medicamento = B.qt_registros_medicamento,
       qt_usuariosus_medicamento = B.qt_usuariosus_medicamento,
       qt_aprovada_medicamento = B.qt_aprovada_medicamento
  from (
select
       COUNT(*) as qt_registros_medicamento,
       COUNT(distinct nu_usuariosus) as qt_usuariosus_medicamento,
       sum(qt_aprovada) as qt_aprovada_medicamento
  from ", tabela,"
      ) B
      where A.no_schema || '.' || A.no_tabela = '", tabela,"'")
  pg_roda_query(query)
}

# atualiza todos os registro de bd_medicamento.tf_coleta_medicamento
pg_update_tf_coleta_medicamentos <- function() {
  tabela=pg_get_lista_tabela_medicamento()
  for (tabela in sort(tabelas,decreasing = TRUE)) {
    pg_update_tf_coleta_medicamento(tabela)
  }  
}


pg_set_log_coleta_medicamento = function(){
  log=pg_obtem_mapa_ftp()
  
  # colocar timestamp
  pg_roda_query("delete from bd_medicamento.tf_coleta_medicamento
  where (vl_discrepancia_tabnet > 5 
         or qt_registros_medicamento = 0
         or qt_registros_medicamento is null)")
    
  # insere novos registros na tabela de coleta
  query=paste0("insert into bd_medicamento.tf_coleta_medicamento
  select 'bd_medicamento_' || nu_ano as no_schema,
         lower('pa' || sg_uf || nu_competencia::text) as no_tabela,
         sg_uf ,
         nu_competencia ,
         string_agg(distinct case when sg_subsistema = 'PA' 
         then no_dbc else null end, ' ') as no_dbc_pa, 
         max(case when split_part(dt_dbc,'-',1)::int > 70 
            then (19 || dt_dbc)::DATE
            else (20 || dt_dbc)::DATE
         end) as dt_dbc,
         current_date as dt_carga_sabeis,
         sum(case when sg_subsistema = 'PA' 
         then qt_bytes_dbc else 0 end) as qt_bytes_dbc_pa, 
         sum(case when sg_subsistema = 'PA' 
         then qt_registros_dbc_local else 0 end) as qt_registros_dbc_local_pa,
         string_agg(distinct case when sg_subsistema = 'AM' 
         then no_dbc else null end, ' ') as no_dbc_am, 
         sum(case when sg_subsistema = 'AM' 
         then qt_bytes_dbc else 0 end) as qt_bytes_dbc_am, 
         sum(case when sg_subsistema = 'AM' 
         then qt_registros_dbc_local else 0 end) as qt_registros_dbc_local_am         
    from ", log,"
   where sg_subsistema in ('PA','AM')
     and sg_sistema = 'SIA'
     and nu_ano >= 2008
     and lower('pa' || sg_uf || nu_competencia::text) 
     not in (select distinct no_tabela from bd_medicamento.tf_coleta_medicamento)
   group by 1,2,3,4
   order by 1,2,3 ;")
  pg_roda_query(query)

  
  # lista tabelas persistidas
  query="select no_schema || '.' || no_tabela as tabela 
  from bd_medicamento.tf_coleta_medicamento  
  where no_schema || '.' || no_tabela in (  
  SELECT table_schema || '.' || table_name as tabela
           FROM information_schema.tables 
          WHERE table_schema like 'bd_medicamento_%')
  "
  tabelas=pg_roda_query(query)$tabela
  
  for (tabela in tabelas) {
      print(tabela)
     pg_update_tf_coleta_medicamento(tabela)
  }

}

# TABNET

pg_tabnet_ufcmp = function(tabnetcsv,listaftp){
  # processa o CSV obtido do TABNET 
  # em http://tabnet.datasus.gov.br/cgi/tabcgi.exe?sia/cnv/qauf.def
  # com a quantidade aprovada de 
  # procedimento 06
  # por UF
  # por ano e mes de processamento
  
  tabnetcsv=
    get_gsheet("https://docs.google.com/spreadsheets/d/1lnZsswLNEe1_2YbV95MZLM8ECl_seP-f9xpgBptcxZI/edit?usp=sharing",
    planilha = 'ufcmp')
  
  tabnet=tabnetcsv
  tabnet[tabnet == '-'] <- 0
  
  # transpoe a tabela de colunas para linhas
  aux=tabnet[,c(1,2)]
  colnames(aux)=c("nu_competencia","qt_aprovada_tabnet")
  aux$sg_uf=colnames(tabnet)[2]
  colnames(tabnet)[1]="nu_competencia"
  
  for (uf in colnames(tabnet)[3:ncol(tabnet)-1]) {
    aux2=tabnet[,c("nu_competencia",uf)]
    colnames(aux2)=c("nu_competencia","qt_aprovada_tabnet")
    aux2$sg_uf=uf
    aux=rbind(
      aux,
      aux2
    )
  }
  
  aux$nu_competencia=as.numeric(substr(str_replace(aux$nu_competencia,'-',''),1,6))
  aux$sg_uf=toupper(aux$sg_uf)
  
  #pg_persiste(listaftp,"public","tm_tabnet",TRUE,FALSE)
  pg_persiste(aux,"public","tf_tabnet",TRUE,FALSE)
  
  # atualiza quantidade aprovada do tabnet  
  pg_roda_query("   update bd_medicamento.tf_coleta_medicamento A
                    set qt_aprovada_tabnet = B.qt_aprovada_tabnet::int 
                   from public.tf_tabnet B
                  where A.nu_competencia = B.nu_competencia::int
                    and A.sg_uf = B.sg_uf")

  # atualiza indice de discrepancia em relacao ao tabnet  
  pg_roda_query("update bd_medicamento.tf_coleta_medicamento
set vl_discrepancia_tabnet = case when qt_aprovada_medicamento > 0 
and qt_aprovada_tabnet > 0 
            then ROUND(ABS(qt_aprovada_tabnet - qt_aprovada_medicamento)::decimal /
            (qt_aprovada_tabnet + qt_aprovada_medicamento)*100)
            when qt_aprovada_medicamento is null and qt_aprovada_tabnet = 0 then 0
            else 100
            end 
")
}

pg_qt_procedimento_mes <- function() {
  
  pg_roda_query("DROP TABLE IF EXISTS bd_medicamento.tf_procedimento_uf_mes;
  CREATE TABLE bd_medicamento.tf_procedimento_uf_mes (
    nu_competencia int4 NULL,
    sg_uf VARCHAR(2) NULL,
    co_procedimento int8 NULL,
    qt_registros int8 NULL,
    qt_usuariosus int8 NULL,
    qt_cnes int8 NULL,
    qt_municipio_ibge_residencia int8 NULL,
    qt_aprovada INT8 NULL
);")
  
  tabelas=pg_get_lista_tabela_medicamento()
  for (tabela in sort(tabelas,decreasing = FALSE)) {
    print(tabela)
    pg_roda_query(paste0("INSERT INTO bd_medicamento.tf_procedimento_uf_mes
   select nu_competencia
       ,sg_uf
       ,co_procedimento
       ,count(*) qt_registros
       ,count(distinct nu_usuariosus) qt_usuariosus
       ,count(distinct co_cnes) qt_cnes
       ,count(distinct co_municipio_ibge_residencia) as qt_municipio_ibge_residencia
       ,sum(qt_aprovada) qt_aprovada
  from ",tabela,"
  group by 1,2,3
  order by 3"))
  }
  
  pg_roda_query("drop table if exists bd_medicamento.tf_procedimento_mes;
create table bd_medicamento.tf_procedimento_mes as
select A.nu_competencia
       ,A.co_procedimento
       ,sum(qt_registros) qt_registros
       ,sum(qt_usuariosus) qt_usuariosus
       ,sum(qt_cnes) qt_cnes
       ,sum(qt_municipio_ibge_residencia) as qt_municipio_ibge_residencia
       ,sum(qt_aprovada) as qt_aprovada
       ,sum(qt_aprovada_tabnet) as qt_aprovada_tabnet
  from bd_medicamento.tf_procedimento_uf_mes A
  left join bd_medicamento.tf_tabnet_procedimento B
  on A.co_procedimento = B.co_procedimento 
  and A.nu_competencia = B.nu_competencia 
  group by 1,2
  order by 1,2")
  
  pg_roda_query("  GRANT ALL ON SCHEMA bd_medicamento TO conitec;
 GRANT SELECT ON ALL TABLES IN SCHEMA bd_medicamento TO conitec;")
  
}



pg_tabnet_sigtapcp = function(){
  # processa o CSV obtido do TABNET 
  # em http://tabnet.datasus.gov.br/cgi/tabcgi.exe?sia/cnv/qauf.def
  # com a quantidade aprovada de 
  # procedimento 06
  # por UF
  # por ano e mes de processamento
  
  tabnet=
    get_gsheet("https://docs.google.com/spreadsheets/d/1EmGWPp9xXwn_PqefazyDpdpYwoqwdABQ0eN7cwE4edk/edit?usp=sharing",
               planilha = 3)
  
  # transpoe a tabela de colunas para linhas
  aux=tabnet[,c(1,2)]
  colnames(aux)=c("no_procedimento","qt_aprovada_tabnet")
  aux$nu_competencia=colnames(tabnet)[2]
  
  for (cp in colnames(tabnet)[4:ncol(tabnet)-1]) {
    aux2=tabnet[,c("procedimento",cp)]
    colnames(aux2)=c("no_procedimento","qt_aprovada_tabnet")
    aux2$nu_competencia=cp
    aux=rbind(
      aux,
      aux2
    )
  }
  
  aux$nu_competencia=as.numeric(substr(str_replace(aux$nu_competencia,'_',''),1,6))
  aux$co_procedimento=as.numeric(substr(aux$no_procedimento,1,10))
  
  #pg_persiste(listaftp,"public","tm_tabnet",TRUE,FALSE)
  pg_persiste(aux,"bd_medicamento","tf_tabnet_procedimento",TRUE,FALSE)
  
  pg_roda_query("CREATE INDEX tf_tabnet_procedimento_nu_competencia_idx 
  ON bd_medicamento.tf_tabnet_procedimento (nu_competencia);
CREATE INDEX tf_tabnet_procedimento_co_procedimento_idx 
ON bd_medicamento.tf_tabnet_procedimento (co_procedimento);
")
  
  # atualiza quantidade aprovada do tabnet  
  pg_roda_query("   update bd_medicamento.tf_coleta_medicamento A
                    set qt_aprovada_tabnet = B.qt_aprovada_tabnet::int 
                   from public.tf_tabnet B
                  where A.nu_competencia = B.nu_competencia::int
                    and A.sg_uf = B.sg_uf")
  
  # atualiza indice de discrepancia em relacao ao tabnet  
  pg_roda_query("update bd_medicamento.tf_coleta_medicamento
set vl_discrepancia_tabnet = case when qt_aprovada_medicamento > 0 and qt_aprovada_tabnet > 0 
            then ROUND(ABS(qt_aprovada_tabnet - qt_aprovada_medicamento)::decimal /
            (qt_aprovada_tabnet + qt_aprovada_medicamento)*100)
            when qt_aprovada_medicamento is null and qt_aprovada_tabnet = 0 then 0
            else 100
            end 
")
}


# faz limpeza em todas as tabelas listadas

pg_set_vacuum_full = function(tabelas){
  for (tabela in tabelas) {
    print(tabela)
    query=paste0("vacuum full ", tabela)
    pg_roda_query(query)
  }
}
# para rodar o vacuum nas tabelas de medicamento: 
# pg_set_vacuum_full(pg_get_lista_tabela_medicamento())


# converte CNS em caracteres numericos
set_cns = function(cns){
  x=gsubfn(
    ".", 
    list(
      "{" = "0", "}" = "9", "~" = "8", 
      "\177" = "7", "Ç" = "6", "ä" = "5", 
      "ü" = "4", "é" = "3", "|" = "2", "â" = "1"
    ), 
    iconv(cns, "CP861", "UTF-8")
  )
}


get_estrutura = function(fdbf){
  
  # estrutura do dbf
  str=system(paste("dbview -e -o ", fdbf," | 
                   awk -F'\t' '{ gsub(/[ \t]+$/,\"\",$1);gsub(\" \",\"_\",$1); print tolower($1)\",\"$2\",\"$3}' | 
                   sed 1d | sed 's/ //g' | nl | awk '{print $1\",\"$2}' | sed ':a;N;$!ba;s/\\n/,/g'"), intern = TRUE)
  str=as.data.frame(matrix(as.vector(strsplit(str, ",")[[1]]),ncol=4, byrow=TRUE))
  
  return(str)
  
}

# transforma o DBC e faz a carga no POSTGRESQL

pg_set_dbc_file = function(file,col,schema,tabela,exe,orverwrite,append,removedbc){
  # file="/media/ferre/ferre1tb/Downloads/dbc/PARS2011.dbc"
  
  fdbc=tail(str_split(file,"/")[[1]],1)
  dbc=tools::file_path_sans_ext(file)
  if (!file.exists(file)) {
    fdbc=file
    # file=paste0("/tmp/",fdbc)
    if (!file.exists(file)) {
      print(paste("baixando...",file))
      download.file(
        paste0(url,fdbc),
        destfile = file
      )      
    }
  }
  setwd(dirname(file))
  
  if (file.exists(file)) {
    # dbc para dbf
    system(paste("wine", exe, file))
    fdbf=system(paste0("ls ",dirname(file), " | grep -i ", tools::file_path_sans_ext(fdbc) ," | 
                       grep -i dbf | head -1"), intern = TRUE)
    str=get_estrutura(fdbf)
    
    # obtem as colunas de interesse 
    cols=paste(str[str$V2 %in% tolower(unlist(col)),"V1"], collapse='","$')
    system(paste0("echo '",paste(str$V2[str$V2 %in% tolower(col)], collapse = ","), "'> /tmp/tmp.csv"))
    system(paste("dbview -b -d\"^\" -t ", fdbf, " | iconv -f \"CP861\" -t \"UTF-8\" | sed 's/,//g' | 
                 awk -F'^' '{print $",cols,"}' | grep ,06 | sed 's/{/0/g' | sed 's/}/9/g' | 
                 sed 's/~/8/g' | sed 's/ /7/g' | sed 's/Ç/6/g' | sed 's/ä/5/g' | sed 's/ü/4/g' | 
                 sed 's/é/3/g' | sed 's/|/2/g' | sed 's/â/1/g' | sed 's/[^A-Z0-9,]/ /g'  | 
                 sed 's/ /4/g' | sed 's/,0000,/,,/g' | sed 's/,0000000000000,/,,/g' | 
                 sort -u >> /tmp/tmp.csv"))
    
    # persiste tabela
    pg_persiste(read.csv(file="/tmp/tmp.csv"),schema,tabela,orverwrite,append)
    # system(paste0("PGPASSWORD=",password," psql -U ",user," -h ",host," -d ",dbname," -p ",port," -c 'COPY ", schema,'.',tabela," FROM STDIN csv HEADER' < /tmp/tmp.csv"))
    
    if (removedbc) {
      file.remove(file)
    }
    file.remove(fdbf)
  }
}

pg_get_set_dbc = function(fdbc,url,schema,tabela,overwrite,append,remove){
  file=paste0("/tmp/",fdbc)
  if (!file.exists(file)) {
    print(paste("baixando...",file))
    download.file(
      paste0(url,fdbc),
      destfile = file
    )
  }
  
  pg_persiste(
    read.dbc(file), 
    schema,tabela,overwrite,append
  )
  
  if (file.exists(file) & remove) {
    # deleta arquivo se existir
    file.remove(file)
  }
  
}


pg_get_set_dbc_cns = function(fdbc,url,cns,schema,tabela,overwrite,orverwrite,append){
  file=paste0("/tmp/",fdbc)
  download.file(
    paste0(url,fdbc),
    destfile = file
  )
  
  x=read.dbc(file)[,c("AP_CODUNI","AP_AUTORIZ","AP_CNSPCN")]
  
  x$AP_CNSPCN=set_cns(x$AP_CNSPCN)
    
  pg_persiste(
    x, 
    schema,tabela,overwrite,append
  )
  
  if (file.exists(file)) {
    # deleta arquivo se existir
    file.remove(file)
  }
  
}



# persiste dado registro listado em bd_medicamento.tf_coleta_medicamento
pg_set_medicamento = function(tabela){
  # FTP do datasus
  url="ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/"
  
  # lista de parametros para a coleta e carga
  tf_coleta_medicamento=pg_roda_query(
    paste0("SELECT * FROM bd_medicamento.tf_coleta_medicamento WHERE no_tabela='", tabela,"'")
  )

  
  # persiste arquivos PA, concatenando quando ha mais de um  
  dbc_pa=strsplit(tf_coleta_medicamento$no_dbc_pa, " ")[[1]]
  col=tolower(c("PA_CODUNI","PA_AUTORIZ","PA_PROC_ID","PA_CIDPRI",
                "PA_QTDAPR","PA_UFMUN","PA_MUNPCN","PA_IDADE","PA_SEXO","PA_CIDSEC"))
  for (i in 1:length(dbc_pa)) {
    # pg_get_set_dbc(dbc_pa[i],url,"public","tmp_pa",i==1,i>1)
    pg_set_dbc_file(paste0(dirdbc,dbc_pa[i]),col,
                    "public","tmp_pa",exe,i==1,i>1,TRUE)
  }
  
  pg_roda_query("delete from tmp_pa where substr(pa_proc_id::text,1,1)<>'6' or
  pa_qtdapr = 0 or pa_qtdapr is null; 
 alter table tmp_pa add pa_cnspcn int8 default null;")
  
  pg_roda_query(paste0(
    "drop table if exists 
    ", tf_coleta_medicamento$no_schema,".", tf_coleta_medicamento$no_tabela,
    ";create table ", tf_coleta_medicamento$no_schema,".", 
    tf_coleta_medicamento$no_tabela,
    " () inherits (bd_medicamento.tf_dispensacao)",
    ";insert into ", tf_coleta_medicamento$no_schema,".", 
    tf_coleta_medicamento$no_tabela," 
    (co_procedimento, co_cidpri, nu_usuariosus, nu_idade, sg_sexo, qt_aprovada, 
    co_cnes, co_municipio_ibge, co_municipio_ibge_residencia, co_cidsec, nu_apac) 
    select  pa_proc_id, pa_cidpri, pa_cnspcn, pa_idade, pa_sexo, pa_qtdapr, 
    pa_coduni, pa_ufmun, pa_munpcn, pa_cidsec, pa_autoriz 
      from tmp_pa order by 1,2,3; drop table if exists tmp_pa,tmp_am;"
  ))
  
  pg_roda_query(paste0("UPDATE ", tf_coleta_medicamento$no_schema,".", 
                       tf_coleta_medicamento$no_tabela," SET nu_competencia = ", 
                       tf_coleta_medicamento$nu_competencia, ", sg_uf = '", 
                       tf_coleta_medicamento$sg_uf,"'"))

  if (!is.na(tf_coleta_medicamento$no_dbc_am)) {
    # persiste arquivos AM
    # pg_get_set_dbc(tf_coleta_medicamento$no_dbc_am,url,"public","tmp_am",TRUE,FALSE)
    col=tolower(c("AP_CODUNI","AP_AUTORIZ","AP_CNSPCN"))
    pg_set_dbc_file(paste0(dirdbc,tf_coleta_medicamento$no_dbc_am),
                    col,"public","tmp_am",exe,TRUE,FALSE,TRUE)
    pg_roda_query("update ", tf_coleta_medicamento$no_schema,".", 
                  tf_coleta_medicamento$no_tabela,
                  " set nu_usuariosus = tmp_am.ap_cnspcn from tmp_am 
                  where pa_autoriz::text = nu_apac and co_cnes = ap_coduni::bigint;")
  }
  
pg_roda_query(paste0(
  "update bd_medicamento.tf_coleta_medicamento A
set dt_carga_sabeis = current_date,
    qt_registros_medicamento = B.qt_registros_medicamento,
    qt_usuariosus_medicamento = B.qt_usuariosus_medicamento,
    qt_aprovada_medicamento = B.qt_aprovada_medicamento,
    vl_discrepancia_tabnet = case when B.qt_aprovada_medicamento > 0 and qt_aprovada_tabnet > 0 
            then ROUND(ABS(qt_aprovada_tabnet - B.qt_aprovada_medicamento)::decimal / 
            (qt_aprovada_tabnet + B.qt_aprovada_medicamento)*100)
            when B.qt_aprovada_medicamento is null and qt_aprovada_tabnet = 0 then 0
            else 100
            end 
from (
select 
count(*) qt_registros_medicamento, 
count(distinct nu_usuariosus) qt_usuariosus_medicamento, 
sum(qt_aprovada) qt_aprovada_medicamento 
from ",tf_coleta_medicamento$no_schema,".", tf_coleta_medicamento$no_tabela,"
      ) B      
  where A.no_tabela = '", tf_coleta_medicamento$no_tabela,"';
"
))
  

    
}

remove_dbc_discrepante <- function(dirdbc) {
  dbcs = unlist(strsplit(pg_roda_query(
    " select case when no_dbc_pa is null then '' else no_dbc_pa end  || ' ' || 
    case when no_dbc_am is null then '' else no_dbc_am end as dbc
      from bd_medicamento.tf_coleta_medicamento 
     where (vl_discrepancia_tabnet > 5 
        or qt_registros_medicamento = 0
        or qt_registros_medicamento is null)
    order by nu_competencia desc, sg_uf"
  )$dbc, " "), recursive = TRUE)
  
  for (dbc in dbcs) {
    file=paste0(dirdbc,"/",dbc)
    print(file)
    if (file.exists(file)) {
      file.remove(file)
    }
     
  }
}


baixa_dbc_discrepante <- function(dirdbc,url) {
  dbcs = unlist(strsplit(pg_roda_query(
    " select case when no_dbc_pa is null then '' else no_dbc_pa end  || ' ' || 
    case when no_dbc_am is null then '' else no_dbc_am end as dbc
      from bd_medicamento.tf_coleta_medicamento 
     where (vl_discrepancia_tabnet > 5 
        or qt_registros_medicamento = 0
        or qt_registros_medicamento is null)
    order by nu_competencia desc, sg_uf"
  )$dbc, " "), recursive = TRUE)
  
  for (dbc in dbcs) {
    file=paste0(dirdbc,"/",dbc)
    print(file)
    if (file.exists(file)) {
      file.remove(file)
    } 
    
    download.file(
      paste0(url,"/",dbc),
      destfile = file
    )
  }
}


baixa_dbc_discrepante_bash <- function(dirdbc) {
  setwd(dirdbc)
  system("wget --ignore-case --no-clobber ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/PA*")
  system("wget --ignore-case --no-clobber ftp://ftp.datasus.gov.br/dissemin/publicos/SIASUS/200801_/Dados/AM*")
}


pg_set_medicamentos <- function() {
  tabelas = pg_roda_query(
    "select no_tabela 
      from bd_medicamento.tf_coleta_medicamento 
     where (vl_discrepancia_tabnet > 5 
        or qt_registros_medicamento = 0
        or qt_registros_medicamento is null)
    order by nu_competencia desc, sg_uf"
  )$no_tabela

  # and qt_aprovada_tabnet > 0
  setwd(dirdbc)  
  for (tabela in tabelas) {
    print(tabela)
    try(pg_set_medicamento(tabela))
  }
  
}

pg_obtem_procedimento_por_sigla <- function(sigla) {
  ds=pg_roda_query(paste0("select no_procedimento, co_procedimento 
                          from bd_medicamento.td_medicamento
                          where sg_procedimento = '",sigla,"'"))
  
  ds$no_procedimento=tolower(ds$no_procedimento)
  ds$no_procedimento=str_replace_all(ds$no_procedimento,"ml","mL")
  ds$no_procedimento=str_replace_all(ds$no_procedimento," ui "," UI ")
  
  return(ds)
}

pg_preco_medicamento <- function() {
  url="https://docs.google.com/spreadsheets/d/1bD4c8bs3MLwKSGWzCf_lay2q9kz9KNr-lfP6Ivqdg0A/edit?usp=sharing"
  ds=as.data.frame(gsheet2tbl(url, sheetid = "valor_medicamento"))
  pg_persiste(ds,"bd_medicamento", "tm_medicamento_preco", TRUE,FALSE)
  query=paste0("drop table if exists bd_medicamento.tf_medicamento_preco;
  create table bd_medicamento.tf_medicamento_preco AS 
  select A.sg_procedimento, A.dt_compra as dt_inicio,
  case when B.dt_compra is null then A.dt_compra + 400 else B.dt_compra-1 
       end as dt_fim, round(A.vl_unitario_centavos::decimal/100,2) as vl_unitario,
       A.vl_unitario_centavos from (select DENSE_RANK () OVER 
  (partition by sg_procedimento ORDER BY dt_compra::date) co_seq_abrev_data ,* 
  from (select distinct sg_procedimento, dt_compra::date, vl_unitario_centavos 
  from bd_medicamento.tm_medicamento_preco ) x order by sg_procedimento ,  dt_compra::date ) A 
  left join ( select DENSE_RANK () 
  OVER (partition by sg_procedimento ORDER BY dt_compra::date) co_seq_abrev_data,* 
  from (select distinct sg_procedimento, dt_compra::date, vl_unitario_centavos 
  from bd_medicamento.tm_medicamento_preco ) x order by sg_procedimento ,  dt_compra::date ) B 
  on A.sg_procedimento=B.sg_procedimento and A.co_seq_abrev_data=B.co_seq_abrev_data-1")
pg_roda_query(query)

# try(pg_roda_query("alter table bd_medicamento.tf_procedimento_mes add vl_total decimal(21,2);"))

query=paste0("update bd_medicamento.tf_procedimento_mes A
   set vl_total = qt_aprovada * vl_unitario
  from bd_medicamento.tf_medicamento_preco B
  where A.sg_procedimento = B.sg_procedimento
    and to_date(nu_competencia::text || '15', 'yyyymmdd') 
        between dt_inicio and dt_fim;")
pg_roda_query(query)

}


# resumo da ETL
resumo=pg_roda_query(
  "select sum(case when vl_discrepancia_tabnet > 1 then 1 else 0  end) arquivos_discrepantes, 
   string_agg(distinct case when vl_discrepancia_tabnet > 1 then no_dbc_pa else null end, ' ') as dbc,
          count(*) arquivos,
       sum(case when vl_discrepancia_tabnet > 1 then qt_registros_medicamento else 0 end) 
       as qt_registros_medicamento_discrepante,
       sum(qt_registros_medicamento) as qt_registros_medicamento,
       sum(qt_aprovada_tabnet) qt_aprovada_tabnet,
       sum(qt_aprovada_medicamento) qt_aprovada_medicamento,
       round(sum(qt_aprovada_tabnet)::decimal/sum(qt_aprovada_medicamento)*100,1) 
       as registros_acurados
  from bd_medicamento.tf_coleta_medicamento  "
)

resumo[3:7]=formata(round(as.numeric(resumo[3:7])))
resumo[8]=formata(100-as.numeric(resumo[8]))

Funções de visualização de dados

library(ggplot2)
library(grid)
library(pyramid)
# library(sf)

library(forecast)
library(tseries)

plot_difusao <- function(dataframe, titulo, tipo,rotulo_x, rotulo_y) {
  # dataframe: atributo | tempo | n | p |rotulo |  
  # plot_difusao(dataframe[[2]],"teste","empilhada")
  # dataframe: tempo | categoria | valor | percentual
  # tempo = colnames(dataframe)[1]
  # categoria = colnames(dataframe)[2]
  # percentual = colnames(dataframe)[4]
  
  # dataframe$ano = as.factor(dataframe$ano)
  # dataframe$rotulo = format(dataframe$rotulo, big.mark = ".", decimal.mark = ",")
  
  # eixo_x=sort(levels(as.factor(dataframe[,2])))
  # eixo_x[!1:length(eixo_x) %in% seq(1, length(eixo_x),by = round(length(eixo_x)/12))]=""
  
  # seq(as.Date("2001-01-01"), by = "month", along = foo)
  # obtem escala de milhar, milhao, bilhao, etc
  escala=10^max(seq(3,15, by = 3)[mean(log10(dataframe$n))>seq(3,15, by = 3)])
  if (!is.na(escala) & !is.infinite(escala) & escala>0) {
    dataframe[,3]=dataframe[,3]/escala
    rotulo_y=paste0(rotulo_y, " (×", formata(escala) ,")")
  }
  
  
  if (tipo == 'empilhada') {
    g =ggplot(
      dataframe,
      aes(
        fill = atributo,
        y = p,
        x = ano,
        label = rotulo
      )
    ) +
      geom_bar(stat = "identity") +
      xlab('ano') +
      ylab('usuários (%)') +
      geom_text(size = 4, position = position_stack(vjust = 0.5)) +
      coord_flip() +
      scale_fill_brewer(palette = "Pastel1") +
      theme(
        legend.position = "bottom",
        legend.text = element_text(size = rel(1.3)),
        axis.title.x = element_text(size = rel(1.3)),
        axis.title.y = element_text(size = rel(1.3)),
        axis.text.x = element_text(size = rel(1.3)),
        axis.text.y = element_text(size = rel(1.3))
      )
  } 
  
  if (tipo == 'barras verticais') {
    g = ggplot(dataframe, aes(fill=atributo, y=n/100000, x=ano)) + 
      geom_bar(position="dodge", stat="identity") +
      scale_fill_brewer(palette = "Set2")+
      theme(
        legend.position = "bottom",
        legend.text = element_text(size = rel(1.3)),
        axis.title.x = element_text(size = rel(1.3)),
        axis.title.y = element_text(size = rel(1.3)),
        axis.text.x = element_text(size = rel(1.3)),
        axis.text.y = element_text(size = rel(1.3))
      )+
      xlab('ano') +
      ylab('usuários por 100 mil hab.') 
  }
  
  if (tipo == 'série temporal 1') {

    g = ggplot(dataframe, aes(x=ano, y=n, group=atributo, color=atributo)) +
      geom_line() +
      scale_fill_brewer(palette = "Set2")+
      labs(title = titulo)+
      # scale_x_continuous(breaks = eixo_x) +
      # scale_x_datetime(labels = date_format("%Y-%m"), breaks = date_breaks("months")) + 
      xlab('') +
      ylab(rotulo_y) + 
      scale_y_continuous(labels=function(x) formata(x)) +
      theme(
        legend.position = "none",
        legend.text = element_text(size = rel(1.0)),
        axis.title.x = element_text(size = rel(1.0)),
        axis.title.y = element_text(size = rel(1.0)),
        axis.text.x = element_text(size = rel(1.0), angle = 45),
        axis.text.y = element_text(size = rel(1.3))
        
      )
   
    
     
  }
  
  if (tipo == 'série temporal 2'){
    
    p = ggplot(dataframe) + 
      geom_line(aes(x = ano, y = n, group = atributo, colour = atributo)) + 
      geom_text(data = subset(dataframe, ano == 
                                input$painel_situacao_clinica_inAno[2] ), 
                aes(label = atributo, colour = atributo, x = Inf, y = n), hjust = 1) +
      scale_colour_discrete(guide = 'none')  +    
      # xlab('ano') +
      # ylab('usuários') +
      theme(plot.margin = unit(c(1,3,1,1), "lines"),
            axis.title.x = element_text(size = rel(1.3)),
            axis.title.y = element_text(size = rel(1.3)),
            axis.text.x = element_text(size = rel(1.3)),
            axis.text.y = element_text(size = rel(1.3))
            ,legend.position = "none"
      ) 
    
    # Code to turn off clipping
    gt <- ggplotGrob(p)
    gt$layout$clip[gt$layout$name == "panel"] <- "off"
    g = grid.draw(gt)
  }
  
  if (tipo == 'série temporal 3') {
    
    tsdata=dataframe$n
    names(tsdata)=dataframe$ano
    
    tsdata= forecast(auto.arima(
      tsdata,
      allowmean = TRUE
    ), h = 60)
    
   g = autoplot(tsdata) +
      geom_line() +
      scale_fill_brewer(palette = "Set2")+
      labs(title = titulo)+
      xlab('') +
      ylab(rotulo_y) + 
      scale_y_continuous(labels=function(x) formata(x)) +
      theme(
        legend.position = "none",
        legend.text = element_text(size = rel(1.0)),
        axis.title.x = element_text(size = rel(1.0)),
        axis.title.y = element_text(size = rel(1.0)),
        axis.text.x = element_text(size = rel(1.0), angle = 45),
        axis.text.y = element_text(size = rel(1.3))
      )
   
  }
  
  return(g)
}

plot_arima <- function(dataframe,titulo, rotulo_x, rotulo_y) {
    tsdata=dataframe$n
    names(tsdata)=dataframe$ano
  
  plot(
  forecast(auto.arima(
    tsdata,
    allowmean = TRUE
  ), h = 60),
   xlab = rotulo_x,
  ylab = rotulo_y,
  main = titulo
)
}