DATA Step, Macro, Functions and more

Transposing numerous single linear regressions

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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_InterceptFront + 1Front + 2Front + 3Front + 4Front + 5Front + 6Front + 7Front + 8Front + 9Front + 10Front + 11Front + 12_RSQ_
1m1Front01...........1
2m2Front0.1..........1
3m3Front-0.00006497..1.04193.........0.99659
4m4Front-0.00006497...1.04193........0.99659
5m5Front-0.00006497....1.04193.......0.99659
6m6Front-0.000018595.....1.09305......0.99439
7m7Front-0.000018595......1.09305.....0.99439
8m8Front-0.000018536.......1.12321....0.99239
9m9Front-0.000018536........1.12321...0.99239
10m10Front-0.000305717.........1.19051..0.97807
11m11Front-0.000305717..........1.19051.0.97807
12m12Front-0.000657577...........1.264390.9311

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

Obs_MODEL__DEPVAR_Independent VariableInterceptCoefficient_RSQ_
1m1FrontFront + 1011
2m2FrontFront + 2011
3m3FrontFront + 3-0.000064971.041930.99659
4m4FrontFront + 4-0.000064971.041930.99659
5m5FrontFront + 5-0.000064971.041930.99659
6m6FrontFront + 6-0.0000185951.093050.99439
7m7FrontFront + 7-0.0000185951.093050.99439
8m8FrontFront + 8-0.0000185361.123210.99239
9m9FrontFront + 9-0.0000185361.123210.99239
10m10FrontFront + 10-0.0003057171.190510.97807
11m11FrontFront + 11-0.0003057171.190510.97807
12m12FrontFront + 12-0.0006575771.264390.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!


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

Re: Transposing numerous single linear regressions

Posted in reply to dangoebel
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

View solution in original post


All Replies
Super User
Posts: 19,837

Re: Transposing numerous single linear regressions

Posted in reply to dangoebel

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.

Trusted Advisor
Posts: 1,228

Re: Transposing numerous single linear regressions

Posted in reply to dangoebel

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;

Respected Advisor
Posts: 3,799

Re: Transposing numerous single linear regressions

Posted in reply to dangoebel
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;

10-14-2014 6-01-34 PM.png
Solution
‎10-15-2014 03:48 AM
Super User
Posts: 10,041

Re: Transposing numerous single linear regressions

Posted in reply to dangoebel
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 276 views
  • 6 likes
  • 5 in conversation