BookmarkSubscribeRSS Feed
Vish33
Lapis Lazuli | Level 10

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

2 REPLIES 2
sahkul
Fluorite | Level 6

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
SAS Super FREQ

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3031 views
  • 0 likes
  • 3 in conversation