Title in the excel report is always hidden

Reply
New Contributor
Posts: 2

Title in the excel report is always hidden

Hi,

I'm pretty new to SAS reporting and when i try to generate a SAS excel report, the title row of my excel report is always hidden.

Please find below the code snippet,

%MACRO CREATEXL (SHETINTV,AUTOHGT,CNTRVERT,CNTRHORZ,EMBFOOT,EMBTITLE,ORIENT,SHEETNAM,FITOPAGE);

  ODS TAGSETS.EXCELXP OPTIONS

    (SHEET_INTERVAL = "&SHETINTV"

      AUTOFIT_HEIGHT = "&AUTOHGT"

      CENTER_VERTICAL = "&CNTRVERT"

      CENTER_HORIZONTAL = "&CNTRHORZ"

      EMBEDDED_FOOTNOTES = "&EMBFOOT"

      EMBEDDED_TITLES = "&EMBTITLE"

      ORIENTATION = "&ORIENT"

      SHEET_NAME = "&SHEETNAM"

      FITTOPAGE = "&FITOPAGE"

    );

%MEND CREATEXL;

title_string = '2013 Usage Information - ' || str_month || ' ,' ||  start_year;

title_disclaimer = cats('Reminders: May include data which no longer active. Account counts are as of the run date of the report');

title_date = cats('Report Generated on :', current_mnth,',',year);

call symput('title_string',"'"||put(title_string,$90.)||"'");

call symput('title_disclaimer',"'"||put(title_disclaimer,$90.)||"'");

call symput('title_date',"'"||put(title_date,$90.)||"'");

call symput('sheet_name',put(sheet_name,$20.));

call symput('xlsDte',put(xlsDte,$15.));

call symput('template_MTD',"'"||put(template_MTD,$90.)||"'");

call symput('template_YTD',"'"||put(template_YTD,$90.)||"'");

run;

/* Using CreateXL macro to generate excel report */

ODS TAGSETS.EXCELXP PATH="&reportpath" FILE="Monthly_Report.xls" STYLE=SANSPRINTER

  options(autofilter='all' autofit_height='yes' absolute_column_width='10');

%CREATEXL (NONE,YES,YES,YES,YES,YES,LANDSCAPE,&sheet_name,YES)

RUN;

ODS ESCAPECHAR='^';

TITLE1 h=3 j=left&title_string;

TITLE2 BOLD h=2 j=LEFT COLOR=red &title_disclaimer;

TITLE3 BOLD h=2 j=LEFT COLOR=red &title_date;

PROC REPORT DATA=datapath.source_record NOWINDOWS headline headskip split='*'

  style(report)=[cellspacing=1 borderwidth=1 bordercolor=CXFFFFFE]

  style(header)=[foreground=white background=cx950000 font_face=arial font_weight=bold font_size=1]

  style(column)=[foreground=black background=cxFFFFFF font_face=arial font_size=1]

  style(lines)=[foreground=violet background=violet font_face=arial font_weight=bold font_size=1];

  COLUMN (Region)

        (City)

        (Total_Account_Created)

      ('New Account Created' Account_MTD Account_YTD)    

      ('New Customer Added' Customer_Count_MTD Customer_Count_YTD)    

      ('Account Type'

        ('Saving Account' Savning_Value_MTD Savning_Value_YTD)

        ('Checking Account' Checking_Account_MTD Checking_Account_YTD)

        ('Platinum Account' Platinum_Account_MTD Platinum_Account_YTD)

        ('Credit Account' Credit_Account_MTD  Credit_Account_YTD)

        )

    ;

  

          

/* Formatting the column text of the Report */

  DEFINE Region / display center 'Region';

  DEFINE City / display center 'Total Account Created';

  DEFINE Account_MTD / display center &template_MTD;

  DEFINE Account_YTD / display center &template_YTD;

  DEFINE Customer_Count_MTD / display center &template_MTD;

  DEFINE Customer_Count_YTD / display center &template_YTD;

  DEFINE Checking_Account_MTD / display center &template_MTD;

  DEFINE Checking_Account_YTD / display center &template_YTD;

  DEFINE Platinum_Account_MTD / display center &template_MTD;

  DEFINE Platinum_Account_YTD / display center &template_YTD;

  DEFINE Credit_Account_MTD / display center &template_MTD;

  DEFINE Credit_Account_YTD / display center &template_YTD;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

ODS LISTING;

- Attached the Monthly Adhoc_Report xls generated.

Also is there way where you can apply the style for individual headers in the above scenario instead of applying for the entire header row.

For example i would like to apply the style just for the cell "Account Type" in the header.

Any suggestions will be of great help.

Thank you.

SAS Super FREQ
Posts: 8,744

Re: Title in the excel report is always hidden

Hi:

  There is an interesting Tech Support note (50757 - Some footnotes might not be displayed in worksheets generated with the ExcelXP tagset) that might prompt you to open a track with Tech Support on your issue. I ran a quick test and did get both titles and footnotes, but it was a very simple test. I'd suggest you open a track with Tech Support to figure out whether your issue is related to the issue in the Tech Support track or not.

  Also, in the code below, note how defaults for the headers are set in the PROC REPORT statement, but for the NAME variable, the header justification and colors are changed in the DEFINE statement for NAME.
  

cynthia

ods _all_ close;

ods tagsets.excelxp file='c:\temp\test_titles.xml'

    style=sasweb

    options(embedded_titles="YES" embedded_footnotes="YES");

proc report data=sashelp.class(obs=5) nowd

     style(header)={background=pink color=black};

  title c=purple h=12pt j=l 'Title should be embedded';

  footnote c=red h=10pt j=r 'Footnote should be embedded';

  column name sex age height weight;

  define name / style(column)={just=c}

                style(header)={fontweight=bold background=white color=purple just=r};

run;

ods tagsets.excelxp close;


test_titles_embed.png
New Contributor
Posts: 2

Re: Title in the excel report is always hidden

Thanks a lot for the cynthia. Finally it worked, looks i need to update the ExcelXP tagset .

ODS PATH statement.

ODS PATH(PREPEND) WORK.TEMPLAT(UPDATE);

%INCLUDE "attached code";

http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl

But instead of updating the Tagset on each run, is there any way to have it updated on my server as one time acitivity?

Ask a Question
Discussion stats
  • 2 replies
  • 465 views
  • 3 likes
  • 2 in conversation