Loading Data from ODBC Data source in a loop

[Filippo Busetto]

Dear Stata Forum,


I have encountered this problem yesterday and I could not figure out how to do it. I have to import a bunch of files from an online platform and save them in a folder. Basically, I have 1 file for each trading day. I tried looping with a local over dates, but it does not work (it works if I put the date myself in the code, but that is not feasible for more than 300 files). I tried something like this:


local dates 131218 311218

foreach x in local dates {

odbc load underlying_basket_isin="underlying_basket_isin" underlying_basket_aii="underlying_basket_aii" underlying_index_name="underlying_index_name" ///
notional_currency1="notional_currency1" trade_id="trade_id" execution_venue="execution_venue" compressed="compressed" price_notation="price_notation" ///
price_rate_eur="price_rate_eur" price_rate="price_rate" notional="notional" notional_eur="notional_eur" quantity_type="quantity_type" quantity="quantity" ///
up_front_payment="up_front_payment" execution_timestamp="execution_timestamp" effective_date="effective_date" maturity_date="maturity_date" ///
termination_date="termination_date" settlement_date="settlement_date" ccp_id_type="ccp_id_type" ccp_id="ccp_id" ///
clearing_timestamp="clearing_timestamp" intragroup="intragroup" underlying_maturity_date="underlying_maturity_date " ///
payment_freq_dq="payment_freq_dq" reference_period="reference_period", table(lab_prj_'x`_Positions) dsn("DISC DP Impala 64bit") clear noquote

cd "chosen directory"
save `x'_pos.dta,replace
}

but this does not work because Stata fails to recognize the x in the loop. Any sort of help is welcome! Thank you very much.

Filippo

[JanDitzen]

Hi Filippo,
what do you mean exactly it "fails to recognize the x"? Does it save the data as "_pos.dta" in each loop and if not, what is the error message? Also I noted that the foreach is invoked incorrectly. Either you use

foreach x of local dates {

(use the of instead of in), or

foreach x in `dates' {

(use in instead of of, but do not use the local).
The difference is that in the first, Stata knows that you loop over the elements of the local. In the second the local dates is evaluated and the contents of the local "pasted in". As far as I am aware, the use of "foreach of local" is faster and more reliable if the local is correctly specified.

Hopefully this helps.

[Filippo Busetto]

Hi Jan,

Thank you for your reply. This is the error message I get.

[Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Unmatched string literal in line 1: SELECT * FROM lab_prj_'x`_Positions
CAUSED BY: Exception:Unmatched string literal SQLSTATE=HY000

I tried with your solution, but it does not solve the problem. The code does not pick up the date value and keeps the `x' in the file name when I try to access it.

[JanDitzen]

Is the problem maybe that the local x is reference incorrectly in

SELECT * FROM lab_prj_'x`_Positions

where it should be

SELECT * FROM lab_prj_`x'_Positions

(not that it is `x' and not 'x` - small but important difference for Stata).
Just a guess, it might be a copying error. But since you receive an error from the odbc command, this is where to look at.

[Filippo Busetto]

Dear Jan,

Yeah....I totally missed that...Thanks again for pointing it out, now it seem to work fine.