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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.