How to Design cover pages in excel using ODS

[Vish33]

Hi ALL,

I want to create coverpage with the titles and images with bar lines and colors included in it. Can u please look into the sample excel provided and suggest me ..using ODS

[sahkul]

I'll create a template with cover page and glossary in required format. Create named ranges in places where you want to update data.

Each time report needs to be updated, copy and paste the template into a location of your choice and using data step update data using the named ranges.

x "copy c:\temp\template.xls

            c:\report\report.xls";

libname output excel "c:\report\report.xls";

proc datasets lib=output;

       delete namedrange;

quit;

data output.namedrange;

set datasetname;

run;

libname output clear;

You might have to create three named ranges to get the report in the format you want.

[Cynthia_sas]

Hi:

  With ODS, your choices will be limited to ODS TAGSETS.MSOFFICE2K_X and ODS TAGSETS.EXCELXP. Multi-sheet workbooks are easier with TAGSETS.EXCELXP, but (by Microsoft design) images are not allowed in the XML file. With MSOFFICE2K_X, you are creating an HTML file, so multi-sheet workbooks are possible, but you have to create separate HTML files and then link them together.

  There have been many previous forum postings on using both of these destinations and the support.sas.com site has an entire section devoted to TAGSETS.MSOFFICE2K_X and another section devoted to TAGSETS.EXCELXP. Searching for either of the destination names should show you all the references that are available.

  Your cover page could be a data set that you display with PROC PRINT or, more likely, PROC REPORT. Your other reports look like fairly standard PROC REPORT output, but without seeing the data (every sheet comes from a separate dataset? the sheets all come from the same dataset?)

  Another factor is whether you have the SAS Add-in for Microsoft Office or are using the BI Platform or whether you will be turning this into a stored process.

  A very simple example is attached below, using TAGSETS.EXCELXP and just some color changes -- no fancy fonts or other cosmetic changes. For more cosmetic changes and/or to switch to TAGSETS.MSOFFICE2K_X, more work is needed on your part. Style templates may have to be used if you need to control the interior border lines. And, out of the box, this example can't have a logo because TAGSETS.EXCELXP creates Office 2003 Spreadsheet XML (which does not allow images). I believe you could have a VBA script or Excel macro save this XML file as standard XLS and then insert your logo in the script or macro, but that is beyond the scope of my example.

  You may want to work with Tech Support on this question, as they can look at all your code and all your data and help you come to a resolution.

cynthia

data cover;
length rline $100;
ordvar=1; rline="Client: Kermit the Frog"; output;
ordvar=2; rline="Muppet Report"; output;
ordvar=3; rline="Start: 07/01/2012"; output;
ordvar=4; rline=" "; output;
ordvar=5; rline=" "; output;
ordvar=6; rline="End: 08/01/2012"; output;
ordvar=7; rline = " "; output;
ordvar=8; rline=" "; output;
ordvar=9; rline=" "; output;
ordvar=10; rline="logo here"; output;
run;
    
title;
ods _all_ close;
ods tagsets.excelxp file='c:\temp\sample.xml' style=sasweb;
       
ods tagsets.excelxp options(sheet_name='Cover');

proc report data=cover nowd noheader missing;
  column ordvar rline;
  define ordvar / order noprint;
  define rline / display
         style(column)={font_weight=bold};
  compute rline;
    if ordvar = 7 then do;
       call define(_col_,'style','style={background=orange foreground=orange}');
    end;
    else if ordvar gt 7 then do;
       call define(_col_,'style','style={background=white foreground=black }');
    end;
    else if ordvar lt 7 then do;
       call define(_col_,'style','style={background=verylightblue foreground=black }');
    end;
  endcomp;
run;

ods tagsets.excelxp options(sheet_name='Report');
proc report data=sashelp.class nowd
  style(header)={background=black foreground=white};
  compute before _page_/ style={just=l foreground=black font_weight=bold};
    line 'Kermit Report';
    line 'From: 07/01/2012';
    line 'To: 08/01/2012';
  endcomp;
run;

ods tagsets.excelxp close;