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!
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
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;
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.
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;
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
Thanks so much! I really appreciate it
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.