Difference between prediction in R and SQL

Question

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) 

Answer 1

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.

For more information, if you are predicting the probability that something occurred (like in your second model), I would recommend reading about a logit regressions in general.

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')