Re: Export PROC MIXED prediction

IlariaPic · Posted 06-07-2021 08:50 AM

Hi,

I am working with PROC MIXED to test different treatments after removing the spatial autocorrelation effect by using the semivariogram parameters.

Now, I want to export a dataset with the model estimates and residues for each observation but SAS is copying the same variable estimate for all the observations belonging to the same treatment. I am using the following code, any tip? I am using SAS 9.4. I have attached the database "A". Thanks

proc mixed data=A;
class Treat_numb;
model DY=Treat_numb/ ddfm=kr s residual outp=resid;
repeated/ subject=intercept local type=sp(gau)(x y); 
parms (2.58)(46.8)(0.0026)/ noiter;

LSMEANS Treat_numb / ADJUST=TUKEY ;
run;

PROC EXPORT
DATA=resid
dbms=xlsx
outfile= "D:\resid.xlsx"
replace;
quit;

Rick_SAS · Posted 06-07-2021 11:52 AM

I don't know what problem you are encountering, but when I run your code I get the correct result.

Use the following statements to see if the values in the RESID output data set are correct:

proc print data=resid(obs=5);
var x y DY resid;
run;

If the data set is correct, then there is something wrong when you export it. Maybe you are looking at the wrong file.

IlariaPic · Posted 06-07-2021 12:15 PM

It was incorrect because if you look inside the export file, the predicted are always the same value for all the observation inside the same treatment

FreelanceReinh · Posted 06-07-2021 11:52 AM

Hello @IlariaPic and welcome to the SAS Support Communities!

@IlariaPic wrote:

(...) SAS is copying the same variable estimate for all the observations belonging to the same treatment. (...)

Do you mean variable Pred in dataset resid?

                                                                                                                                     S        P
                                                                                                                                     t        e
                                T                                                     S                                              u        a
                             T  r                                                     t                                              d        r
                             r  e                       N                             d                                              e        s
                             e  a                P      _       N                     E                                              n        o
                             a  t  B             r      u       _                     r                                              t        n
                             t  _  l             o      p       i                     r              A     L       U        R        R        R
                             m  n  o             t      t       n             P       P              l     o       p        e        e        e
 O    n                      e  u  c             e      a       p      N      r       r              p     w       p        s        s        s
 b    u                      n  m  c     D       i      k       u      U      e       e       D      h     e       e        i        i        i
 s  A m     x          y     t  b  o     Y       n      e       t      E      d       d       F      a     r       r        d        d        d

  1 5 2 747594.52 5033041.10 MF 1 BL1 14.2527 6.8567 167.054 199.64 0.8368 15.2242 0.87643 3.62354 0.05 12.6878 17.7606 -0.97150 -0.16113 -0.15946
  2 5 2 747587.52 5033006.10 MF 1 BL1 14.9840 7.2169 184.850 199.64 0.9259 15.2242 0.87643 3.62354 0.05 12.6878 17.7606 -0.24020 -0.03984 -0.03942
  3 5 2 747566.52 5032971.10 MF 1 BL1 17.0357 7.1418 207.976 199.64 1.0418 15.2242 0.87643 3.62354 0.05 12.6878 17.7606  1.81150  0.30046  0.29733
  4 5 2 747580.52 5032943.10 MF 1 BL1 13.8009 7.3158 172.590 199.64 0.8645 15.2242 0.87643 3.62354 0.05 12.6878 17.7606 -1.42330 -0.23607 -0.23361
...

Given your MODEL statement

model DY=Treat_numb/ ...;

isn't it plausible that all observations with the same Treat_numb have the same predicted DY value, i.e., Pred, whereas the residuals (e.g., Resid=DY-Pred) vary between observations?

Rick_SAS · Posted 06-07-2021 11:58 AM

@FreelanceReinh : The OP is using the OUTPREDP= option, which incorporates the random effects. See the last section of this article, which explains the OUTP= vs OUTPREDM= options.

I am not sure how you got constant values for the residuals. I did not.

Maybe the problem is reading in the data. Let's use this data to debug:

data A;
input x	y		Treat_numb	DY	;
datalines;
747594.5184	5033041.099	1	14.2527	
747587.5184	5033006.099	1	14.984	
747566.5184	5032971.099	1	17.0357	
747580.5184	5032943.099	1	13.8009	
747559.5184	5032908.099	1	15.8453	
747552.5184	5032873.099	1	14.3906	
747538.5184	5032838.099	1	14.6602	
747524.5184	5032803.099	1	14.4836	
747517.5184	5032768.099	1	13.7466	
747496.5184	5032733.099	1	15.3517	
747482.5184	5032698.099	1	16.1853	
747468.5184	5032663.099	1	15.6219	
747874.5184	5032929.099	1	18.8228	
747860.5184	5032887.099	1	16.4776	
747860.5184	5032852.099	1	13.3124	
747832.5184	5032810.099	1	17.1777	
747818.5184	5032775.099	1	17.1091	
747811.5184	5032740.099	1	16.4903	
747797.5184	5032705.099	1	16.2574	
747783.5184	5032670.099	1	17.1503	
747776.5184	5032635.099	1	16.6857	
747762.5184	5032600.099	1	16.521	
747748.5184	5032565.099	1	17.3206	
747741.5184	5032530.099	1	16.8753	
748049.5184	5032852.099	1	15.4865	
748021.5184	5032817.099	1	17.3925	
748021.5184	5032789.099	1	16.3603	
748021.5184	5032761.099	1	14.8172	
747993.5184	5032726.099	1	17.3343	
748000.5184	5032698.099	1	15.4856	
747979.5184	5032663.099	1	15.7408	
747986.5184	5032635.099	1	14.1556	
747965.5184	5032600.099	1	15.5088	
747937.5184	5032565.099	1	18.3992	
747937.5184	5032537.099	1	16.7378	
747937.5184	5032509.099	1	16.1896	
747937.5184	5032481.099	1	14.3255	
747909.5184	5032446.099	1	13.8514	
747678.5184	5032999.099	2	16.1822	
747685.5184	5032971.099	2	14.0734	
747650.5184	5032936.099	2	18.1249	
747650.5184	5032908.099	2	17.3134	
747650.5184	5032880.099	2	15.2376	
747615.5184	5032845.099	2	17.8256	
747615.5184	5032817.099	2	18.2199	
747622.5184	5032789.099	2	14.2636	
747601.5184	5032754.099	2	17.0114	
747580.5184	5032719.099	2	16.8656	
747587.5184	5032691.099	2	13.8307	
747587.5184	5032663.099	2	12.3092	
747552.5184	5032628.099	2	16.1235	
747552.5184	5032600.099	2	15.6561	
747790.5184	5032964.099	2	10.3388	
747769.5184	5032943.099	2	15.0688	
747748.5184	5032922.099	2	19.0251	
747783.5184	5032908.099	2	16.7404	
747762.5184	5032887.099	2	12.7791	
747741.5184	5032866.099	2	15.5261	
747720.5184	5032845.099	2	14.4319	
747755.5184	5032831.099	2	15.1505	
747734.5184	5032810.099	2	12.0431	
747713.5184	5032789.099	2	14.7612	
747692.5184	5032768.099	2	14.1706	
747720.5184	5032754.099	2	13.2452	
747699.5184	5032733.099	2	14.4666	
747678.5184	5032712.099	2	13.5315	
747664.5184	5032691.099	2	14.3379	
747692.5184	5032677.099	2	12.6675	
747671.5184	5032656.099	2	16.0429	
747650.5184	5032635.099	2	14.2039	
747678.5184	5032621.099	2	13.4011	
747657.5184	5032600.099	2	15.4299	
747636.5184	5032579.099	2	15.3095	
747671.5184	5032565.099	2	17.0312	
748140.5184	5032803.099	2	14.8502	
748105.5184	5032768.099	2	14.3607	
748119.5184	5032740.099	2	16.5823	
748091.5184	5032705.099	2	15.8468	
748070.5184	5032670.099	2	13.4072	
748070.5184	5032642.099	2	15.6583	
748070.5184	5032614.099	2	17.3162	
748070.5184	5032586.099	2	16.1352	
748035.5184	5032551.099	2	16.6558	
748035.5184	5032523.099	2	15.7196	
748014.5184	5032488.099	2	13.8037	
748014.5184	5032453.099	2	14.1342	
747993.5184	5032404.099	2	13.5432	
747657.5184	5033006.099	3	15.2529	
747657.5184	5032978.099	3	17.3862	
747629.5184	5032943.099	3	16.2099	
747636.5184	5032915.099	3	17.7711	
747615.5184	5032880.099	3	18.1456	
747587.5184	5032845.099	3	14.3223	
747601.5184	5032817.099	3	17.4219	
747580.5184	5032782.099	3	16.4425	
747580.5184	5032754.099	3	16.5382	
747545.5184	5032719.099	3	13.4946	
747545.5184	5032691.099	3	16.022	
747524.5184	5032656.099	3	14.3681	
747531.5184	5032628.099	3	15.8082	
747958.5184	5032880.099	3	15.4322	
747958.5184	5032852.099	3	16.3397	
747930.5184	5032817.099	3	17.3782	
747937.5184	5032789.099	3	17.4798	
747916.5184	5032754.099	3	17.5616	
747895.5184	5032719.099	3	15.0908	
747902.5184	5032691.099	3	17.0871	
747902.5184	5032663.099	3	17.1576	
747867.5184	5032628.099	3	15.6431	
747867.5184	5032600.099	3	17.921	
747867.5184	5032572.099	3	17.3888	
747846.5184	5032537.099	3	17.0461	
747825.5184	5032502.099	3	15.6876	
747839.5184	5032474.099	3	16.7379	
748021.5184	5032838.099	3	15.7513	
748000.5184	5032803.099	3	16.7668	
747972.5184	5032768.099	3	14.7859	
747986.5184	5032740.099	3	18.8191	
747951.5184	5032705.099	3	13.4772	
747951.5184	5032677.099	3	17.6236	
747951.5184	5032649.099	3	17.9013	
747951.5184	5032621.099	3	17.9448	
747923.5184	5032586.099	3	17.6293	
747930.5184	5032558.099	3	18.582	
747909.5184	5032523.099	3	18.6436	
747881.5184	5032488.099	3	14.9855	
747895.5184	5032460.099	3	17.4526	
747720.5184	5032992.099	4	18.061	
747692.5184	5032957.099	4	15.5041	
747699.5184	5032929.099	4	18.6178	
747678.5184	5032894.099	4	18.1275	
747657.5184	5032859.099	4	14.2819	
747657.5184	5032831.099	4	16.498	
747657.5184	5032803.099	4	18.2596	
747622.5184	5032768.099	4	12.7287	
747622.5184	5032740.099	4	15.1391	
747622.5184	5032712.099	4	17.4723	
747601.5184	5032677.099	4	14.0331	
747608.5184	5032649.099	4	19.3509	
747587.5184	5032614.099	4	17.5156	
747587.5184	5032579.099	4	19.6674	
747902.5184	5032887.099	4	16.042	
747902.5184	5032859.099	4	16.0664	
747867.5184	5032824.099	4	14.5629	
747874.5184	5032796.099	4	18.0023	
747853.5184	5032761.099	4	16.9353	
747860.5184	5032733.099	4	16.4014	
747832.5184	5032698.099	4	15.4785	
747832.5184	5032670.099	4	17.7188	
747832.5184	5032642.099	4	16.7882	
747797.5184	5032607.099	4	14.7846	
747797.5184	5032579.099	4	17.5189	
747797.5184	5032551.099	4	17.3421	
747769.5184	5032516.099	4	15.8409	
748063.5184	5032831.099	4	16.0691	
748042.5184	5032796.099	4	14.4959	
748049.5184	5032768.099	4	17.4157	
748028.5184	5032733.099	4	16.3706	
748028.5184	5032705.099	4	16.2923	
748028.5184	5032677.099	4	16.0381	
747993.5184	5032642.099	4	14.7028	
747993.5184	5032614.099	4	16.0152	
747993.5184	5032586.099	4	17.6182	
747965.5184	5032551.099	4	14.9384	
747979.5184	5032523.099	4	19.1654	
747951.5184	5032488.099	4	15.4053	
747930.5184	5032453.099	4	13.8199	
747944.5184	5032425.099	4	21.6317	
747636.5184	5033020.099	5	12.3816	
747608.5184	5032985.099	5	15.4957	
747587.5184	5032950.099	5	14.6347	
747587.5184	5032922.099	5	17.5501	
747587.5184	5032894.099	5	15.5509	
747587.5184	5032866.099	5	13.6618	
747552.5184	5032831.099	5	17.1552	
747559.5184	5032803.099	5	14.4923	
747531.5184	5032768.099	5	14.7803	
747545.5184	5032740.099	5	12.539	
747517.5184	5032705.099	5	14.7845	
747524.5184	5032677.099	5	13.3654	
747489.5184	5032642.099	5	15.1562	
747937.5184	5032901.099	5	13.6869	
747916.5184	5032866.099	5	16.6175	
747895.5184	5032831.099	5	17.1187	
747895.5184	5032803.099	5	18.3943	
747895.5184	5032775.099	5	15.7501	
747895.5184	5032747.099	5	13.6204	
747860.5184	5032712.099	5	15.4928	
747874.5184	5032684.099	5	12.408	
747846.5184	5032649.099	5	16.9401	
747825.5184	5032614.099	5	17.4945	
747832.5184	5032586.099	5	17.1189	
747804.5184	5032551.099	5	17.1115	
747804.5184	5032523.099	5	18.395	
747804.5184	5032495.099	5	14.8649	
747972.5184	5032859.099	5	16.1744	
747972.5184	5032831.099	5	17.9423	
747979.5184	5032803.099	5	13.1972	
747944.5184	5032768.099	5	19.4707	
747944.5184	5032740.099	5	18.5044	
747944.5184	5032712.099	5	14.8406	
747916.5184	5032677.099	5	18.8137	
747923.5184	5032649.099	5	14.7087	
747902.5184	5032614.099	5	19.3677	
747909.5184	5032586.099	5	12.7397	
747874.5184	5032551.099	5	21.8917	
747874.5184	5032523.099	5	18.6709	
747874.5184	5032495.099	5	13.9388	
747853.5184	5032460.099	5	15.1566	
747734.5184	5032957.099	6	18.0133	
747706.5184	5032908.099	6	17.2544	
747692.5184	5032866.099	6	16.7495	
747692.5184	5032824.099	6	17.3108	
747664.5184	5032775.099	6	18.0998	
747643.5184	5032726.099	6	18.1244	
747636.5184	5032684.099	6	16.2771	
747622.5184	5032642.099	6	16.7599	
747608.5184	5032600.099	6	18.3559	
747825.5184	5032943.099	6	12.8754	
747818.5184	5032922.099	6	14.69	
747804.5184	5032901.099	6	14.4918	
747790.5184	5032880.099	6	15.5778	
747825.5184	5032866.099	6	18.4528	
747811.5184	5032845.099	6	18.3908	
747797.5184	5032824.099	6	17.2848	
747783.5184	5032803.099	6	17.69	
747769.5184	5032782.099	6	17.2365	
747755.5184	5032761.099	6	15.7682	
747741.5184	5032740.099	6	15.0265	
747783.5184	5032726.099	6	18.0318	
747762.5184	5032705.099	6	15.7373	
747748.5184	5032684.099	6	15.8417	
747734.5184	5032663.099	6	15.3374	
747720.5184	5032642.099	6	15.1829	
747706.5184	5032621.099	6	15.1631	
747741.5184	5032607.099	6	17.2197	
747727.5184	5032586.099	6	16.0691	
747713.5184	5032565.099	6	16.0456	
747699.5184	5032544.099	6	17.7802	
748091.5184	5032824.099	6	17.711	
748077.5184	5032782.099	6	16.5784	
748063.5184	5032740.099	6	17.9443	
748042.5184	5032691.099	6	16.9375	
748035.5184	5032649.099	6	16.7367	
748021.5184	5032607.099	6	17.2109	
748007.5184	5032565.099	6	17.6679	
747993.5184	5032523.099	6	18.1028	
747979.5184	5032481.099	6	19.8117	
747965.5184	5032439.099	6	17.7884	
;

proc mixed data=A;
class Treat_numb;
model DY=Treat_numb/ ddfm=kr s residual outp=resid;
repeated/ subject=intercept local type=sp(gau)(x y); 
parms (2.58)(46.8)(0.0026)/ noiter;
LSMEANS Treat_numb / ADJUST=TUKEY ;
run;

proc print data=resid(obs=5); 
var x y DY resid; 
run;

PROC EXPORT
DATA=resid
dbms=xlsx
outfile= "C:\Temp\resid.xlsx"
replace;
quit;

IlariaPic · Posted 06-07-2021 12:12 PM

I think I got it!
I am working with spatial data, if I want to have a prediction for each observation I need to use as covariate something like a coordinate or a soil property that is unique for each observation.

Indeed, if I use the following code I get a unique prediction for each observation! Thanks everybody

proc mixed data=A;
class Treat_numb;
model DY=x Treat_numb/ddfm=kr s residual outp=resid;
repeated/ subject=intercept local type=sp(gau)(x y);
parms (2.58)(46.8)(0.0026)/ noiter;

LSMEANS Treat_numb / ADJUST=TUKEY ;
run;

PROC EXPORT
DATA=resid
dbms=xlsx
outfile= "D:\resid.xlsx"
replace;

quit;

FreelanceReinh · Posted 06-07-2021 12:16 PM

Hello @Rick_SAS,

@Rick_SAS wrote:

I am not sure how you got constant values for the residuals.

My residuals aren't constant (see the partial output I posted), unlike the predicted values (variable Pred) within treatment groups.

Does your PROC PRINT step yield different results than mine?

Rick_SAS · Posted 06-07-2021 12:42 PM

@FreelanceReinh : Yes, the predicted values are as you reported. Sorry for the confusion. The output data set was called RESID so in my mind I mistakenly thought the question was about the residuals and I looked at that variable.

IlariaPic · Posted 06-07-2021 12:16 PM

Yes exactly, I was not understanding why the "Pred" (predicted values) were all the same inside each treatment

SAS Innovate 2025: Register Now