/*
KAZUAKI UEKAWA's REGassist MACRO
SAS consultant 
www.src.uchicago.edu/users/ueka
kuekawa@alumni.uchicago.edu

2002 Jan 10th

This is a macro program to run regression and create a nice table immediately after it.  Also it creates residual files from each 
regression model.  First of all, you need to have this file in a working directory.  THen, you need to have a main SAS program
and write a program like this below.  After this proc reg, do an include statement (see below) in order to include this very file.

Now this is for 7 models, but can add up to 10 models.  But in that case you will need to modify  PROC REPORT part.
Look at CLUMN statement.  Just add extra variables there.  I think this is self-explanatory.

%let outcomevar=BSDGEDUP;
%let tablename=finalreg;
ods listing close;
proc reg data=ajsdata2;
model &outcomevar.2=&outcomevar.1  kwait GNP11 GINI DPROP90/p clm;
model &outcomevar.2=&outcomevar.1  kwait SELECT NATEXAM/p clm;
model &outcomevar.2=&outcomevar.1  kwait DPROP90 SELECT NATEXAM  G7AdjICC_BSMPV01  G7AdjICC_&outcomevar /p clm;
model &outcomevar.2=&outcomevar.1  kwait DPROP90 SELECT NATEXAM  G7AdjSDW_BSMPV01 G7AdjSDW_&outcomevar/p clm;
model &outcomevar.2=&outcomevar.1  kwait DPROP90 SELECT NATEXAM  G7AdjSDB_BSMPV01 G7AdjSDB_&outcomevar /p clm;
model &outcomevar.2=&outcomevar.1  kwait DPROP90 SELECT NATEXAM  G7AdjSDB_BSMPV01 G7AdjSDB_&outcomevar G7SD_curcov1/p clm;
model &outcomevar.2=&outcomevar.1  kwait DPROP90 SELECT NATEXAM  G7AdjSDB_BSMPV01 G7AdjSDB_&outcomevar SD_pedag/p clm;
id acro;
ods output parameterestimates=param1 anova=anovaresult  fitstatistics=fitresult
OutputStatistics=residual
;
run;

%include "D:\AJSprorgams\REGassist.sas";

*/


/*Getting N of observation, as well as results of F-test*/
data anovaresultA;set anovaresult;
keep model Nobs;
if source="Corrected Total";
Nobs=DF;
data anovaresultB;
retain model Fvalue DF probF;
set anovaresult;

keep model Fvalue probF DF;
if source="Model";
Nobs=DF;
data anova;merge anovaresultA anovaresultB;
by model;
run;


/*Getting R-squares*/
data RsquareA;set fitresult;
keep model Rsquare;
if label2="R-Square";
Rsquare=nvalue2;
data RsquareB;set fitresult;
keep model AdjRsquare;
if label2="Adj R-Sq";
AdjRsquare=nvalue2;
data Rsquare;merge RsquareA RsquareB;
by model;
run;


/*Put together info from Anova and Fitstatistics*/
data info;merge Anova Rsquare;
by model;
run;
proc transpose data=info out=infoT;
id model;
run;

/*and put this into a form that is meargeable with parameter data below*/
data infoT2;
set infoT;
Model1Estimate=Model1;
Model2Estimate=Model2;
Model3Estimate=Model3;
Model4Estimate=Model4;
Model5Estimate=Model5;
Model6Estimate=Model6;
Model7Estimate=Model7;
Model8Estimate=Model8;
Model9Estimate=Model9;
Model10Estimate=Model10;
drop _label_ model1 model2 model3 model4 model5 model6 model7 model8 model9 model10;
run;

/*Next manupulate main result data*/
proc transpose data=param1 out=paramT;by model;id variable;run;
data paramT2;set paramT;new=compress(Model||_name_)  ;drop _label_;run;
proc transpose data=paramT2 out=paramT3;id new;run;


data paramT4;set paramT3 infoT2;
%macro keisan (suji);
if model&suji.probT < 0.1 then model&suji.SIG='~';
if model&suji.probT < 0.05 then model&suji.SIG='*';
if model&suji.probT < 0.01 then model&suji.SIG='**';
if model&suji.probT < 0.001 then model&suji.SIG='***';
if model&suji.probT < 0.0001 then model&suji.SIG='****';
if model&suji.probT <-9999 then model&suji.SIG='    ';
%mend keisan;
%keisan (suji=1);
%keisan (suji=2);
%keisan (suji=3);
%keisan (suji=4);
%keisan (suji=5);
%keisan (suji=6);
%keisan (suji=7);
%keisan (suji=8);
%keisan (suji=9);
%keisan (suji=10); /*for now I created up to model 10*/
run;


ods listing;
proc report  data=paramT4  nowd headline  spacing=1 out=OLSparam;
title1 "Table 1: &tablename";
footnote1 "Continuous Variables are standardized, mean=0 std=1.";
footnote2 "";
column  
_NAME_
Model1Estimate
Model1STDerr
model1SIG
Model2Estimate
Model2STDerr
Model2SIG
Model3Estimate
Model3STDerr
Model3SIG
Model4Estimate
Model4STDerr
Model4SIG
Model5Estimate
Model5STDerr
Model5SIG
Model6Estimate
Model6STDerr
Model6SIG
Model7Estimate
Model7STDerr
Model7SIG
;
*define _name_/"" order=data;
define _NAME_ /"Variables";
define Model1Estimate /"1"  format=comma6.2;
define Model2Estimate /"2"  format=comma6.2;
define Model3Estimate /"3"  format=comma6.2;
define Model4Estimate /"4"  format=comma6.2;
define Model5Estimate /"5"  format=comma6.2;
define Model6Estimate /"6"  format=comma6.2;
define Model7Estimate /"7"  format=comma6.2;
define Model8Estimate /"8"  format=comma6.2;
define Model9Estimate /"9"  format=comma6.2;
define Model10Estimate /"10"  format=comma6.2;/*for now I created up to 10*/

define Model1STDerr /""  format=comma6.2;
define Model2STDerr /""  format=comma6.2;
define Model3STDerr  /""  format=comma6.2;
define Model4STDerr /""  format=comma6.2;
define Model5STDerr /""  format=comma6.2;
define Model6STDerr /""  format=comma6.2;
define Model7STDerr /""  format=comma6.2;
define Model8STDerr /""  format=comma6.2;
define Model9STDerr /""  format=comma6.2;
define Model10STDerr /""  format=comma6.2;/*for now I created up to 10*/

define model1SIG /"" ;
define model2SIG /"" ;
define model3SIG /"" ;
define model4SIG /"" ;
define model5SIG /"" ;
define model6SIG /"" ;
define model7SIG /"" ;
define model8SIG /"" ;
define model9SIG /"" ;
define model10SIG /"" ;/*for now I created up to 10*/
run;


PROC EXPORT DATA= WORK.OLSparam
            OUTFILE= "C:\My Documents\&tablename..xls" 
            DBMS=EXCEL2000 REPLACE;
RUN;


proc transpose data=residual out=residual2;
by model;
var   residual;run;
*proc print data=residual2;
*run;

data residual3; set residual2;
drop _name_;
proc transpose data=residual3 out=residual4;
run;
*proc print data=residual4;
*run;

data justname;set residual;
where ="MODEL1";
keep acro;
run;

data residualfile;
merge justname  residual4;
if _name_ ne "";
drop _name_;
run;
proc print;
title "&tablename";
run;