topic Re: Transposing numerous single linear regressions in SAS Programming

Transposing numerous single linear regressions

dangoebel — Tue, 14 Oct 2014 19:06:35 GMT

I have been running numerous linear regressions with the most current month as the dependent variable and each subsequent month as the independent variables. I am currently getting this as the output.

Obs	_MODEL_	_DEPVAR_	Intercept	Front + 1	Front + 2	Front + 3	Front + 4	Front + 5	Front + 6	Front + 7	Front + 8	Front + 9	Front + 10	Front + 11	Front + 12	_RSQ_
1	m1	Front	0	1	.	.	.	.	.	.	.	.	.	.	.	1
2	m2	Front	0	.	1	.	.	.	.	.	.	.	.	.	.	1
3	m3	Front	-0.00006497	.	.	1.04193	.	.	.	.	.	.	.	.	.	0.99659
4	m4	Front	-0.00006497	.	.	.	1.04193	.	.	.	.	.	.	.	.	0.99659
5	m5	Front	-0.00006497	.	.	.	.	1.04193	.	.	.	.	.	.	.	0.99659
6	m6	Front	-0.000018595	.	.	.	.	.	1.09305	.	.	.	.	.	.	0.99439
7	m7	Front	-0.000018595	.	.	.	.	.	.	1.09305	.	.	.	.	.	0.99439
8	m8	Front	-0.000018536	.	.	.	.	.	.	.	1.12321	.	.	.	.	0.99239
9	m9	Front	-0.000018536	.	.	.	.	.	.	.	.	1.12321	.	.	.	0.99239
10	m10	Front	-0.000305717	.	.	.	.	.	.	.	.	.	1.19051	.	.	0.97807
11	m11	Front	-0.000305717	.	.	.	.	.	.	.	.	.	.	1.19051	.	0.97807
12	m12	Front	-0.000657577	.	.	.	.	.	.	.	.	.	.	.	1.26439	0.9311

I was wondering if there was an easy way to rearrange the data like this

Obs	_MODEL_	_DEPVAR_	Independent Variable	Intercept	Coefficient	_RSQ_
1	m1	Front	Front + 1	0	1	1
2	m2	Front	Front + 2	0	1	1
3	m3	Front	Front + 3	-0.00006497	1.04193	0.99659
4	m4	Front	Front + 4	-0.00006497	1.04193	0.99659
5	m5	Front	Front + 5	-0.00006497	1.04193	0.99659
6	m6	Front	Front + 6	-0.000018595	1.09305	0.99439
7	m7	Front	Front + 7	-0.000018595	1.09305	0.99439
8	m8	Front	Front + 8	-0.000018536	1.12321	0.99239
9	m9	Front	Front + 9	-0.000018536	1.12321	0.99239
10	m10	Front	Front + 10	-0.000305717	1.19051	0.97807
11	m11	Front	Front + 11	-0.000305717	1.19051	0.97807
12	m12	Front	Front + 12	-0.000657577	1.26439	0.9311

My current equation is

proc reg data=work.danreg outest=work.regoutput edf;

m1:model Front = ‘Front + 1’/noprint;

m2:model Front = ‘Front + 2’/noprint;

m3:model Front = ‘Front + 3’/noprint;

m4:model Front = ‘Front + 4’/noprint;

m5:model Front = ‘Front + 5’/noprint;

m6:model Front = ‘Front + 6’/noprint;

m7:model Front = ‘Front + 7’/noprint;

m8:model Front = ‘Front + 8’/noprint;

m9:model Front = ‘Front + 9’/noprint;

m10:model Front = ‘Front + 10’/noprint;

m11:model Front = ‘Front + 11’/noprint;

m12:model Front = ‘Front + 12’/noprint;

proc print;

VAR _Model_ _DEPVAR_ Intercept _RSQ_ ‘Front + 1’ Front + 2’ ‘Front+3’ ‘Front + 4’

‘Front + 5’ ‘Front + 6’ ‘Front + 7’ ‘Front + 8’ ‘Front + 9’ ‘Front + 10’ ‘Front + 11’ ‘Front + 12’;

run;

Thanks for your help!

Re: Transposing numerous single linear regressions

Reeza — Tue, 14 Oct 2014 19:35:43 GMT

Use the ODS table instead, though you'll have to capture the R-Squared separately modify that table and merge it in. This gives you the idea at least.

ods table parameterEstimates=Sample;

proc reg data=sashelp.class;

M1: model weight=height;

M2: model weight=age;

run;quit;

proc print data=Sample;

run;

Re: Transposing numerous single linear regressions

dangoebel — Tue, 14 Oct 2014 20:02:36 GMT

I entered the equation but now is has the heading variable switching between intercept and independent variable with the value in another heading titled estimate.

Re: Transposing numerous single linear regressions

stat_sas — Tue, 14 Oct 2014 20:31:15 GMT

proc contents data=work.regoutput out=vars;
run;

proc sql;
select name into :ind separated by ' ' from vars where name like 'Front%';
quit;

data want(keep=Obs _MODEL_ _DEPVAR_ Independent_Variable Intercept Coefficient _RSQ_);
retain Obs _MODEL_ _DEPVAR_ Independent_Variable Intercept Coefficient _RSQ_;
set work.regoutput;
array front{*} &ind;
Coefficient = sum(of front{*});
Independent_variable=cat('Front',' + ',obs);
run;

proc print data=want;
run;

Re: Transposing numerous single linear regressions

data_null__ — Tue, 14 Oct 2014 23:02:28 GMT

options validvarname=any;
filename FT15F001 temp; 
proc import file=FT15F001 dbms=csv out=model replace; 
parmcards; 
_MODEL_,_DEPVAR_,Intercept,Front + 1,Front + 2,Front + 3,Front + 4,Front + 5,Front + 6,Front + 7,Front + 8,Front + 9,Front + 10,Front + 11,Front + 12,_RSQ_
m1,Front,0,1,.,.,.,.,.,.,.,.,.,.,.,1
m2,Front,0,.,1,.,.,.,.,.,.,.,.,.,.,1
m3,Front,-0.00006497,.,.,1.04193,.,.,.,.,.,.,.,.,.,0.99659
m4,Front,-0.00006497,.,.,.,1.04193,.,.,.,.,.,.,.,.,0.99659
m5,Front,-0.00006497,.,.,.,.,1.04193,.,.,.,.,.,.,.,0.99659
m6,Front,-0.000018595,.,.,.,.,.,1.09305,.,.,.,.,.,.,0.99439
m7,Front,-0.000018595,.,.,.,.,.,.,1.09305,.,.,.,.,.,0.99439
m8,Front,-0.000018536,.,.,.,.,.,.,.,1.12321,.,.,.,.,0.99239
m9,Front,-0.000018536,.,.,.,.,.,.,.,.,1.12321,.,.,.,0.99239
m10,Front,-0.000305717,.,.,.,.,.,.,.,.,.,1.19051,.,.,0.97807
m11,Front,-0.000305717,.,.,.,.,.,.,.,.,.,.,1.19051,.,0.97807
m12,Front,-0.000657577,.,.,.,.,.,.,.,.,.,.,.,1.26439,0.9311
;;;;
run; 
proc print; 
   run; 
proc transpose 

      data=model 

      out=model2(where=not missing(Coefficient1))) 

      name='Independent Variabele'n 

      prefix=Coefficient

      ;
   by _model_ --Intercept _rsq_ notsorted; 
   var front:;
   run; 
proc print; 
   run;

Re: Transposing numerous single linear regressions

Ksharp — Wed, 15 Oct 2014 07:48:15 GMT

options validvarname=any;
filename FT15F001 temp; 
proc import file=FT15F001 dbms=csv out=model replace; 
parmcards; 
_MODEL_,_DEPVAR_,Intercept,Front + 1,Front + 2,Front + 3,Front + 4,Front + 5,Front + 6,Front + 7,Front + 8,Front + 9,Front + 10,Front + 11,Front + 12,_RSQ_
m1,Front,0,1,.,.,.,.,.,.,.,.,.,.,.,1
m2,Front,0,.,1,.,.,.,.,.,.,.,.,.,.,1
m3,Front,-0.00006497,.,.,1.04193,.,.,.,.,.,.,.,.,.,0.99659
m4,Front,-0.00006497,.,.,.,1.04193,.,.,.,.,.,.,.,.,0.99659
m5,Front,-0.00006497,.,.,.,.,1.04193,.,.,.,.,.,.,.,0.99659
m6,Front,-0.000018595,.,.,.,.,.,1.09305,.,.,.,.,.,.,0.99439
m7,Front,-0.000018595,.,.,.,.,.,.,1.09305,.,.,.,.,.,0.99439
m8,Front,-0.000018536,.,.,.,.,.,.,.,1.12321,.,.,.,.,0.99239
m9,Front,-0.000018536,.,.,.,.,.,.,.,.,1.12321,.,.,.,0.99239
m10,Front,-0.000305717,.,.,.,.,.,.,.,.,.,1.19051,.,.,0.97807
m11,Front,-0.000305717,.,.,.,.,.,.,.,.,.,.,1.19051,.,0.97807
m12,Front,-0.000657577,.,.,.,.,.,.,.,.,.,.,.,1.26439,0.9311
;;;;
run; 

data want; 
set model;
Coefficient=coalesce(of Front___1 - Front___12);
drop Front___1 - Front___12;
run;

Xia Keshan

Re: Transposing numerous single linear regressions

dangoebel — Wed, 15 Oct 2014 19:54:11 GMT

Thanks so much! I really appreciate it