# Difference between prediction in R and SQL

I'm working on a prediction model for a continuous variable (amount of medicine injected) .I use R for modeling.My project flow is to multiply the prediction of a glm (logistic regression) model that is used to predict 0/1 if a medicine was injected at all with an lm (linear regression) model that is used predict amount of medicine injected - this model works rather good In R .My problem is that when I move this model to MSSQL I get different values for the prediction (i.e. for a random row the value in the R is 400 and in SQL the value for the same row is 640.The model in SQL is made by attaching the models coefficiants from the glm model to produce the glm prediction values and then multiplying it with the lm model prediction values. I don't understand why there is a difference if I use the same coefficients?

Here is the code for the lm and glm models in r:

d7_lm<-lm(Ttl_Inject~UserSource+IsNewIndividual+IsCross,data=train)
d7_glm<-glm(Is_Injected~UserSource+IsNewIndividual+IsCross,data=train)


Here is a part of the r code for the prediction:

demo$d7_lm_pred<-predict(d7_lm,newdata=demo,type='response') demo$d7_glm_pred_response<-ifelse(predict(d7_glm,newdata=demo,type='response')>0.5,1,0)
demo$glm01_lm_response<-demo$d7_lm_pred*demo$d7_glm_pred_response # this is used for a container of the prediction model's values.  Here is a part of the SQL code : select TOP 1000*, InjectionAmount_pred= (-2.213e -1.180e+00*(case when User='IAF' then 1 else 0 end)-1.665e+00*(case when UserSource='Viral' then 1 else 0 end) +IsNewIndividual * 1.167e+00+IsCross ) * IIF((1 / (1 + EXP(-(-1.346e-03+1.140e-02*(case when UserSource='IAF' then 1 else 0 end) -2.975e-03*(case when UserSource='Viral' then 1 else 0 end) -IsNewIndividual * 1.503e-04 +IsCross ))))>0.5,1,0)  • "My project flow is to multiply the prediction of a glm (logistic regression) model that is used to predict 0/1 if a medicine was injected at all with an lm (linear regression) model that is used predict amount of medicine injected" I can't make any sense of this sentence. Are you structurally testing whether, firstly, any medicine was applied and if so, the dose applied? – AdamO Jul 31 '14 at 15:55 • Also, are these predictions WAY off or are they mostly off? It looks like you have introduced a large extent of rounding error by only using 4 significant digits in your log odds ratios (e.g. -1.346e-03). R may print model output in such a fashion, but the actual log odds ratio used in prediction is very precise (on the order of$10^{-16}$, see .Machine$double.eps). See the following: stackoverflow.com/questions/2287616/controlling-digits-in-r – AdamO Jul 31 '14 at 15:58

The model in SQL is made by attaching the models coefficients from the glm model to produce the glm prediction values and then multiplying it with the lm model prediction values.

Does your model have an identity link function? That would make a difference. Could you provide the SQL code? That would help in getting an answer.

Also, this appears to a be a questions about SQL and not about statistics. It might not be a good fit for cross validate.

Edit: Okay, first, the way you are using GLMs is identical to using LMs. A GLM, as the name implies, is a more general form of a linear model. It has 2 characteristics - the link function and the residual distribution family. You define the distribution in R by using the family argument in the function. The link function is the function that relates the linear model to the response variable. You can also set the link function in the glm() parameters. An LM or linear model is a GLM with an identity link (the linear model is equal to the response) and a Gaussian distribution of residuals (that is the residuals are normally distributed).

For an example, look at the following code.

m1<-lm(Petal.Length~Sepal.Length,data=iris)
summary(m1)

Call:
lm(formula = Petal.Length ~ Sepal.Length, data = iris)

Residuals:
Min       1Q   Median       3Q      Max
-2.47747 -0.59072 -0.00668  0.60484  2.49512

Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept)  -7.10144    0.50666  -14.02   <2e-16 ***
Sepal.Length  1.85843    0.08586   21.65   <2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.8678 on 148 degrees of freedom
Multiple R-squared:   0.76, Adjusted R-squared:  0.7583
F-statistic: 468.6 on 1 and 148 DF,  p-value: < 2.2e-16


Now run the glm function but with an identity link and Gaussian distribution.

glm1<-lm(Petal.Length~Sepal.Length,data=iris)
summary(glm1)

Call:
lm(formula = Petal.Length ~ Sepal.Length, data = iris)

Residuals:
Min       1Q   Median       3Q      Max
-2.47747 -0.59072 -0.00668  0.60484  2.49512

Coefficients:
Estimate Std. Error t value Pr(>|t|)
(Intercept)  -7.10144    0.50666  -14.02   <2e-16 ***
Sepal.Length  1.85843    0.08586   21.65   <2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.8678 on 148 degrees of freedom
Multiple R-squared:   0.76, Adjusted R-squared:  0.7583
F-statistic: 468.6 on 1 and 148 DF,  p-value: < 2.2e-16


Notice that the models are exactly the same? This is because the LM is an identity linked, Gaussian family model.

http://en.wikipedia.org/wiki/Logistic_regression

Edit: I see in your original post where you say you are using a logistic regression, but I can't see in your code where you are applying the logistic regression.

If you are intent on using a GLM then you could use a logit link and either Poisson or binomial distribution depending on your data.

Now, to your question of coefficients in SQL, I don't have your data to replicate your problem, but the model objects in R have a coefficients item. This will help you transport your model coefficients from R to SQL. You can set this to a table and export it to get maximum precision. For example,

d7_lm<-lm(Ttl_Inject~UserSource+IsNewIndividual+IsCross,data=train)
t1<-d7_lm\$coefficients
write.csv(t1, 'modelCoefficients.csv')

• Thanks Eric for the clue,I'm not fimilair with "identity link function".I'm going to attach a part of my R code (model prediction) and a part of the SQL code. – user49422 Jul 31 '14 at 14:04
• Could you please attach the code where the GLM and LM are defined? – Eric Jul 31 '14 at 14:13
• Hi, I added the models – user49422 Jul 31 '14 at 14:18
• Thank you Eric, I did the changes and it works good now.I added ,family=binomial(link = "logit") to the glm model and extracted the coefficients to a cvs file than I Inserted the values in the SQL. – user49422 Aug 3 '14 at 6:46