Data query with odbc: from R to Stata

[Xavier Pedros]

Hi all!

I just got access to a SQL Server, where there is a database storing a number of metrics, that I can connect to to download them. I managed to correctly connect, using my credentials, but I am having some issues in understanding how I can request a specific metric. I understand that I need to define a number of attributes, however I don't know how I can program that into Stata.

I have the following R code, which applied to the same database, pulls out a certain metric - see below. My question is, how does this translate into the odbc package?

R Code:

 
## Request data by country
'select      zd.date,
zd.val,
a.name,
z.name

from  wi_master.dbo.zone_data_view_link vl
inner join  wi_master.dbo.zone_data zd on vl.fk_zone_data_id = zd.id
inner join  wi_master.dbo.zones z on zd.fk_zone_id = z.id
inner join  wi_master.dbo.metrics m on zd.fk_metric_id = m.id
inner join  wi_master.dbo.attributes a on zd.fk_attribute_id = a.id
where vl.fk_data_view_id = 3
and z.type_id = 10
and date_type = 'Q'
and zd.fk_metric_id = 3
and zd.fk_attribute_id  in (0,755,799,1615)
and zd.archive = 0
and date >= '2015-01-01'
order by z.name asc, m.name asc, a.name asc, date asc'

On Stata, I just got to do "odbc query" and "odbc load" to point to a specific table - however I am not sure how to specify the rest of the attributes.

Thanks in advance!

[Joseph Coveney]

The SQL statement is the same—it depends upon the relational database management system that you're using, not upon whether you're calling it from R or Stata, and so there's no translation necessary. I haven't tried to unravel your SQL statement, but try something along the lines of

#delimit ;
local sql_statement SELECT zd.date, zd.val, a.name, z.name
    FROM wi_master.dbo.zone_data_view_link AS vl
        INNER JOIN wi_master.dbo.zone_data AS zd ON vl.fk_zone_data_id = zd.id
        INNER JOIN wi_master.dbo.zones AS z ON zd.fk_zone_id = z.id
        INNER JOIN wi_master.dbo.metrics AS m ON zd.fk_metric_id = m.id
        INNER JOIN wi_master.dbo.attributes AS a ON zd.fk_attribute_id = a.id
            WHERE vl.fk_data_view_id = 3 AND z.type_id = 10 AND date_type = 'Q'
                AND zd.fk_metric_id = 3 AND zd.fk_attribute_id IN (0, 755, 799, 1615)
                AND zd.archive = 0
                AND date >= '2015-01-01'
                    ORDER BY z.name ASC, m.name ASC, a.name ASC, date ASC;
#delimit cr
odbc load, exec("`sql_statement';") dsn("MySQLServerDSNName") clear