Produto 4- Relatório técnico contendo análise dos indicadores e resultados apresentados no painel de estudo de utilização de medicamentos (coorte) da sala de situação aberta dos tratamentos disponibilizados para o PCDT de artrite reumatoide.

Termo de Cooperação 76, TA nº 5, Ação nº 3. Apoiar a elaboração de estudos técnicos especializados para viabilizar as ações e estratégias preconizadas pela Comissão Nacional de Incorporação de Tecnologias no Sistema Único de Saúde (Conitec). Contrato CON21-00013320

Felipe Ferré

21/02/2022

1 Introdução
- 1.1 Objetivos
  - 1.1.1 Objetivos específicos
2 Método
3 Resultados
4 Considerações
Apêndice

1 Introdução

O uso dos medicamentos disponibilizados pelo SUS para medicamentos biológicos é regido pelo respectivo Protocolo Clínico 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 nas 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, onde o controle do Estado torna-se mandatório devido ao alto custo das terapias (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 do tratamento no elenco não implica na sua efetividade na saúde da população devido à relação do gestor com o Complexo Econômico-Industrial da Saúde, incluindo a cadeia logística e demais prestadores; 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). Toda a cadeia assistencial deve ser contemplada visando eficiência dos gastos públicos (Meiners et al. 2017) antes de pautar a incorporação ou desincorporação de tratamentos no elenco do SUS.

Os dados de mundo real de medicamentos regulados por PCDT para análise de medicamentos regulados por PCDT são disponibilizados pela Secretaria de Atenção Especializada à Saúde - SAES, a qual dissemina 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 tabela mantida pelo SIGTAP - Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS. No presente produto são apresentados os resultados da versão 24, de 09/09/2021 da Sala Aberta de Situação em Saúde para medicamentos regulados por PCDT, em grande parte, derivados do Componente Especializado da Assistência Farmacêutica.

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

1.1 Objetivos

Realizar análise análise dos indicadores e resultados apresentados no painel de estudo de utilização de medicamentos (coorte) da sala de situação aberta dos tratamentos disponibilizados para o PCDT de artrite reumatoide.

1.1.1 Objetivos específicos

Estabelecer processo transparente de extração, engenharia e processamento de dados abertos da produção ambulatorial do SUS.
Estabelecer processo transparente de repositórios analíticos e visualização.
Estabelecer indicadores transparentes de monitoramento de medicamentos regidos pelo Protocolo Clínico e Diretrizes Terapêuticas de medicamentos biológicos.

2 Método

2.1 Coleta

A extração, transformação e carga (do inglês, Extract, Transform, Load - ETL) foi 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 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. A tabela 2.1 mostra exemplos de arquivos DBC.

log = pg_obtem_mapa_ftp()
query=paste0("SELECT no_dbc, dt_dbc, sg_sistema, 
                    sg_subsistema, qt_registros_dbc_local, qt_bytes_dbc
               FROM ", log, 
             "  order by random() limit 30")
  
kb=pg_roda_query(query)

kb$qt_registros_dbc_local=formata(kb$qt_registros_dbc_local)
kb$qt_bytes_dbc=formata(kb$qt_bytes_dbc)

colnames(kb)=set_colnames(colnames(kb))


library(kableExtra)

kbl(kb, 
    align = c(rep("r",ncol(kb))),
    caption = "Amostra de arquivos DBC disponíveis no repositório do DataSUS") %>%
    kable_classic()

Table 2.1: 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
ARDF1203.dbc	13-09-16	SIA	AR	196	14.963
DCPR1601.dbc	16-08-15	CNES	DC	184	17.346
LTAL0711.dbc	14-06-04	CNES	LT	468	6.963
PSCE1511.dbc	16-11-07	SIA	PS	22.080	504.862
ADMS1303.dbc	14-09-03	SIA	AD	2.424	130.748
EQSE2006.dbc	20-08-24	CNES	EQ	5.604	51.501
STTO1708.dbc	17-09-15	CNES	ST	1.838	92.367
DCPE1609.dbc	16-10-17	CNES	DC	61	9.480
RCSC1507.dbc	16-08-11	CNES	RC	528	24.577
RDPI9606.dbc	13-10-31	SIH	RD	18.918	672.124
PAAC0006.dbc	13-10-24	SIA	PA	8.738	85.842
GMES0709.dbc	14-06-04	CNES	GM	2	1.175
ERRO1904.dbc	20-06-04	SIH	ER	504	11.127
RJRN2012.dbc	22-02-07	SIH	RJ	809	53.676
RCCE1001.dbc	14-06-04	CNES	RC	236	8.199
RDMA0307.dbc	13-10-31	SIH	RD	31.785	1.366.867
EQSP0605.dbc	14-06-04	CNES	EQ	21.830	230.761
DCES0904.dbc	14-06-04	CNES	DC	58	9.148
GMRN2001.dbc	20-05-08	CNES	GM	7	1.474
PFRJ2009.dbc	20-11-28	CNES	PF	350.616	17.555.236
EERR1704.dbc	17-07-17	CNES	EE	1	1.136
RCTO1506.dbc	16-08-11	CNES	RC	151	7.417
SRMG2011.dbc	20-12-18	CNES	SR	94.910	1.010.623
LTPR1212.dbc	16-08-09	CNES	LT	3.505	42.261
HBSC2002.dbc	20-05-08	CNES	HB	1.078	32.297
SRPE0709.dbc	14-06-04	CNES	SR	13.371	136.257
AQRO0805.dbc	11-06-28	SIA	AQ	373	24.995
RJAL0611.dbc	13-10-31	SIH	RJ	1.290	67.412
CIHASC2106.dbc	22-02-09	CIHA	CIHA	47.618	858.047
ERRN2108.dbc	22-02-07	SIH	ER	1.439	29.922

# obtem o nome da tabela de log de regitros do DATASUS
log = pg_obtem_mapa_ftp()

# obtem data a partir tabela de log de regitros do DATASUS
log_data = format(as.Date(gsub("[^0-9]","",log),"%Y%m%d"),"%B/%Y")
  
# sumario dos dados abertos do DATASUS
query=paste0("SELECT count(distinct no_dbc) qt_dbc, 
       sum(qt_bytes_dbc) qt_bytes, 
       sum(qt_registros_dbc_local) qt_registros,
       min(case when nu_competencia > 100 then nu_competencia else null end) cmpmin,
       max(nu_competencia) cmpmax
 FROM ", log)
  
sumariodbc=pg_roda_query(query)
sumariodbc[1:3]=formata(sumariodbc[1:3])
sumariodbc[4:5]=formata_mes(sumariodbc[4:5])

O dataSUS disponibilizou, em março/2022, 13.097.031.743 registros em 142.077 arquivos DBC com dados de 01/1992 à 01/2022. A tabela 2.2 resume o conteúdo disponível.

# sumario dos dados abertos do DATASUS
query=paste0("SELECT sg_sistema as sistema,
       string_agg(distinct sg_subsistema, ' ') as subsistema,  
       count(distinct no_dbc) dbc, 
       sum(qt_bytes_dbc) bytes, 
       sum(qt_registros_dbc_local) registros,
       min(case when nu_competencia > 100 then nu_competencia else null end) mes_inicial,
       max(nu_competencia) mes_final
 FROM ", log, " group by 1")
  
kbsumariodbc=pg_roda_query(query)
kbsumariodbc[3:5]=formata(kbsumariodbc[3:5])
kbsumariodbc$mes_inicial=formata_mes(kbsumariodbc$mes_inicial)
kbsumariodbc$mes_final=formata_mes(kbsumariodbc$mes_final)
colnames(kbsumariodbc)=set_colnames(colnames(kbsumariodbc))

library(kableExtra)

kbl(kbsumariodbc, 
    align = c(rep("r",ncol(kbsumariodbc))),
    caption = "Sumário dos arquivos DBC segundo o sistema de informação do SUS") %>%
    kable_classic()

Table 2.2: Sumário dos arquivos DBC segundo o sistema de informação do SUS
sistema	subsistema	dbc	bytes	registros	mes inicial	mes final
CIH	CR	868	165.114.178	7.676.888	01/2008	04/2011
CIHA	CIHA	3.368	2.673.482.061	184.922.056	01/2011	12/2021
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.200	35.180.575.573	917.691.565	08/2005	01/2022
SIA	AB ABO ACF AD AM AMP AN AQ AR ATD BI PA PS SAD	43.822	200.329.668.784	6.670.672.295	07/1994	12/2021
SIH	CH CM ER RD RJ SP	26.065	61.502.025.163	2.726.203.028	01/1992	12/2021
SIM	DO DOE DOF DOI DOM	745	2.211.278.790	33.027.587	12/1996	12/2019
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	796	1.607.239.756	36.916.469	12/2000	12/2020
SINASC	DN DNR	760	3.545.635.104	94.238.796	12/1994	12/2019
SISPRENATAL	PN	944	232.410.320	5.591.213	01/2012	12/2014

Os dados originam-se do Sistema de Informações Ambulatoriais (SIA), sumarizados na tabela 2.3, e dados populacionais foram coletados a partir do sítio do Instituto Brasileiro de Geografia e Estatística (IBGE). O território foi caracterizado segundo o código IBGE do município e do gestor estadual. A população residente foi caracterizada segundo o sexo e faixa etária.

# sumario dos dados abertos do DATASUS
query=paste0("SELECT sg_subsistema as subsistema,
       count(distinct no_dbc) dbc, 
       sum(qt_bytes_dbc) bytes, 
       sum(qt_registros_dbc_local) registros,
       min(case when nu_competencia > 100 then nu_competencia else null end) mes_inicial,
       max(nu_competencia) mes_final
 FROM ", log, "
 where sg_sistema = 'SIA' 
   and qt_registros_dbc_local > 0
 group by 1
 order by registros desc ")
  
# formata decimal, milhar e data
kbsumariodbcsia=pg_roda_query(query)
kbsumariodbcsia[2:4]=formata(kbsumariodbcsia[2:4])
kbsumariodbcsia$mes_inicial=formata_mes(kbsumariodbcsia$mes_inicial)
kbsumariodbcsia$mes_final=formata_mes(kbsumariodbcsia$mes_final)
colnames(kbsumariodbcsia)=set_colnames(colnames(kbsumariodbcsia))

library(kableExtra)

kbl(kbsumariodbcsia, 
    align = c(rep("r",ncol(kbsumariodbcsia))),
    caption = "Sumário dos arquivos DBC segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)") %>%
    kable_classic()

Table 2.3: Sumário dos arquivos DBC segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)
subsistema	dbc	bytes	registros	mes inicial	mes final
PA	9.052	109.623.423.085	4.496.687.662	07/1994	12/2021
BI	4.536	71.020.065.629	1.743.856.391	01/2008	12/2021
AM	4.492	10.972.576.933	231.063.003	01/2008	12/2021
PS	2.910	1.725.606.746	92.333.908	11/2012	12/2021
AD	4.533	2.201.160.848	42.179.584	01/2008	12/2021
AQ	4.502	3.304.876.232	41.218.221	01/2008	12/2021
ATD	2.398	613.731.448	9.482.462	08/2014	12/2021
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.086	292.934.234	3.319.258	01/2008	12/2021
ACF	2.305	19.548.222	244.612	08/2014	12/2021
AB	544	14.622.114	211.252	01/2008	04/2017
AMP	308	1.579.644	17.529	03/2016	12/2021

Os arquivos disponibilizados via Sistema de Informações Ambulatoriais (SIA) são

PA: Produção Ambulatorial a qual corresponde o arquivo do “corpo” da APAC.
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
SAD Atenção Domiciliar.

As unidades da federação e códigos IBGE são:

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)
Tocantins - TO (17).

Ano com dois dígidos e mês de competência.

Os procedimentos de saúde do grupo medicamentos foram categorizados, respectivamente como, subgrupo e forma de organização, cuja lista é mantida via Sistema de Gerenciamento da Tabela de Procedimentos, Medicamentos e OPM do SUS (SIGTAP) pela Secretaria de Atenção Especializada à Saúde (SAES/MS). A tabela 2.4 lista os medicamentos biológicos considerados no presente estudo.

# sumario dos dados abertos do DATASUS
query=paste0("select sg_procedimento, no_procedimento, co_procedimento
             from bd_medicamento.td_medicamento where st_biologico = 'sim'")
  
# formata decimal, milhar e data
kbbiologicos=pg_roda_query(query)
colnames(kbbiologicos)=set_colnames(colnames(kbbiologicos))

library(kableExtra)

kbl(kbbiologicos, 
    caption = "Medicamentos biológicos listados no presente estudo") %>%
    kable_classic()

Table 2.4: Medicamentos biológicos listados no presente estudo
sg procedimento	no procedimento	co procedimento
ABAT125	ABATACEPTE 125 MG INJETÁVEL (POR SERINGA PREENCHIDA)	604320140
ABAT250	ABATACEPTE 250 MG INJETÁVEL (POR FRASCO AMPOLA).	604320124
ADAL40	ADALIMUMABE 40 MG INJETAVEL (POR SERINGA PREENCHIDA)	601010019 604380011 604380062 604380097
CERT200	CERTOLIZUMABE PEGOL 200 MG/ML INJETÁVEL (POR SERINGA PREENCHIDA)	604380070
CITR5	CITRATO DE TOFACITINIBE 5 MG (POR COMPRIMIDO)	604320159
ETAN25	ETANERCEPTE 25 MG INJETAVEL (POR FRASCO-AMPOLA)	601010027 604380020
ETAN50	ETANERCEPTE 50 MG INJETAVEL (POR FRASCO-AMPOLA OU SERINGA PREENCHIDA)	601010051 604380038
ETAN50A	ETANERCEPTE 50 MG INJETÁVEL (POR FRASCO-AMPOLA OU SERINGA PREENCHIDA)(BIOSSIMILAR A)	604380100
FING05	FINGOLIMODE 0.5 MG (POR CÁPSULA)	604320132
GOLI50	GOLIMUMABE 50 MG INJETÁVEL (POR SERINGA PREENCHIDA	604380089
INFL1010	INFLIXIMABE 10 MG/ML INJETAVEL (POR FRASCO-AMPOLA COM 10 ML)	601010035 601010043 604380046 604380054
RITU50050	RITUXIMABE 500 MG INJETÁVEL (POR FRASCO-AMPOLA DE 50 ML)	604680023
RITU50050A	RITUXIMABE 500 MG INJETÁVEL (POR FRASCO- AMPOLA DE 5O ML ) (BIOSSIMILAR A)	604680031
SECU150	SECUQUINUMABE 150 MG/ML SOLUÇÃO INJETÁVEL (POR SERINGA PREENCHIDA)	604690029
TOCI204	TOCILIZUMABE 20 MG/ML INJETAVEL (POR FRASCO-AMPOLA DE 4 ML)	604690010
USTEQ45	USTEQUINUMABE 45 MG/0,5ML SOLUÇÂO INJETÀVEL (POR SERINGA PREENCHIDA)	604690037
VEDO300	VEDOLIZUMABE 300 MG PÓ LIOFILIZADO PARA SOLUÇĂO INJETÁVEL	604320167

Os diagnósticos foram registrados conforme a Classificação Internacional de Doenças e Problemas Relacionados à Saúde (CID-10) segundo a subcategoria. O estabelecimento foi identificado segundo o Cadastro Nacional de Estabelecimentos de Saúde (CNES, SAES/MS).

2.2 Extração da coorte a partir do servidor local

Os dados completos do SIA PA e BI são explorados para identificar o percurso no SUS do usuário das tecnologias de saúde relacionadas.

O algoritmo realiza as seguintes estapas:

1 - Criar as tabelas vazias para armazenar os registros de dispensação relacionados 2 - Extrair os registros a partir dos códigos SIGTAP selecionados 3 - Obter a lista distinta de usuários do SUS 4 - Obter os demais registros dos usuários do SUS relacionados, excetuando os SIGTAP selecionados.

# codigo bash: 

# postgresql
dbase="banco de dados local"
us="usuário local"
pw="senha local"
pt="porta local"
hs="localhost"

# codigos SIGTAP de biologicos
co_procedimento="604320140, 604320124, 601010019, 604380011, 604380062, 604380097, 604380070, 604320159, 601010027, 604380020, 601010051, 604380038, 604380100, 604320132,  604380089, 601010035, 601010043, 604380046, 604380054, 604680031, 604680023, 604690029, 604690010, 604690037, 604320167"
apelido="bio"
# prefixo das tabelas
dt="$(date '+%Y%m%d_%H%M')_${apelido}"

# atributos
cp="nu_competencia,sg_uf,co_procedimento,co_cidpri,nu_cnspcn,nu_idade::int2,sg_sexo,qt_aprovada,co_cnes_estabelecimento,co_ibge_municipio_evento,co_ibge_municipio_residencia,nu_apac,co_cidsec"

Extração de procedimentos selecionados a partir da tabela PA.

# codigo bash: 

# tabela temporaria
query_create="CREATE TABLE public.tm_sia_${dt}_pa (
nu_competencia int4 NULL,
sg_uf varchar(2) NULL,
co_procedimento int8 default null,
co_cidpri varchar(4) default null, 
nu_usuariosus int8 NULL, 
nu_idade int2 NULL,
sg_sexo varchar(1) default null, 
qt_aprovada int8 NULL,
co_cnes int8 NULL,
co_municipio_ibge int4 NULL,
co_municipio_ibge_residencia int4 NULL,
nu_apac int8 NULL,
co_cidsec varchar(4) default null
);"

# executa o comando do PostgreSQL criar a tabela PA
echo $query_create | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

# lista dos arquivos SIA PA persistidos no servidor local
query="select
    table_schema || '.' || table_name
from
    information_schema.tables
where
    table_schema like 'bd_sus_sia_pa_%'
order by 1"

# insere os registros segundo o filtro de codigos SIGTAP
for tabela in $(echo $query | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t);do
   echo "----------- PA ----------------------"
   echo "insert into public.tm_sia_${dt}_pa select $cp from 
   $tabela 
   where co_procedimento in ($co_procedimento)" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

done

Extração de procedimentos selecionados a partir da tabela BI.

# codigo bash: 

# executa o comando do PostgreSQL criar a tabela BI
echo "CREATE TABLE public.tm_sia_${dt}_bi (like public.tm_sia_${dt}_pa);"| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

# lista dos arquivos SIA BI persistidos no servidor local
query="select
    table_schema || '.' || table_name
from
    information_schema.tables
where
    table_schema like 'bd_sus_sia_bi_%'
order by 1"

# insere os registros segundo o filtro de codigos SIGTAP
for tabela in $(echo $query | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t);do
   echo "---------- BI -----------------------"
   echo "insert into public.tm_sia_${dt}_bi select $cp from 
   $tabela 
   where co_procedimento in ($co_procedimento)" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

done

Extração de outros procedimentos da tabela PA dos usuários dos procedimentos selecionados.

# codigo bash: 

# lista os usuarios dos procedimentos relacionados
echo "drop table if exists public.tm_sia_${dt}_usuario; 
      create table public.tm_sia_${dt}_usuario as 
      select * from (SELECT DISTINCT nu_usuariosus from public.tm_sia_${dt}_bi 
      union 
      SELECT DISTINCT nu_usuariosus from public.tm_sia_${dt}_pa) x 
      where nu_usuariosus > 0" | 
      PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

# cria tabela para conter os demais registros PA dos usuarios listados
echo "CREATE TABLE public.tm_sia_${dt}_pa_outros 
       (like public.tm_sia_${dt}_pa);"| 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

# lista dos arquivos SIA PA persistidos no servidor local
query="select
    table_schema || '.' || table_name
from
    information_schema.tables
where
    table_schema like 'bd_sus_sia_pa_%'
order by 1"

# insere outros registros segundo a lista dos usuarios e filtro de codigos SIGTAP
for tabela in $(echo $query | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t);do
   echo "--------- PA OUTROS ------------------------"
   echo "insert into public.tm_sia_${dt}_pa_outros select $cp from 
   $tabela 
   where co_procedimento not in ($co_procedimento)
   and nu_cnspcn in (select * from public.tm_sia_${dt}_usuario)" | 
   PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

done

Extração de outros procedimentos da tabela BI dos usuários dos procedimentos selecionados.

# codigo bash: 

# cria tabela para conter os demais registros BI dos usuarios listados
echo "CREATE TABLE public.tm_sia_${dt}_bi_outros 
       (like public.tm_sia_${dt}_pa);"| 
       PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

# lista dos arquivos SIA BI persistidos no servidor local
query="select
    table_schema || '.' || table_name
from
    information_schema.tables
where
    table_schema like 'bd_sus_sia_bi_%'
order by 1"


# insere outros registros segundo a lista dos usuarios e filtro de codigos SIGTAP
for tabela in $(echo $query | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t);do
   echo "------- BI OUTROS --------------------------"
   echo "insert into public.tm_sia_${dt}_bi_outros select $cp from 
   $tabela 
   where co_procedimento not in ($co_procedimento) 
   and nu_cnspcn in (select * from public.tm_sia_${dt}_usuario) " | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

done

Identifica a idade minima de entrada na coorte.

# codigo bash: 

echo "alter table tm_sia_${dt}_usuario add nu_idade_min int2 default null;
update tm_sia_${dt}_usuario A
   set nu_idade_min = B.nu_idade_min
  from (select nu_usuariosus, min(nu_idade) nu_idade_min from tm_sia_${dt}_pa group by 1) B
  where A.nu_usuariosus = B.nu_usuariosus;" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

Identifica o sexo mais presente para o respectivo codigo de usuario do SUS.

# codigo bash: 

echo "alter table tm_sia_${dt}_usuario add sg_sexo char(1) default null;
update tm_sia_${dt}_usuario A
   set sg_sexo = B.sg_sexo
  from (select nu_usuariosus, 
       case when sg_sexo_f >= sg_sexo_m then 'F' else 'M' end as sg_sexo 
from (       
select nu_usuariosus, 
       sum(case when sg_sexo='F' then 1 else 0 end) as sg_sexo_f,
       sum(case when sg_sexo='M' then 1 else 0 end) as sg_sexo_m
  from tm_sia_${dt}_pa 
 group by 1 
)z) B
  where A.nu_usuariosus = B.nu_usuariosus;
"| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

Identifica o municipio de residencia mais presente para o respectivo codigo de usuario do SUS.

# codigo bash: 

echo "alter table tm_sia_${dt}_usuario add co_municipio_ibge_residencia int4 default null;
create view tm_sia_${dt}_usuario_municipio as 
select nu_usuariosus, 
       co_municipio_ibge_residencia,
       count(*) as qt_registros
  from tm_sia_${dt}_pa 
 group by 1 ,2;
update tm_sia_${dt}_usuario A
   set co_municipio_ibge_residencia = B.co_municipio_ibge_residencia
  from (
select A.*, B.co_municipio_ibge_residencia
from (
select nu_usuariosus, max(qt_registros) qt_registros from tm_sia_${dt}_usuario_municipio group by 1
) A
left join tm_sia_${dt}_usuario_municipio B
on A.nu_usuariosus = B.nu_usuariosus and A.qt_registros = B.qt_registros
) B
  where A.nu_usuariosus = B.nu_usuariosus;
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

Obtem o recorte dos usuarios que entraram na coorte com 18 anos ou menos.

# codigo bash: 

echo "create materialized view tm_sia_${dt}_usuario_18anos as
select nu_usuariosus from tm_sia_${dt}_usuario where nu_idade_min <=18;

create table tm_sia_${dt}_pa_18anos (LIKE tm_sia_${dt}_pa);
insert into tm_sia_${dt}_pa_18anos
select * 
  from tm_sia_${dt}_pa
 where nu_usuariosus in (select nu_usuariosus from tm_sia_${dt}_usuario_18anos)
order by nu_usuariosus, nu_competencia, co_procedimento;
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -e

Exporta as tabelas para o servidor na nuvem.

# codigo bash: 

# base de dados na nuvem
dbase2="base da nuvem"
us2="usuario da nuvem"
pw2="senha da nuvem"
pt2="porta da nuvem"
hs2="host da nuvem"

# elimina tabelas anteriores
echo "drop table if exists 
      tm_sia_${dt}_pa, 
      tm_sia_${dt}_bi, 
      tm_sia_${dt}_pa_outros, 
      tm_sia_${dt}_bi_outros"  | 
      PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

# cria estrutura das tabelas no servidor da nuvem
echo $query_create  | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e
echo "CREATE TABLE tm_sia_${dt}_bi (like public.tm_sia_${dt}_pa);
      CREATE TABLE tm_sia_${dt}_pa_outros (like public.tm_sia_${dt}_pa);
      CREATE TABLE tm_sia_${dt}_bi_outros (like public.tm_sia_${dt}_pa);
      CREATE TABLE tm_sia_${dt}_usuario_18anos (like public.tm_sia_${dt}_pa);" | 
      PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e


# exporta dados do servidor local para o da nuvem
echo "select * from public.tm_sia_${dt}_pa" | 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t | 
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2  -c \
"COPY public.tm_sia_${dt}_pa FROM STDIN quote '\"' delimiter ',' csv " 

echo "select * from public.tm_sia_${dt}_bi" | 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t | 
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2  -c \
"COPY public.tm_sia_${dt}_bi FROM STDIN quote '\"' delimiter ',' csv " 

echo "select * from public.tm_sia_${dt}_pa_outros" | 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t | 
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2  -c \
"COPY public.tm_sia_${dt}_pa_outros FROM STDIN quote '\"' delimiter ',' csv " 

echo "select * from public.tm_sia_${dt}_bi_outros" |
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t | 
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2  -c \
"COPY public.tm_sia_${dt}_bi_outros FROM STDIN quote '\"' delimiter ',' csv " 

echo "select * from tm_sia_${dt}_usuario_18anos" | 
PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t | 
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2  -c \
"COPY tm_sia_${dt}_usuario_18anos FROM STDIN quote '\"' delimiter ',' csv " 


# atualiza privilegios
echo "GRANT SELECT ON ALL TABLES IN SCHEMA public TO usuario;" |
PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

Adiciona a sigla do procedimento sigtap. A mesma sigla eh adotada para codigos SIGTAP diferentes.

# codigo bash: 

echo "alter table tm_sia_${dt}_pa 
        add no_sigtap varchar(255) NULL, add sg_sigtap varchar(20) null;
      alter table tm_sia_${dt}_pa_outros 
        add no_sigtap varchar(255) NULL, add sg_sigtap varchar(20) null;
      alter table tm_sia_${dt}_bi 
        add no_sigtap varchar(255) NULL, add sg_sigtap varchar(20) null;
      alter table tm_sia_${dt}_bi_outros 
        add no_sigtap varchar(255) NULL, add sg_sigtap varchar(20) null;" | 
        PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e


echo "
update tm_sia_${dt}_pa A
   set no_sigtap = B.no_sigtap,
sg_sigtap = B.sg_sigtap
  from bd_geral.td_sigtap B
 where A.co_procedimento = B.co_sigtap ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

echo "
update tm_sia_${dt}_pa_outros A
   set no_sigtap = B.no_sigtap,
sg_sigtap = B.sg_sigtap
  from bd_geral.td_sigtap B
 where A.co_procedimento = B.co_sigtap ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

echo "
update tm_sia_${dt}_bi A
   set no_sigtap = B.no_sigtap,
sg_sigtap = B.sg_sigtap
  from bd_geral.td_sigtap B
 where A.co_procedimento = B.co_sigtap ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

echo "
update tm_sia_${dt}_bi_outros A
   set no_sigtap = B.no_sigtap,
sg_sigtap = B.sg_sigtap
  from bd_geral.td_sigtap B
 where A.co_procedimento = B.co_sigtap ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

echo "
update tm_sia_${dt}_pa A
   set no_sigtap = B.no_procedimento,
sg_sigtap = B.sg_procedimento
  from bd_geral.tf_sigtap_medicamento B
 where A.co_procedimento = B.co_sigtap_procedimento ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

echo "
update tm_sia_${dt}_pa_outros A
   set no_sigtap = B.no_procedimento,
sg_sigtap = B.sg_procedimento
  from bd_geral.tf_sigtap_medicamento B
 where A.co_procedimento = B.co_sigtap_procedimento ;
" | PGPASSWORD=$pw2 psql -U $us2 -h $hs2 -d $dbase2 -p $pt2 -e

2.3 Processamento

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).

Foram escritos painéis com a ferramenta Shiny, do projeto R, a qual adota conceitos do tema Bootstrap de visualização. O painel analítico foi composto de indicadores na forma de cartões de visualização com tabela, histograma, série temporal.

Os registros foram selecionados segundo o diagnóstico primário e código de procedimento SIGTAP entre 2009 e março de 2021. Foram selecionados usuários residentes segundo o sexo (‘F,’‘M’) e idade entre 0 e 130, a partir de informações prestadas pela Secretaria Estadual de Saúde - SES, cujo critério de quantidade aprovada contemplou todos os registros > 0.

Os comandos a seguir, em linguagem bash, nativa dos sistemas operalcionais Linux, são executados para listagem e extração da coorte segundo o critério inicial do código SIGTAP.

3 Resultados

3.1 Extração do repositório SIA

A tabela 3.1 resume o conteúdo disponível na Sala Aberta de Inteligência em Saúde - SABEIS relativo ao SIA processado.

# sumario dos dados abertos do DATASUS
query=paste0("SELECT sg_subsistema as subsistema,
       count(distinct no_dbc) dbc, 
       sum(qt_bytes_dbc) bytes, 
       sum(qt_registros_dbc_local) registros,
       min(case when nu_competencia > 100 then nu_competencia else null end) mes_inicial,
       max(nu_competencia) mes_final
 FROM ", log, "
 where sg_sistema = 'SIA' 
   and qt_registros_dbc_local > 0
   and sg_subsistema in ('PA','AM','AQ','AN','AR','BI','PS')
   and nu_ano >= 2008
 group by 1
 order by registros desc")
  

kbsumariodbcsiaprocessado=pg_roda_query(query)

# valores totais
query=paste0(" SELECT 'total' as subsistema,
       count(distinct no_dbc) dbc, 
       sum(qt_bytes_dbc) bytes, 
       sum(qt_registros_dbc_local) registros,
       min(case when nu_competencia > 100 then nu_competencia else null end) mes_inicial,
       max(nu_competencia) mes_final
 FROM ", log, "
 where sg_sistema = 'SIA' 
   and qt_registros_dbc_local > 0
   and sg_subsistema in ('PA','AM','AQ','AN','AR','BI','PS')
   and nu_ano >= 2008
 group by 1
 order by registros desc
")

kbsumariodbcsiatotal=pg_roda_query(query)


kbsumariodbcsiaprocessado=rbind(
  kbsumariodbcsiaprocessado,
  kbsumariodbcsiatotal
)

# formata decimal, milhar e data
kbsumariodbcsiaprocessado[2:4]=formata(kbsumariodbcsiaprocessado[2:4])
kbsumariodbcsiaprocessado$mes_inicial=formata_mes(kbsumariodbcsiaprocessado$mes_inicial)
kbsumariodbcsiaprocessado$mes_final=formata_mes(kbsumariodbcsiaprocessado$mes_final)

# extrai valores totais formatados
kbsumariodbcsiatotal=kbsumariodbcsiaprocessado[kbsumariodbcsiaprocessado$subsistema=='total',]

colnames(kbsumariodbcsiaprocessado)=set_colnames(colnames(kbsumariodbcsiaprocessado))

library(kableExtra)

kbl(kbsumariodbcsiaprocessado, 
    align = c(rep("r",ncol(kbsumariodbcsiaprocessado))),
    caption = "Sumário dos arquivos DBC processados na Sala Aberta de Inteligência em Saúde - SABEIS segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)") %>%
    kable_classic()   %>%
    footnote(
    general = paste(
      "Dados a partir de 2008."
    )
  )

Table 3.1: Sumário dos arquivos DBC processados na Sala Aberta de Inteligência em Saúde - SABEIS segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)
subsistema	dbc	bytes	registros	mes inicial	mes final
PA	4.679	103.598.573.970	3.900.270.976	01/2008	12/2021
BI	4.536	71.020.065.629	1.743.856.391	01/2008	12/2021
AM	4.492	10.972.576.933	231.063.003	01/2008	12/2021
PS	2.910	1.725.606.746	92.333.908	11/2012	12/2021
AQ	4.502	3.304.876.232	41.218.221	01/2008	12/2021
AN	2.145	458.824.790	6.534.272	01/2008	10/2014
AR	4.086	292.934.234	3.319.258	01/2008	12/2021
total	27.350	191.373.458.534	6.018.596.029	01/2008	12/2021
Note:
Dados a partir de 2008.

O dataSUS disponibilizou, em março/2022, 6.018.596.029 registros em 27.350 arquivos DBC com dados de 01/2008 à 12/2021. Foram identificados 27 arquivos discrepantes (PAAC2112.dbc PAAL2112.dbc PAAM2112.dbc PAAP2112.dbc PABA2112.dbc PACE2112.dbc PADF2112.dbc PAES2112.dbc PAGO2112.dbc PAMA2112.dbc PAMG2112.dbc PAMS2112.dbc PAMT2112.dbc PAPA2112.dbc PAPB2112.dbc PAPE2112.dbc PAPI2112.dbc PAPR2112.dbc PARJ2112.dbc PARN2112.dbc PARO2112.dbc PARR2112.dbc PARS2112.dbc PASC2112.dbc PASE2112.dbc PASP2112a.dbc PASP2112b.dbc PASP2112c.dbc PATO2112.dbc), isto é, com mais de 1% de variação, representando, no conjunto, 2.911.785 registros.

Verificou-se 11.615.837.021 de quantidade aprovada persistida na SABEIS, enquanto, no tabnet, o valor foi de 11.541.893.541 (0,6%).

3.2 Processamento

A tabela 3.2 resume o volume de atendimentos relacionados aos medicamentos registrados no SIA.

# sumario dos dados abertos do DATASUS
query=paste0("select round(nu_competencia::decimal/100)::text ano ,
       round(sum(qt_usuariosus_medicamento)/12) usuários_ao_mes,
       sum(qt_registros_medicamento) dispensações,
       sum(qt_aprovada_medicamento) unidades,
       sum(qt_aprovada_tabnet) unidades_no_tabnet,
       100-round(sum(qt_aprovada_medicamento)::decimal/sum(qt_aprovada_tabnet)*100,1) percentual
  from bd_medicamento.tf_coleta_medicamento 
 group by 1
 union
 select 'total' ano ,
       round(sum(qt_usuariosus_medicamento)/12) usuários_ao_mes,
       sum(qt_registros_medicamento) dispensações,
       sum(qt_aprovada_medicamento) unidades,
       sum(qt_aprovada_tabnet) unidades_no_tabnet,
       100-round(sum(qt_aprovada_medicamento)::decimal/sum(qt_aprovada_tabnet)*100,1)
  from bd_medicamento.tf_coleta_medicamento 
 group by 1
 order by 1")
  
kbprocessamento=pg_roda_query(query)

# formata decimal, milhar e data
kbprocessamento[2:5]=formata(kbprocessamento[2:5])
kbprocessamento$percentual=formata(kbprocessamento$percentual)

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

library(kableExtra)

kbl(kbprocessamento, 
    align = c(rep("r",ncol(kbprocessamento))),
    caption = "Sumário anual dos usuários, dispensações e unidades farmacotécnicas  processadas na Sala Aberta de Inteligência em Saúde - SABEIS segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)") %>%
    kable_classic()

Table 3.2: Sumário anual dos usuários, dispensações e unidades farmacotécnicas processadas na Sala Aberta de Inteligência em Saúde - SABEIS segundo o sistema de informação do Sistema de Informações Ambulatoriais (SIA)
ano	usuários ao mes	dispensações	unidades	unidades no tabnet	percentual
2008	698.734	11.254.430	531.971.227	534.784.455	0,5
2009	812.231	13.283.721	633.489.233	634.564.790	0,2
2010	796.544	12.797.876	617.829.441	619.586.472	0,3
2011	886.837	14.137.231	659.564.631	659.586.655	0,0
2012	969.459	15.877.469	722.674.241	723.246.845	0,1
2013	1.003.520	16.500.303	734.808.232	736.095.454	0,2
2014	1.140.760	19.036.599	815.769.214	817.056.265	0,2
2015	1.238.775	20.832.059	838.670.041	840.185.315	0,2
2016	1.205.815	19.719.557	862.737.054	865.622.710	0,3
2017	1.283.544	21.089.371	924.031.162	925.916.483	0,2
2018	1.321.852	21.804.066	972.957.989	975.316.986	0,2
2019	1.439.017	23.917.563	1.020.101.583	1.022.200.782	0,2
2020	1.530.392	25.507.262	1.124.959.611	1.127.084.548	0,2
2021	1.433.127	27.055.425	1.156.273.362	1.060.645.781	-9,0
total	15.760.606	262.812.932	11.615.837.021	11.541.893.541	-0,6

3.3 Análise

query="select count(*) registro,
       count(distinct nu_usuariosus) usuario,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'F' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) feminino,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'M' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) masculino,
       count(distinct nu_usuariosus) usuario,
       round(avg(nu_idade)) idade,
       round(stddev(nu_idade)) idadesd, 
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max,
       count(distinct co_ibge_municipio_residencia) residencia,
       count(distinct co_ibge_municipio_evento) atendimento
  from bd_sabeis.tf_diretriz_artrite_reumatoide
where sg_procedimento in (select sg_procedimento from bd_geral.td_procedimento_medicamento where st_biologico = 'sim')"

sumarioprecoorte=pg_roda_query(query)

sumarioprecoorte$registro=formata(sumarioprecoorte$registro)
sumarioprecoorte$usuario=formata(sumarioprecoorte$usuario)
sumarioprecoorte$residencia=formata(sumarioprecoorte$residencia)
sumarioprecoorte$atendimento=formata(sumarioprecoorte$atendimento)

sumarioprecoorte$nu_competencia_min=formata_mes(sumarioprecoorte$nu_competencia_min)
sumarioprecoorte$nu_competencia_max=formata_mes(sumarioprecoorte$nu_competencia_max)

Na formação da pré-coorte foram extraídos, entre 01/2016 e 11/2021, 3.735.046 de dispensações para 134.609 usuários do SUS, sendo 82% do sexo feminino e 18% do sexo masculino, com média de idade de 54 (desvio padrão de 15), os quais residiam em 4.840 municípios, tendo sido atendidos em 187 municípios.

# executar uma vez
query="drop table if exists bd_sabeis.tm_diretriz_artrite_reumatoide;  
create table bd_sabeis.tm_diretriz_artrite_reumatoide (like bd_sabeis.tf_diretriz_artrite_reumatoide including all); 
alter table bd_sabeis.tm_diretriz_artrite_reumatoide add co_seq_dispensacao int2 default 0;
insert into bd_sabeis.tm_diretriz_artrite_reumatoide
select * ,
       dense_rank () 
       over (
              partition by nu_usuariosus
              order by nu_competencia
              ) as co_seq_dispensacao
  from bd_sabeis.tf_diretriz_artrite_reumatoide
where nu_usuariosus not in (
 select distinct nu_usuariosus 
   from bd_sabeis.tf_diretriz_artrite_reumatoide
  where nu_competencia not between 201607 and 202106
  and nu_usuariosus > 0
  ) 
  and nu_usuariosus > 0
 and sg_procedimento in (select sg_procedimento from bd_geral.td_procedimento_medicamento where st_biologico = 'sim');
  
alter table bd_sabeis.tm_diretriz_artrite_reumatoide add st_excluido int2 default 0;
 
 update bd_sabeis.tm_diretriz_artrite_reumatoide A
 set st_excluido = 1
 from (
 select nu_usuariosus, nu_competencia
 from (
 select A.nu_usuariosus,
        A.nu_competencia,
        to_date(B.nu_competencia::text || '28','yyyymmdd')-to_date(A.nu_competencia::text || '01','yyyymmdd') dias
   from bd_sabeis.tm_diretriz_artrite_reumatoide A,
        bd_sabeis.tm_diretriz_artrite_reumatoide B
  where A.nu_usuariosus=B.nu_usuariosus
    and A.co_seq_dispensacao=B.co_seq_dispensacao-1
 ) a where dias > 180
 ) B
 where A.nu_usuariosus = B.nu_usuariosus
   and A.nu_competencia > B.nu_competencia;

 update bd_sabeis.tm_diretriz_artrite_reumatoide A
 set st_excluido = 1
 from (
 select nu_usuariosus, sg_procedimento
 from (
 select nu_usuariosus, sg_procedimento,
        count(*) dispensacao
   from bd_sabeis.tm_diretriz_artrite_reumatoide 
   group by 1,2
 ) a where dispensacao <= 3
 ) B
 where A.nu_usuariosus = B.nu_usuariosus 
   and A.sg_procedimento=B.sg_procedimento;
  
delete from  bd_sabeis.tm_diretriz_artrite_reumatoide where st_excluido = 1;
 
alter table bd_sabeis.tm_diretriz_artrite_reumatoide DROP st_excluido;
"

query="select count(*) registro,
       count(distinct nu_usuariosus) usuario,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'F' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) feminino,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'M' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) masculino,
       count(distinct nu_usuariosus) usuario,
       round(avg(nu_idade)) idade,
       round(stddev(nu_idade)) idadesd, 
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max,
       count(distinct co_ibge_municipio_residencia) residencia,
       count(distinct co_ibge_municipio_evento) atendimento
  from bd_sabeis.tm_diretriz_artrite_reumatoide;
"

sumariocoorte=pg_roda_query(query)

sumariocoorte$registro=formata(sumariocoorte$registro)
sumariocoorte$usuario=formata(sumariocoorte$usuario)
sumariocoorte$residencia=formata(sumariocoorte$residencia)
sumariocoorte$atendimento=formata(sumariocoorte$atendimento)

sumariocoorte$nu_competencia_min=formata_mes(sumariocoorte$nu_competencia_min)
sumariocoorte$nu_competencia_max=formata_mes(sumariocoorte$nu_competencia_max)

Na formação da coorte de incidentes foram removidos os usuários com registros anteriores a 2016/06, considerando-se, portanto, seis meses de período sem uso de biológico como critério de inclusão. Foram excluídos usuários com registros posteriores a 2021/07, observando-se apenas usuários com encerramento do tratamento. O critério de descontinuidade do tratamento foi definida em 180 dias de intervalo entre duas dispensações, ainda que tenha havido trocas. Foram exluídos tratamentos com menos de três dispensações. Na coorte foram extraídos, entre 07/2016 e 06/2021, 182.713 de dispensações para 13.718 usuários do SUS, sendo 80% do sexo feminino e 20% do sexo masculino, com média de idade de 52 (desvio padrão de 17), os quais residiam em 2.152 municípios, tendo sido atendidos em 173 municípios.

query="select count(*) registro,
       count(distinct nu_usuariosus) usuario,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'F' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) feminino,
       round(count(distinct case when sg_sexo_primeiro_tratamento = 'M' then nu_usuariosus else null end)::decimal/count(distinct nu_usuariosus)*100) masculino,
       count(distinct nu_usuariosus) usuario,
       round(avg(nu_idade)) idade,
       round(stddev(nu_idade)) idadesd, 
       min(nu_competencia) as nu_competencia_min,
       max(nu_competencia) as nu_competencia_max,
       count(distinct co_ibge_municipio_residencia) residencia,
       count(distinct co_ibge_municipio_evento) atendimento
  from bd_sabeis.tf_diretriz_artrite_reumatoide"

sumarioprecoorte=pg_roda_query(query)

sumarioprecoorte$registro=formata(sumarioprecoorte$registro)
sumarioprecoorte$usuario=formata(sumarioprecoorte$usuario)
sumarioprecoorte$residencia=formata(sumarioprecoorte$residencia)
sumarioprecoorte$atendimento=formata(sumarioprecoorte$atendimento)

sumarioprecoorte$nu_competencia_min=formata_mes(sumarioprecoorte$nu_competencia_min)
sumarioprecoorte$nu_competencia_max=formata_mes(sumarioprecoorte$nu_competencia_max)

# sumario dos dados abertos do DATASUS
query=paste0("select 
         sg_procedimento,
         count(*) usuario,
         round(avg(dispensações)) dispensações_media,
         round(stddev(dispensações)) dispensações_sd,
         round(max(dispensações)) dispensações_max,
         round(avg(dias)) dias_media,
         round(stddev(dias)) dias_sd,
         round(max(dias)) dias_max
  from (
  select nu_usuariosus, sg_procedimento,
         MIN(round(nu_competencia::decimal/100)) as ano,
         count(*) dispensações,
         count(distinct nu_competencia) meses,
         max(to_date(nu_competencia::text || '28','yyyymmdd'))-min(to_date(nu_competencia::text || '01','yyyymmdd')) as dias,
         count(distinct sg_procedimento) medicamento
    from bd_sabeis.tm_diretriz_artrite_reumatoide
   group by 1,2
   )x
  group by 1
  order by 1")
  
perfilmedicamento=pg_roda_query(query)

# formata decimal, milhar e data
perfilmedicamento$usuario=formata(perfilmedicamento$usuario)
perfilmedicamento$dias_max=formata(perfilmedicamento$dias_max)

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

library(kableExtra)

kbl(perfilmedicamento, 
    align = c(rep("r",ncol(perfilmedicamento))),
    caption = "Sumário dos tratamentos aderentes de medicamentos biológicos para artrite reumatoide") %>%
    kable_classic()

Table 3.3: Sumário dos tratamentos aderentes de medicamentos biológicos para artrite reumatoide
sg procedimento	usuario	dispensações media	dispensações sd	dispensações max	dias media	dias sd	dias max
ABAT125	457	11	7	44	395	279	1.488
ABAT250	559	14	10	56	475	352	1.762
ADAL40	5.020	13	10	59	451	337	1.792
CERT200	1.463	12	9	56	416	302	1.730
CITR5	653	9	5	25	325	183	878
ETAN25	198	13	9	44	494	347	1.639
ETAN50	2.679	14	10	58	490	358	1.792
ETAN50A	130	6	2	11	192	55	300
GOLI50	1.388	14	10	58	483	363	1.823
INFL1010	850	9	7	48	428	294	1.823
RITU50050	667	2	2	21	113	149	1.123
TOCI204	928	12	9	52	437	322	1.670

A tabela 3.3 resume o perfil de utilização de medicamentos do usuários considerados aderentes.

library("survival")
library("survminer")

# saida: censoring status 1=censored, 2=dead

# sumario dos dados abertos do DATASUS
query=paste0("select 
         nu_usuariosus, 
         sg_procedimento,
         1 as saida,
         MIN(round(nu_competencia::decimal/100)) as ano,
         count(*) dispensações,
         count(distinct nu_competencia) meses,
         max(to_date(nu_competencia::text || '28','yyyymmdd'))-min(to_date(nu_competencia::text || '01','yyyymmdd')) as dias,
         count(distinct sg_procedimento) medicamento
    from bd_sabeis.tm_diretriz_artrite_reumatoide
    where sg_procedimento in ('RITU50050','INFL1010')
   group by 1,2,3")
  
kaplanmeier=pg_roda_query(query)


fit <- survfit(Surv(dias, saida) ~ sg_procedimento, data = kaplanmeier)
print(fit)

## Call: survfit(formula = Surv(dias, saida) ~ sg_procedimento, data = kaplanmeier)
## 
##                             n events median 0.95LCL 0.95UCL
## sg_procedimento=INFL1010  850    850    361     331     362
## sg_procedimento=RITU50050 667    667     57      27      57

ggsurvplot(fit,
          pval = TRUE, conf.int = TRUE,
          risk.table = TRUE, # Add risk table
          risk.table.col = "strata", # Change risk table color by groups
          linetype = "strata", # Change line type by groups
          surv.median.line = "hv", # Specify median survival
          ggtheme = theme_bw(), # Change ggplot2 theme
          palette = c("#E7B800", "#2E9FDF"))

A figura ?? mostra uma análise de sobrevivência no tratamento de adalimumabe em comparação ao etanercepte.

4 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 visualizaçã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.

O presente trabalho fornece insumos de ciência de dados para que a equipe que presta auxílio à CONITEC automatize a construção de coortes e crie rotinas de monitoramento de usuários aderentes e não aderentes.

Apêndice

4.1 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)

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)
}

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
  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
  # linha: ano/mes processamento
  # Coluna: Unidade da federação
  # Conteúdo: Qtd.aprovada
  # Períodos disponíveis todos
  # Prodecimento: 06...
  
  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)
# 
# pg_roda_query("GRANT ALL ON TABLE 
#               bd_medicamento.tf_medicamento_preco TO conitec;")
# 
# 
# }



pg_preco_medicamento <- function() {
  url="https://docs.google.com/spreadsheets/d/1xw50Vlsdgko3T7JBG9Qm0Lc1X2PHNAQ4/edit?usp=sharing&ouid=109427081976027472278&rtpof=true&sd=true"
  ds=as.data.frame(gsheet2tbl(url, sheetid = "valor_medicamento"))
  
  # remove registros sem valor atribuido
  ds=ds[ds$vl_unitario>0,]
  
  # elimina colunas vazias
  ds$co_seq_procedimento = NULL
  ds$co_procedimento = NULL
  ds$sg_procedimento = NULL
  
  # formata data da compra
  ds$dt_compra=as.Date(ds$dt_compra, format = "%m/%d/%Y")
  
  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_procedimento4 as 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,
    A.vl_unitario 
from
    (
    select
        dense_rank () over (partition by sg_procedimento4
    order by
        dt_compra::date) co_seq_abrev_data ,
        *
    from
        (
        select
            distinct sg_procedimento4,
            dt_compra::date,
            vl_unitario
        from
            bd_medicamento.tm_medicamento_preco ) x
    order by
        sg_procedimento4 ,
        dt_compra::date ) A
left join (
    select
        dense_rank () over (partition by sg_procedimento4
    order by
        dt_compra::date) co_seq_abrev_data,
        *
    from
        (
        select
            distinct sg_procedimento4,
            dt_compra::date,
            vl_unitario
        from
            bd_medicamento.tm_medicamento_preco ) x
    order by
        sg_procedimento4 ,
        dt_compra::date ) B on
    A.sg_procedimento4 = B.sg_procedimento4
    and A.co_seq_abrev_data = B.co_seq_abrev_data-1")
pg_roda_query(query)

pg_roda_query("CREATE INDEX tf_medicamento_preco_sg_procedimento_idx ON
              bd_medicamento.tf_medicamento_preco (sg_procedimento);")


pg_roda_query("GRANT ALL ON TABLE 
              bd_medicamento.tf_medicamento_preco TO conitec;")

# 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]))

4.2 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
)
}

4.3 Funções bash para extração, transformação e carga (ETL)


  echo "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,
    vl_total decimal(21,2) NULL
);" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt


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_%') order by 1"

for tabela in $(echo $query | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt --csv -t);do

  cp=$(echo $tabela | awk -F'.' '{print substr($2,5,6)}')
  uf=$(echo $tabela | awk -F'.' '{print toupper(substr($2,3,2))}')

   echo $tabela

   echo "insert into bd_medicamento.tf_procedimento_uf_mes
select $cp nu_competencia
      , '$uf' as 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 A
  where qt_aprovada > 0
  group by 1,2,3
  order by 3
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -q

done


  echo "alter TABLE bd_medicamento.tf_procedimento_uf_mes
add sg_procedimento varchar(25) default null
-- ,add vl_total decimal(21,2) default null
 " | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

echo "CREATE INDEX tf_procedimento_uf_mes_co_procedimento_idx ON bd_medicamento.tf_procedimento_uf_mes (co_procedimento);
CREATE INDEX tf_procedimento_uf_mes_sg_procedimento_idx ON bd_medicamento.tf_procedimento_uf_mes (sg_procedimento);
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "update bd_medicamento.tf_procedimento_uf_mes A
  set sg_procedimento = M.sg_procedimento
from bd_medicamento.td_medicamento M
    where A.co_procedimento::Text::tsquery @@ M.co_procedimento::tsvector
 ;" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

echo "CREATE INDEX tf_procedimento_mes_co_procedimento_idx ON bd_medicamento.tf_procedimento_mes (co_procedimento);
CREATE INDEX tf_procedimento_mes_sg_procedimento_idx ON bd_medicamento.tf_procedimento_mes (sg_procedimento);
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "update bd_medicamento.tf_procedimento_uf_mes A
  set vl_total = round(qt_aprovada * vl_unitario,2)
from bd_medicamento.tf_medicamento_preco P
    where A.sg_procedimento = P.sg_procedimento
   and to_date(A.nu_competencia::text || '15', 'yyyymmdd')
   between dt_inicio and dt_fim
 ;" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "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
  from bd_medicamento.tf_procedimento_uf_mes A
  where qt_aprovada > 0
  group by 1,2
  order by 1,2" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -q

  echo "GRANT ALL ON SCHEMA bd_medicamento TO conitec;
 GRANT SELECT ON ALL TABLES IN SCHEMA bd_medicamento TO conitec;" |
 PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt -q

  echo "alter TABLE bd_medicamento.tf_procedimento_mes
add qt_aprovada_tabnet int8 default 0,
add sg_procedimento varchar(25) default null,
add vl_total decimal(21,2) default null
 " | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "
  update bd_medicamento.tf_procedimento_mes A
     set qt_aprovada_tabnet = B.qt_aprovada_tabnet
  from bd_medicamento.tf_tabnet_procedimento B
  where A.co_procedimento = B.co_procedimento
  and A.nu_competencia = B.nu_competencia
  and B.no_procedimento <> 'TOTAL'
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "update bd_medicamento.tf_procedimento_mes A
  set sg_procedimento = M.sg_procedimento
from bd_medicamento.td_medicamento M
    where A.co_procedimento::Text::tsquery @@ M.co_procedimento::tsvector
 ;" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

  echo "update bd_medicamento.tf_procedimento_mes A
  set vl_total = round(qt_aprovada * vl_unitario,2)
from bd_medicamento.tf_medicamento_preco P
    where A.sg_procedimento = P.sg_procedimento
   and to_date(A.nu_competencia::text || '15', 'yyyymmdd')
   between dt_inicio and dt_fim
 ;" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt
 
 
 echo "alter table bd_medicamento.tf_procedimento_uf_mes 
add vl_total_ipca decimal(21,2) default 0;
 
update bd_medicamento.tf_procedimento_uf_mes 
   set vl_total_ipca = vl_total;
  
update bd_medicamento.tf_procedimento_uf_mes A
   set vl_total_ipca = vl_total_ipca * vl_fator_correcao
  from bd_geral.td_ipca B
 where A.nu_competencia = B.nu_competencia;  
"| PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt
 
echo "alter table bd_medicamento.tf_procedimento_mes 
add vl_total_ipca decimal(21,2) default 0;
 
update bd_medicamento.tf_procedimento_mes 
   set vl_total_ipca = vl_total;
  
update bd_medicamento.tf_procedimento_mes A
   set vl_total_ipca = vl_total_ipca * vl_fator_correcao
  from bd_geral.td_ipca B
 where A.nu_competencia = B.nu_competencia;  
" | PGPASSWORD=$pw psql -U $us -h $hs -d $dbase -p $pt

4.4 Funções para análise descritiva e preditiva

4.5 Função bash para cálculo do fotor do IPCA em relação ao último mês disponível

cmpn=$(lynx -dump http://api.sidra.ibge.gov.br/values/h/n/t/1737/p/last/n1/all/v/63 | grep '"D1C":' | awk -F'"' '{print $(NF-1)}')

# baixa o ipca
for cmp in $(seq $cmp0 $cmpn | egrep "01$|02$|03$|04$|05$|06$|07$|08$|09$|10$|11$|12$");do
  ipca=$(lynx -dump http://api.sidra.ibge.gov.br/values/h/n/t/1737/p/$cmp/n1/all/v/2266 | grep '"V":' | awk -F'"' '{print $(NF-1)}')
  echo "$cmp,$ipca" 
done | tac > ipca.tmp # cmp
   
# calcula o fator acumulado para cmpn+1
ipca=$(head -1 ipca.tmp | awk -F',' '{print $2}')
echo "$cmpn,$ipca,1" > ipca.csv # primeira linha cmp,ipca,acumulado,um_real   

for line in $(cat ipca.tmp | sed 1d ); do
  cmp=$(echo $line | awk -F',' '{print $1}')
  ipca2=$(echo $line | head -1  | awk -F',' '{print $2}')
  val=$(echo "scale=4; $ipca / $ipca2  " | bc)
echo "$cmp,$ipca2,$val" >> ipca.csv
done # linie