## Transposing numerous single linear regressions

Solved
Occasional Contributor
Posts: 14

# Transposing numerous single linear regressions

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;

Accepted Solutions
Solution
‎10-15-2014 03:48 AM
Super User
Posts: 10,778

## Re: Transposing numerous single linear regressions

```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

All Replies
Super User
Posts: 23,712

## Re: Transposing numerous single linear regressions

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;

Occasional Contributor
Posts: 14

## Re: Transposing numerous single linear regressions

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.

Posts: 1,270

## Re: Transposing numerous single linear regressions

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;

Posts: 3,852

## Re: Transposing numerous single linear regressions

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;

Solution
‎10-15-2014 03:48 AM
Super User
Posts: 10,778

## Re: Transposing numerous single linear regressions

```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

Occasional Contributor
Posts: 14

## Re: Transposing numerous single linear regressions

Thanks so much! I really appreciate it

🔒 This topic is solved and locked.