[R] Automated Regressions

[Frederic Andrieu]

Hi Ryan,

My name is Frederic Andrieu and I work for Mango Solutions in the UK. It looks like you have swapped the a and c in this for loop statement:

This "for (c in a(1:2000)) {" should be this "for (a in c(1:2000)) {"

Which is why the for loop is not running, I would suggest something like this:
#################################################################################################################################################

Require(RODBC)

myChannel <- odbcConnect(dsn = "myDSN")
sqlString1 <- "SELECT myTable.Contries FROM myTable GROUP BY myTable.Countries;"
myContries <- sqlQuery(channel = myChannel, query = sqlString1)

# myContries is now a table of counties with 1 column and as many rows as there are countries

# Then this is the function that will do the legwork myLm <- function(myCountry = myCountries[1]){
	print(myCountry)
	sqlString2 <- paste("SELECT myTable.* FROM myTable WHERE (((myTable.Countries)=", myCountry,")); ", sep = "")
	myData <- sqlQuery(channel = myChannel, query = sqlString2)
	myOut <- summary(lm(Quant ~ UPE + Mon, data = myData))$coeff
	myOut <- data.frame(myOut)
	colnames(myOut) <- c("Estimate", "Std. Error",  " tvalue",    " Pr(>|t|)")
	myOut <- data.frame(myCountry, myOut)
	return(myOut)
}

# Applying the function
myAnalysis <- lapply(myCountries, myLm)
myAnalysis <- do.call(rbind, myAnalysis)

#################################################################################################################################################

Let me know if this helps.

Best regards,

Frederic Andrieu

M: +44(0)07813 526 123
T: +44 (0)1249 766 801  
F: +44 (0)1249 767 707
www.mango-solutions.com 
Unit 2 Greenways Business Park
Bellinger Close
Chippenham
Wilts
SN15 1BN
UK 



-----Original Message-----
From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of ryanSt
Sent: 12 December 2011 14:59
To: r-help at r-project.org
Subject: [R] Automated Regressions

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




--
View this message in context: http://r.789695.n4.nabble.com/Automated-Regressions-tp4186280p4186280.html
Sent from the R help mailing list archive at Nabble.com.

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
LEGAL NOTICE
This message is intended for the use o...{{dropped:10}}