DataSUS date formatting issue

[guilhermejacob]

@ajdamico, when I run
dbGetQuery( mdb_src$con , "SELECT RIGHT( cast( dtobito as text ) , 4 ) as ano , COUNT(*) from geral_cid10 GROUP BY ano order by ano" )
it returns:

    ano      L5
1  +004       1
2  +006  615147
3  +007 1467989
4  1996  908883
5  1997  903516
6  1998  931895
7  1999  938658
8  2000     496
9  2001  961492
10 2002  982807
11 2003 1002340
12 2004 1024073
13 2005 1006827
14 2006 1031691
15 2007 1047824
16 2008 1077007
17 2009 1103088
18 2011 1170498
19 2012 1181166
20 2013 1210474
21 2014 1227039

The first 3 lines and the year 2000 are wrong, as you can see in http://tabnet.datasus.gov.br/cgi/tabcgi.exe?sim/cnv/obt10uf.def, when you select Linha: Ano do óbito and all years.

can you tell me why?

[ajdamico]

what's the smallest catalog that will create mdb_src? what rows (as few as possible) can i subset this_cat <- get_catalog( "datasus" , output_dir = tempdir() ) to in order to reproduce this? thanks

[guilhermejacob]

Try this:

library(lodown)
library(DBI)
library(MonetDBLite)

catalog <- get_catalog( "datasus" , output_dir = tempdir() )

catalog <- subset( catalog , year %in% 1999:2000 )
catalog <- subset( catalog , type == "mortality" )
catalog <- subset( catalog , special == "infantil" )

lodown( "datasus" , catalog = catalog )

path_to_db = paste0( tempdir() , "/MonetDB")
db <- dbConnect( MonetDBLite() , path_to_db )
dbListTables(db)
dbGetQuery( db , "SELECT RIGHT( cast( dtobito as text ) , 4 ) as ano , COUNT(*) from infantil_cid10 GROUP BY ano order by ano" )

[ajdamico]

nice. thanks.

library(DBI)

db <- dbConnect( MonetDBLite::MonetDBLite() )

x <- data.frame( col = c( 10000000 , 1000 ) )

dbWriteTable( db , 'x' , x )

# CAST( ... AS TEXT ) includes exponentials
dbGetQuery( db , "SELECT CAST( col AS TEXT ) FROM x" )

dbGetQuery( db , "SELECT RIGHT( CAST( col AS TEXT ) , 4 ) FROM x" )

# easy workaround
dbGetQuery( db , "SELECT RIGHT( CAST( CAST( col AS BIGINT ) AS TEXT ) , 4 ) FROM x" )

[ajdamico]

sure, i think it's safer to use dbListFields( db , tablename ) instead of digging into sys tables though?