topic Re: Proc Tabulate - append hardcoded columns to a descriptive statistics table in SAS Programming

Proc Tabulate - append hardcoded columns to a descriptive statistics table

fostet85 — Fri, 19 Mar 2021 19:31:26 GMT

Hi!

I'm using SAS 9. I am replicating a paper, and I am trying to show that my results are comparable to the results in the paper with a side-by-side table. See picture below for an example. In the example, my results would be the "Our Replication" section and the results I'm referencing would go into the "Cooper and Boynton (2004)" section.

My question is - is it possible to append columns with hard-coded numbers to a table using proc tabulate? Is there a better way to do this?

Code for the portion of the table displaying my results:

PROC FORMAT;
  VALUE  lossfirm 0="Non-Loss Firm"
                1="Loss Firm" ;
RUN;

proc tabulate data=x1;
title 'Replication';
class lossfirm;
var at ti ni;
table at='Total Assets ($millions)'*(N*F=COMMA.0 MEAN*F=COMMA.0) ti='Taxable Income ($millions)'*(MEAN*F=COMMA.0) ni='Net Income ($millions)'*(MEAN*F=COMMA.0) ,
(lossfirm='' ALL='All Firms') ;
FORMAT lossfirm lossfirm.;
run;

Re: Proc Tabulate - append hardcoded columns to a descriptive statistics table

Reeza — Fri, 19 Mar 2021 19:36:27 GMT

Proc Tabulate only takes a single data set as a source so you have to make it into one table first somehow.
You could add that data to your current input table as a separate "firm" but with the idea that the mean of 1 value is itself so that may work.

Another way is to pipe your proc tabulate output to a data set using the OUT=option, merge that with the pre-calculated data and then use PROC REPORT to display it as desired.

Re: Proc Tabulate - append hardcoded columns to a descriptive statistics table

mkeintz — Fri, 19 Mar 2021 20:38:56 GMT

You can construct synthetic data that would produce the Cooper/Boynton statistics. Adding a new variable STUDY (= 'Cooper and Boynton (2004)' or 'Our Replication') and using STUDY as a new class variable, should do the trick:


data combined (drop=i _:);
  set x1 end=end_of_x1;
  retain study "Our Replication        ";
  output;
  if end_of_x1;
  infile datalines;
  study='Cooper and boynton (2004)';
  input lossfirm _n at ti ni    _nall _atall _tiall _niall ;
  do i=1 to _n; 
    output;      ** Output loss firms **;
  end; 
  /* ******** Gen at,ti,ni (x) for non-loss firms   ****** */
  /* We know that  _nall*_atall =   _n*at + (_nall-_n)*x   */
  /*  ==>   (_nall-_n)*x   = _nall*_atall - _n*at          */
  /*  ==>   x = (_nall*_atall - _n*at)/(_nall-_n)          */
  at = (_nall*_atall - _b*at)/(_nall-_n);
  ti = (_nall*_tiall - _b*ti)/(_nall-_n);
  ni = (_nall*_niall - _b*ni)/(_nall-_n);
  do i=1 to (_nall-_n);
    output;
  end;
datalines;
1  9088  4303409 -159907 -159107  27384 26100649 442705 322621 ;
run;

What I don't understand is how you got tabulate to drop the non-loss firm columns. But ignoring that, notice how the program imputes the values for the non-loss firms that would replicate the values for ALL firms.

You should be able to apply proc tabulate to the COMBINED data set.

Re: Proc Tabulate - append hardcoded columns to a descriptive statistics table

ballardw — Fri, 19 Mar 2021 20:53:00 GMT

You may be better off summarizing the data. Then use Proc Report for display.