Finalizing the publication-style report by using proc report efforts

[Phil_NZ]

Hi all SAS Users,

By searching, asking, consulting you, now I may go to the final step of getting the code for publication-style report. I hope this discussion also benefits other people who want to convert from STATA code to SAS code totally.

The last code working well so far is:

Input:parm.xls (I also attach the excel file in this post)

Code:

PROC IMPORT OUT= WORK.PARM  DATAFILE= "C:\Users\pnguyen\OneDrive - Massey University\PhD JOURNEY\leniency & operating\test_present\parm.xls"  DBMS=EXCEL REPLACE;
  RANGE="parm";
  GETNAMES=YES;
  MIXED=NO;
  SCANTEXT=YES;
  USEDATE=YES;
  SCANTIME=YES;
RUN;
/*write a dataset with standard error on the same column of coefficient*/
data parm2;
set parm;
if not missing(stderr) and variable not in ('R-square','Adj.R-sq','N. Obs.') then do; 
value=estimate; type='coefficient'; output; end;
if not missing(stderr) then do; value=stderr; type='stderr' ;output; end;
if variable in ('R-square','Adj.R-sq','N. Obs.') then do; value=estimate; type=variable ;output; end; 
run;


proc format;           
	picture stderrf (round)       
     	low-high=' 9.9999)' (prefix='(')                                
            .=' ';                                                  
run;



ods html close;
ods html file="c:\temp\MyHTMLfile.htm";;
title;
proc report data=parm2 nowd  out=temptbl;
*column model numord variable type dependent, value;
column numord variable type dependent, (value);
define numord /group order=data noprint;
define variable / group order=data ' ';
define type / group order=data noprint;
define dependent / across ' ';
define value /analysis sum;

compute value;
  array cc _c5_ _c7_ _c9_ ;
      if type='stderr' then do;
         call define(_col_,'format','stderrf.');
      end;
	  else if type='coefficient' then do; 
		call define(_col_,'format','8.4');
        do i=1 to 3;
        if 0.05<cc(i) <= 0.1  then call define(_col_, "style", "style=[ posttext='*']" ); 
        else if 0.01 <cc(i) <=0.05 then call define(_col_, "style", "style=[ posttext='**']" ); 
        else if cc(i) <= 0.01   then call define(_col_, "style", "style=[ posttext='***']" );
		end;
		end;
   endcomp;
run;

Output:

However, it works well only if the three columns have all 6 variables from X1 to X6 and the intercept.

I have two question here which irritate me:

1. How can we change the display of "value" in second, third, and fourth column on the Output Table (under Y1,Y2,Y3 respectively) to "aaa", "bbb", "xyz" ?

2. When I adjust the input by delete X3-X6 associated with dependent Y1, X4-X6 associated with dependent Y2 as the input below:

input:palmz (I also attached the file)

I run the same code as above and my output is:

I am wondering how can I push the R-square, Adj.R-sqr, and N.Ods to the bottom of the Output Table and how to blank the yellowed cells.

Many thanks and warmest regards,

Phil.