turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Creating STATA like output in SAS regs, e.g. t-sta...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2010 07:20 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Arsenio_Staer

10-22-2010 11:56 PM

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

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Arsenio_Staer

10-25-2010 11:05 AM

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]

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]

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Arsenio_Staer

10-25-2010 10:04 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Arsenio_Staer

10-26-2010 12:19 PM

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

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