Tabulate only key sas results and transferring those to Excel

Reply
Regular Contributor
Posts: 183

Tabulate only key sas results and transferring those to Excel

Hi,

I run the regression in SAS and SAS shows the results from running the regression such as mean, median, standard deviation, skewness, kurtosis, parameter estimates, standard error, t-stat, p-value etc. But I need to tabulate only the key results (such as parameter estimates, standard error and significance of any specific parameter (from p-value)) and then transfer those key results either to excel or to word.

For example, I run the multiple regressions in multiple datasteps, such as:

ret1 = bm   mv     roa    roe 

ret2 = bm   mv     roa    roe

ret3 = bm   mv     roa    roe

Please note that ret1, ret2 and ret3 are dependent variables and bm, mv, roa and roe are independent variables.

I need to have the results in the following way:

                                      ret1                                               ret2                                              ret3

Intercept             parameter estimates***                parameter estimates***            parameter estimates***

bm                      parameter estimates***                parameter estimates***            parameter estimates***

                            (standard error)                                  (standard error)                       (standard error)

mv                      parameter estimates**                parameter estimates**            parameter estimates**

                               (standard error)                            (standard error)                       (standard error)

roa                     parameter estimates***                parameter estimates***            parameter estimates***

                               (standard error)                             (standard error)                       (standard error)

roe                     parameter estimates*                  parameter estimates*               parameter estimates*

                              (standard error)                              (standard error)                       (standard error)

Please note that ***, ** and * indicate that parameters are significant at 1%, 5% and 10% respectively, which is determined fro

Trusted Advisor
Posts: 1,203

Re: Tabulate only key sas results and transferring those to Excel

Hi,

ParameterEstimates obtained through ods, will contain the required info. You can maniplute that to get the desired output.

ods output ParameterEstimates= ParameterEstimates;
proc reg data=fitness outest=outest;
model Oxygen=RunTime RunPulse Age MaxPulse;
model Runtime= RunPulse Age MaxPulse;
quit;

proc print data= ParameterEstimates;
run;

Regular Contributor
Posts: 183

Re: Tabulate only key sas results and transferring those to Excel

Hi,

I did not get properly. Say, the name of my dataset is ceobuy. And I want to run the followings:

proc panel data = ceobuy;

model car_1 = bm mv roa roe/pooled;       /*** Pooled OLS***/

id year CompanyISIN;

run;

and

proc panel data = ceobuy;

model car_1 = bm mv roa roe/fixone;       /*** Firm Fixed Effect***/

id year CompanyISIN;

run;

Grand Advisor
Posts: 17,316

Re: Tabulate only key sas results and transferring those to Excel

The way to do it, is to keep only the results your need in SAS and then manipulate them to the format you want in SAS then export. This allows you to easily re-run and make changes in your analysis as necessary (ie cleaning up data) and still extract the same output.

Your estimates from Proc Panel  from the following, will end up in two tables PooledOLS and FixedFE, that you can then manipulate into the format desired. Another option is to use multiple Model statements in a single call, and then manipulate that output. See the last example.

ods Table ParameterEstimates=PooledOLS;

proc panel data = ceobuy;

model car_1 = bm mv roa roe/pooled;       /*** Pooled OLS***/

id year CompanyISIN;

run;

ods Table ParameterEstimates=FixedFE;

proc panel data = ceobuy;

model car_1 = bm mv roa roe/fixone;       /*** Firm Fixed Effect***/

id year CompanyISIN;

run;

You can also try using multiple model statements and see how the output is formatted, perhaps more to your liking?

ods Table ParameterEstimates=Together;

proc panel data = ceobuy;

Pooled: model car_1 = bm mv roa roe/pooled;   /*** Pooled OLS***/

Fixed: model car_1 = bm mv roa roe/fixone;       /*** Firm Fixed Effect***/

id year CompanyISIN;

run;

Regular Contributor
Posts: 183

Re: Tabulate only key sas results and transferring those to Excel

But is it possible to see the results like this format?

sample.PNG

Grand Advisor
Posts: 17,316

Re: Tabulate only key sas results and transferring those to Excel

By default, no.  By constructing the table yourself, yes.

Contributor
Posts: 63

Re: Tabulate only key sas results and transferring those to Excel

         /* **********************************************

                   Generate Regression Model

    ********************************************* */

  ODS Listing Close;

       Proc Reg Data=X_&i._INFO OutEst=RegOut_X_&i._INFO_REG    RSQUARE  TABLEOUT;

                         Model  COST = QTY  ;

   * Plot Cost * Qty;

                       Run;

                       Quit;

  

  Data RegOut_X_&i._INFO_REG;

  SET RegOut_X_&i._INFO_REG;

  IF _TYPE_ IN ('PARMS','PVALUE');

  FORMAT QTY best.;

  RUN;

     

     Proc Format;        

  Picture MyFmt

  low - 0    = '99999999.99 ' (prefix=' - ')

  0 - 0    =' 0000.00' ( noedit )

  0 - high = ' 999999999.99 ' (prefix=' + ');

     Run; 

  Proc format;

  picture twodec low -<0='09.99'(prefix='  -  ')

  0 <- high='999999999' (prefix='  +  ');

  run;

                Proc SQL NoPrint; Select  _RSQ_  INTO:RSQ from RegOut_X_&i._INFO_REG where _TYPE_ = 'PARMS'; Run;Quit; 

  Proc SQL NoPrint; Select  QTY  INTOSmiley TongueVALUE from RegOut_X_&i._INFO_REG where _TYPE_ = 'PVALUE';Run; Quit;   

                Proc SQL NoPrint; Select   Put(Qty,MyFmt.)   INTO:Qty_Int from RegOut_X_&i._INFO_REG where _TYPE_ = 'PARMS'; Run;Quit;  

  Proc SQL NoPrint; Select  tRIM(pUT(Round(Intercept),COMMA20.))  INTO:Intercept_R from RegOut_X_&i._INFO_REG where _TYPE_ = 'PARMS'; Run;Quit;      

  Proc SQL NoPrint; Select  Sum(_p_ ,_EDF_) INTO:NumOBS from RegOut_X_&i._INFO_REG where _TYPE_ = 'PARMS'; Run;Quit;

  

             

  

  %PUT RSQ=&rsq;%PUT PVALUE=&PVALUE;%PUT Qty_Int=&Qty_Int;%PUT Intercept_R=&Intercept_R ;%PUT NumObs =&NumObs; 

  

  Data RegOut_X_&i._INFO_REG;

  SET RegOut_X_&i._INFO_REG;

  IF _TYPE_ IN ('PARMS','PVALUE');

  FORMAT QTY best.;

  RUN;

    Data A_RegOut_X_&i._INFO_REG ;

    Format Costlabel $4. Regression_Equation  Model $Char125.; Length Model $ 125;

     Set RegOut_X_&i._INFO_REG ;

  IF Qty_Int=0 and Intercept_R=0 then delete;    /* Absurd */

  CostLabel = 'Cost';

  Qty_Int = QTY;

                          R_SQ = "&RSQ";

  P_Value = "&PVALUE";

  Intercept_R =  Round(Intercept) ; 

  

                  

   /* **********************************************

      Define Regression Equation

      ********************************************* */

                      Format Sign $1.;

  if   Qty_Int < 0 then Sign = ' ';

  else if   Qty_Int > 0 then Sign = '+';

                    If Sign = '+' then Regression_Equation  = Left(Trim(Compbl(CostLabel ||' = '|| Put(Intercept_R,comma20.)||' '|| Sign ||round(qty_int) ||' * Qty')));

  if Sign = ' ' then Regression_Equation  = Left(Trim(Compbl(CostLabel ||' = '|| Put(Intercept_R,comma20.)||' '|| Round(Qty_Int,0.01)   ||' * Qty')));

                  

          %Let RSQ= %sysevalf(&RSQ*100,ceil) ; %put rsq = &rsq;

  R_Sq_PCT=R_Sq*100;

  

         If  . < P_Value <= 0.05 then do;

                       If (  0 < R_SQ <  0.25) then MODEL="Significant : Model Weak and explains &RSQ % of total variations.";

   else If (.25 < R_SQ <  0.50) then MODEL="Significant : Model Fair and explains &RSQ % of total variations.";

   else If (.50 < R_SQ <  0.75) then MODEL="Significant : Model Moderate and explains &RSQ % of total variations.";

   else If (.75 < R_SQ <  1.00) then MODEL="Significant : Model Strong and explains &RSQ % of total variations.";  

                       End;

    Else If P_Value GT 0.05 then do;

                 MODEL="Statistically InSignificant : Model Doesn't Fits Well (Prob > 0.05) and explains only &RSQ % of total variations..";

    End;

        Run;

  

                  /* **********************************************

                   Identify and isolate records

      ************************************************ */

  Data ELG NELG;

           Set RegOut_X_&i._INFO_REG;

   

    IF Qty_Int=0 and Intercept_R=0 then delete;    /* Absurd */

     Label Intercept_R='Intercept(Bo) ' Qty_Int='B1(Slope)' Lin_In_Lin_Out='Lin In - Lin Out' P_Value='P-Value' R_SQ='R_Square' NumOBS='No. of Records'

                        Regression_Equation='Regression Equation'    Model='Model Status'; 

    IF Substr(Model,1,19 ) = 'Significant : Model' then output ELG;

      if P_Value GT 0.05  then output NELG;

    

  Run;

        

           /* ******************************************

                         Extra EXCEL Tabs

              ****************************************** */

                   ODS LISTING CLOSE;

  /* **********************************************

               Using ODS, create an Excel file

         ************************************************ */

          

  ODS Tagsets.ExcelXP File="H:\Analysis\Test_&RunDate..xls" tagset=excelxp

  Style=SASWEB OPTIONS (absolute_column_width='9,9,25,20,20,20,9,9,9,9,15,25,9,9,9,9,6,40' 

  fittopage='yes' pages_fitwidth='1' pages_fitheight='100' autofit_height='yes' center_horizontal='yes' center_vertical='yes' FitToPage = 'yes'

  Sheet_name ="Test_Eligible" Autofit_Height = 'Yes' Orientation = 'landscape'   );

      

  Proc Print data = ELG  Label Split='*';

  

  Format InterCept_R   Comma20. ;

  Run;

  ODS   Tagsets.ExcelXP

  Style=SASWEB  OPTIONS (absolute_column_width='9,9,25,20,20,20,9,9,9,9,15,25,9,9,9,9,6,40' 

  fittopage='yes' pages_fitwidth='1' pages_fitheight='100' autofit_height='yes' center_horizontal='yes' center_vertical='yes' FitToPage = 'yes'

  Sheet_name ="Test_Not_Eligible" Autofit_Height = 'Yes' Orientation = 'landscape'   );

  

  Proc Print data = NELG  Label Split='*';;

  

  Format InterCept_R   Comma20. ;

  Run;

  ODS Tagsets.ExcelXP close;

  ODS listing;

Regular Contributor
Posts: 183

Re: Tabulate only key sas results and transferring those to Excel

Hi, thanks but it is difficult for me. I would like to run the fixed effects model so I need to use proc panel:

proc panel data = ceobuy;

model car_1 = bm mv roa roe/fixone;       /*** Firm Fixed Effect***/

id year CompanyISIN;

run;

The name of the dataset is ceobuy. car_1 is the dependent variable. bm, mv, roa and roe are independent variables. So what will be the code if you use these information?

Grand Advisor
Posts: 17,316

Re: Tabulate only key sas results and transferring those to Excel

You haven't posted any sample data so we can't run your code, we'd have to mock up an example. Consider posting sample data and perhaps trying some of the options (even if its difficult) yourself and then reposting code you're specifically having issues with.

Regular Contributor
Posts: 183

Re: Tabulate only key sas results and transferring those to Excel

Hi,

A dataset is included.

Attachment
Ask a Question
Discussion stats
  • 9 replies
  • 465 views
  • 0 likes
  • 4 in conversation