Hello R-Experts,
I've got a question, concerning the automation of a number of regressions
(lm) with the help of a loop (for i in ....).
The situation is as follows (the code follows after that):
I have my data in an access database. I have historical data for 2000
parts, for each of this parts I want to do a regression analysis, so I
need to do 2000 regressions (just for one country, there are also more
countries). So this would be a lot of manual effort.
What I want to do is to automate this routine. I have already established
a database connection via RODBC.
So I can acces every part with its distinct code by a SQL Query. For every
part, the SQL Query has to be adapted for the parts name.
My idea was to generate the SQL Code in Excel for ervery part and save
this as an txt-file. So I can define the SQL-Codes as an object (SQL_Code
<- load.table("...txt, header = FALSE).
In the next step I can build a loop, which tells R to go through the
object SQL_Code line for line, using the text for the SQL Query.
The code is as follows:
SQL Code in the sqlparts.txt. file: "SELECT table.* FROM table WHERE
((table.part) = '2929AAD766')" (2000 lines for all parts, each part has a
distinct code)
SQL_Code <- load.table("sqlparts.txt", header = FALSE)
Loop:
for (c in a(1:2000)) {
Dataset <- sqlQuery(Database, SQL_Code[a,1])
print(summary(lm(Quant~ UPE + Mon, data = Dataset)))
}
Unfortunately, this loop doesn't work. I think, it's because R does not
interpret the object SQL_Code as text, so the sqlQuery is incomplete.
Can anybody help me with that problem?
Thank you in advance.
Greets
Ryan