Creating STATA like output in SAS regs, e.g. t-stats below coefficient

[Arsenio_Staer]

Dear all,

It's a question i have been researching for a while. Is there a way to program or code in SAS maybe through excel tagset or through ODS to output t stats in parenthesis below coefficient estimates in a regression table like in STATA? as in below:

Regression of ETFs tracking Dow Jones Industrial Average Index
2007 - 2010

Variables Dow Jones Daily Ret <--- dependent variable
DIA Flow 0.00302 <--- coeff estimate
tstat ----> (0.145)
Ideally i would also look for the same structure in other tables, e.g. correlation tables.

Thank you in advance,

Arsenio

[sbb]

Suggest you re-post in a more appropriate Statistics forum.

Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

Hi:
Consider the following code from PROC REG:
[pre]
ods trace on /label listing;
proc reg data=sashelp.class;
model age=height;
run;
quit;
ods trace off;
[/pre]

It creates 4 output objects, as identified in the LISTING output (Each output object starts with the line "Output Added"):
[pre]
The REG Procedure
Model: MODEL1
Dependent Variable: Age

Output Added:
-------------
Name: NObs
Label: Number of Observations
Template: Stat.Reg.NObs
Path: Reg.MODEL1.Fit.Age.NObs
Label Path: 'The Reg Procedure'.'MODEL1'.'Fit'.Age.'Number of Observations'
-------------


Number of Observations Read 19
Number of Observations Used 19

Output Added:
-------------
Name: ANOVA
Label: Analysis of Variance
Template: Stat.REG.ANOVA
Path: Reg.MODEL1.Fit.Age.ANOVA
Label Path: 'The Reg Procedure'.'MODEL1'.'Fit'.Age.'Analysis of Variance'
-------------


Analysis of Variance

Sum of Mean
Source DF Squares Square F Value Pr > F

Model 1 26.40634 26.40634 32.77 <.0001
Error 17 13.69893 0.80582
Corrected Total 18 40.10526

Output Added:
-------------
Name: FitStatistics
Label: Fit Statistics
Template: Stat.REG.FitStatistics
Path: Reg.MODEL1.Fit.Age.FitStatistics
Label Path: 'The Reg Procedure'.'MODEL1'.'Fit'.Age.'Fit Statistics'
-------------


Root MSE 0.89767 R-Square 0.6584
Dependent Mean 13.31579 Adj R-Sq 0.6383
Coeff Var 6.74143

Output Added:
-------------
Name: ParameterEstimates
Label: Parameter Estimates
Template: Stat.REG.ParameterEstimates
Path: Reg.MODEL1.Fit.Age.ParameterEstimates
Label Path: 'The Reg Procedure'.'MODEL1'.'Fit'.Age.'Parameter Estimates'
-------------


Parameter Estimates

Parameter Standard
Variable DF Estimate Error t Value Pr > |t|

Intercept 1 -1.41049 2.58074 -0.55 0.5918
Height 1 0.23624 0.04127 5.72 <.0001
[/pre]

So which 2 numbers from the above output would you want to have "stacked" together. Given that the PROC REG output is coming from 4 separate output objects, what you want to do is possible, but would require that you do something like this:
1) capture your output object(s) of interest in a dataset
2) take the numbers (variables) you want out of each output object
3a) create a new variable from the 2 variables of interest with a carriage return or line feed between them and then use PROC PRINT or PROC REPORT on this new dataset with the new variable

OR

3b) create a new table template to use with the data from #2 and code the new table template to stack the 2 variables of interest. Then invoke the new table template with a DATA step program.

The creation of this type of output is not specific to any destination....but you would be using features of ODS, such as ODS OUTPUT for #1, ODS ESCAPECHAR in #3a or ODS Table templates in #3b. Once your information was captured and your new data with the parentheses around the t stat was ready to go, you could send your output to any destination that supported the technique that you had chosen.

But it's still not clear to me WHICH 2 numbers from the REG output you would want to see stacked. So consider this other example, that stacks 2 numbers taken from 1 output object in PROC MEANS. The name of the output object for PROC MEANS is BASE.SUMMARY, so first, the program creates an output dataset using ODS OUTPUT. Then the output dataset is passed to PROC REPORT, where the parentheses are added around the HEIGHT_MAX variable and concatenated with the HEIGHT_MIN variable in order to make the character variable NEWVAR (which has a carriage return or line feed between the 2 pieces of information).

As you can see, getting information from the SAME output object is a bit simpler than getting information from 2 different output objects. so what you want do do will be a bit more complicated than what is shown in the program below.

cynthia
[pre]
ods output summary=work.mnout;
proc means data=sashelp.class nway min max;
var height;
class age;
run;

proc print data=mnout;
run;

ods html file='c:\temp\useReport.html' style=sasweb;
ods escapechar='~';
proc report data=mnout nowd split='/';
title 'Using PROC REPORT';
column age nobs height_min height_max newvar;
define age / order;
define nobs / sum;
define height_min / display noprint;
define height_max / display noprint;
define newvar / computed 'Based on: Height/Min/(Max)'
style(column)={just=r};
compute newvar / character length=30;
newvar = catt(put(height_min,4.1),'~n','(',put(height_max,6.2),')');
endcomp;
run;
ods _all_ close;
title;
[/pre]

[Arsenio_Staer]

Thank you for the answer Cynthia,

I planned to have estimates t stats from the regression model as the value in parenthesis below the parameter estimates :


Parameter Estimates

Parameter Standard
Variable DF Estimate Error t Value Pr > |t|

Intercept 1 -1.41049 2.58074 -0.55 0.5918
Height 1 0.23624 0.04127 5.72 <.0001

I will analyze the code you posted and extract the t stats from ParameterEstimates and join them in the ODS table output using the escape char code snippet you provided in the end. It should work.

A small question: how would you put that value in the parenthesis ( in your example height_max) on the next row when you ods output to excel using tagsets? So that the height_min is on the row 2 and height_max is on the row 3 in excel file? For now, outputting the results to excel, puts in the same row and consequently in the same cell.

Thank you!

Arsenio

[Cynthia_sas]

Hi:
Well, if you want your statistic in a separate cell, then you have a slightly different challenge. If I were dealing with just PROC MEANS output, I wouldn't use the ODS ESCAPECHAR trick at all (which as you noted, actually does stack the 2 statistics together in one cell). Instead, I would "transpose" the data and then use PROC REPORT.

Here's the PROC MEANS example using TRANSPOSE and a PICTURE format and PROC REPORT to get parentheses around Height_Max (and only Height_Max).
[pre]
ods output summary=work.mnout;
proc means data=sashelp.class nway min max;
var height;
class age;
run;

proc transpose data=work.mnout out=work.trout;
by age;
run;

proc print data=work.trout;
title 'After Transpose, what are variables';
run;

** create a picture format to add parentheses.;
** could also create a character variable with parentheses, instead.;
proc format;
picture paren low-high='00099.99)' (prefix='(');
run;

ods html file='c:\temp\transposed.html' style=sasweb;
proc report data=work.trout nowd;
title 'PROC REPORT with CALL DEFINE puts parens around Max';
column age _label_ col1;
define age /order order=data 'Age';
define _label_ / order order=data 'Statistic';
define col1 / display 'Value';
compute col1;
if _label_ = 'Maximum' then do;
call define (_col_,'format','paren.');
end;
endcomp;
run;
ods html close;
[/pre]

Remember that SAS, when it makes a tabular report, generally wants every report row to have the same number of columns, so for example, if I was doing a report on SASHELP.CLASS, it would not be a "normal" tabular report for me to have NAME, AGE and HEIGHT on 1 report row and to have WEIGHT underneath HEIGHT or WEIGHT underneath AGE on the next report row.

So, if you do capture the output from PROC REG into 2 different datasets, you will then have to bring those datasets together in a way that makes sense. For example, this code:
[pre]
ods output fitstatistics=work.fs
parameterestimates=work.pe;
proc reg data=sashelp.class;
model age=height;
run;
quit;

proc print data=work.fs;
title 'Fit Statistics Stored in Dataset';
run;

proc print data=work.pe;
title 'Parameter Estimates Stored in Dataset';
run;
[/pre]

creates these 2 output datasets, WORK.FS and WORK.PE, as shown below:
[pre]
*** *** *** WORK.FS *** *** ***
Fit Statistics Stored in Dataset

Obs Model Dependent Label1 cValue1 nValue1 Label2 cValue2 nValue2

1 MODEL1 Age Root MSE 0.89767 0.897674 R-Square 0.6584 0.658426
2 MODEL1 Age Dependent Mean 13.31579 13.315789 Adj R-Sq 0.6383 0.638333
3 MODEL1 Age Coeff Var 6.74143 6.741428 0


*** *** *** WORK.PE *** *** ***
Parameter Estimates Stored in Dataset

Obs Model Dependent Variable DF Estimate StdErr tValue Probt

1 MODEL1 Age Intercept 1 -1.41049 2.58074 -0.55 0.5918
2 MODEL1 Age Height 1 0.23624 0.04127 5.72 <.0001
[/pre]

Important to note is that the 2 datasets have 2 different structures. The Fit Statistics dataset has different column names than the Parameter Estimates dataset.

Again, what you want to achieve is do-able, but you may have to do a bit of data manipulation to get the report layout (report rows and columns) that you want.

cynthia