LINE statement in ExcelXp tagset issue with call define

[jshaik]

Hi,

I am trying to create titles using LINE statement.But the LINE statement is not giving the expected results.

I need a title with three parts with spaces in between.I tried using compute before _page_ and LINE statement.Its not working as it places all the line statements values in the same row.

Moreover call define statement in the second sheet is causing SAS session crash.

I am looking for something like below above the data  table.Please suggest.

"Report date :                                                                                                                      Reviewer :                                                                                                        reviewed on : " ;

I tried %quote function as well to include spaces as well.

sample code :

ods _all_ close;
ods tagsets.excelXP file = "reporting/report.xls" style =journal options( merge_titles_footnotes='yes');

ods tagsets.ExcelXP options(sheet_name='Data Entry'  embedded_titles='yes'  embedded_footnotes='yes'  absolute_column_width = '20,20,11,11,11,11,11,11,11,11,11,11,11,11,11,15,15,15,15' ) ;

proc report data = data_entry_sheet_final nowd missing split= '*'
style(header)=[background = silver foreground=black  font = ("calibri",10pt) font_weight = BOLD ]
style(column)={font = ("calibri",10pt)}
style(report)={font = ("calibri",10pt)} ;
column 
VAR0
VAR   
&mth1 
&mth2 
&mth3 
&mth4 
&mth5 
&mth6 
&mth7 
&mth8 
&mth9 
&mth10
&mth11
&mth12
&mth13
CHANGE_PER_MONTH
CHANGE_PER_YEAR
AVERAGE_PER_MONTH
Y_T_D 
;

define var0                     /display  ' '                   ;
define VAR       /display  ' '   ;
define &mth1     /display    ;
define &mth2     /display    ;
define &mth3     /display    ;
define &mth4     /display    ;
define &mth5     /display    ;
define &mth6     /display    ;
define &mth7     /display    ;
define &mth8     /display    ;
define &mth9     /display    ;
define &mth10    /display    ;
define &mth11    /display    ;
define &mth12    /display    ;
define &mth13    /display    ;
define CHANGE_PER_MONTH  /display '% CHANGE_PER_MONTH'  ;
define CHANGE_PER_YEAR   /display '% CHANGE_PER_YEAR'  ;
define AVERAGE_PER_MONTH  /display 'AVERAGE_PER_MONTH' ;
define Y_T_D    /display 'Y_T_D'  ;
compute before _page_ ;
line @10 'Report date :'    @65 'Reviewer : '   @100 'Reviewed on:' ;

line @10 'MCO name :'     @65 'Address : '    ;
endcomp;

compute &mth1. ;

if (find(upcase(VAR),'_COST') gt 0 or find(upcase(VAR),'_COST') gt 0) then call define(_col_,'FORMAT','DOLLAR10.2') ;

if find(upcase(VAR),'_COST') eq 0 then call define(_col_,'FORMAT','COMMA10.2') ;

endcomp;

run;

ods tagsets.ExcelXP options(sheet_name='Data Summary'  embedded_titles='yes'  embedded_footnotes='yes' absolute_column_width = '20,20,11,11,11,11,11,11,11,11,11,11,11,11,11,15,15,15,15' ) ;
  proc report data = data_summary_sheet_final_r1c1 nowd missing split= '*'
  style(header)=[background = silver foreground=black  font = ("calibri",10pt) font_weight = BOLD ]
  style(column)={font = ("calibri",10pt)}
  style(report)={font = ("calibri",10pt)} ;
  column 
  VAR0
  VAR   
  &mth1 
  &mth2 
  &mth3 
  &mth4 
  &mth5 
  &mth6 
  &mth7 
  &mth8 
  &mth9 
  &mth10
  &mth11
  &mth12
  &mth13
  CHANGE_PER_MONTH
  CHANGE_PER_YEAR
  AVERAGE_PER_MONTH
  Y_T_D 
  ;

  define var0                     /display  ' '                   ;
  define VAR       /display  ' '   ;
  define &mth1     /display    ;
  define &mth2     /display    ;
  define &mth3     /display    ;
  define &mth4     /display    ;
  define &mth5     /display    ;
  define &mth6     /display    ;
  define &mth7     /display    ;
  define &mth8     /display    ;
  define &mth9     /display    ;
  define &mth10    /display    ;
  define &mth11    /display    ;
  define &mth12    /display    ;
  define &mth13    /display    ;
  define CHANGE_PER_MONTH  /display '% CHANGE_PER_MONTH'  ;
  define CHANGE_PER_YEAR   /display '% CHANGE_PER_YEAR'  ;
  define AVERAGE_PER_MONTH  /display 'AVERAGE_PER_MONTH' ;
  define Y_T_D    /display 'Y_T_D'  ;

compute &mth1. ;

if (find(upcase(VAR),'_COST') gt 0 or find(upcase(VAR),'_COST') gt 0) then call define(_col_,'FORMAT','DOLLAR10.2') ;

if find(upcase(VAR),'_COST') eq 0 then call define(_col_,'FORMAT','COMMA10.2') ;

endcomp;

  run;

ods _all_ close;
ods listing ;     

       
       
       
      

[Cynthia_sas]

Hi:

  I am not exactly sure what you mean when you say that you need "a title with three parts with spaces in between." What you are doing with the COMPUTE block and the LINE statement isn't really a TITLE, it's more like a table "caption" because any text you write will be placed inside the boundary of the table. It would only appear at the top of the first sheet that contains your table. Also, your "@" pointer control won't work with ODS destinations like RTF, PDF, HTML or TAGSETS.EXCELXP. That particular form of the LINE statement

line @10 'Report date :'    @65 'Reviewer : '   @100 'Reviewed on:' ;

line @10 'MCO name :'     @65 'Address : '    ;

would work in the LISTING destination, but not in any other ODS destination. Is this set of text strings something that you want to appear -inside- the worksheet or only when the worksheet is -printed-? There are other ways to create what you want. For example, you can use PRINT_HEADER and PRINT_FOOTER suboptions with TAGSETS.EXCELXP to put "extra" text into the worksheet that would only show at printing time. Or, you can put a title string on multiple lines at left/right/center justify. But your @ pointer control probably won't work the way you want.

cynthia