Date:         Thu, 6 Dec 2007 14:10:02 -0600
Reply-To:     Kevin Morgan <kmorgan@GRAINSCANADA.GC.CA>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kevin Morgan <kmorgan@GRAINSCANADA.GC.CA>
Subject:      Re: write into an Excel sheet
Comments: To: ash007 <RamsamyAshley@GMAIL.COM>
In-Reply-To:  A<b46a7cfa-214f-4e3b-ab42-228a85107158@y5g2000hsf.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Salut Ash:

Je voir ton SAS code est en francais. Mon francais est tres faible, mais
je assai a apprenez quand est possible.

/*/


I have been working with the Tagsets.excel destination and I will
include an example of my code for you:

First off if you have worked with other ODS destinations submit the
following and review your log file:


ods tagsets.excelxp options (doc='help');

What you will get is a list of options that you can use with the ExcelXP
tagset.




/*The excelxp is a good ODS destintaion but before using it too much get
a copy of the most recent update from SAS and run it before you give
your ODS statement. I especially find that the stock 9.1.2 excelXP
tagset doesn't work well when outputting multiple sheets*/

%include "I:\SAS Template\ODS Tagsets\EXCELXP\UpdateXP Tagset.sas";

/*The output from SAS to excel XP usually looks awful, because the
colors are wrong and the font size is wrong. I put a style sheet which
puts a color in the cells of column and row headers and makes the font
9pt Ariel everywhere, boring but but useful */

%include "I:\SAS Template\Style Templates\NMUexc1.sas";


/***********************************************************************
***/

/*Next is the ODS  Path and Style Statement";
ods tagsets.excelxp file="&DIR\Dev.SAS Actuals for &calendar_month
&current_year. &version .xls" style=nmuexc1;

/*** You can assign some global options here like widthfudge but I
haven't***/


/*WorkSheet options are here, I have named the sheet Current, Made the
titles appear on the page, set the orientation, and even set a sheet
interval to NONE, I did that because I wanted to have two proc outputs
on one page*/

ods tagsets.excelxp options (sheet_name='Current' embedded_titles='Yes'
Orientation='Landscape' sheet_interval='None');


proc tabulate data=UNLOADS; title1 ' ';
        title2 "NET Unloads Inspected & Weighed by Region";
        title3 "Based on FEE FLAGS; Prairie Inspected is creditted to
Prairie";
        title4 "Bayport & Eastern Receipts Inspected & Weighed uses the
MRS data";
        class  source fiscal_month loc_code; var inspect_tonnes
weigh_tonnes inland_tonnes;
        table fiscal_month=' ' all='Total', source=' '*loc_code=' '*
                (inspect_tonnes='Receipts Inspected'
weigh_tonnes='Receipts Weighed' inland_tonnes='Prairie
Inspected')*f=comma12.;
        keylabel sum=' '; format fiscal_month fisc_month. LOC_CODE
$LOC_CODE.;
        run; title1; title2; title3; title4;


proc tabulate data=SHIPMENTS; title1 ' ';
        title2 "NET Shipments Inspected & Weighed by Region";
        title3 "Based on Feed Flags (VC, VR, LH -from OCR; BP & NH from
SHIPMENTS; EA from MRS)";
        title4 "BP & EA uses MRS data; VC, VR, LH uses OCR; NH uses ISA
Vessels";
        class  source fiscal_month loc_code; var inspect_tonnes
weigh_tonnes;
        table fiscal_month=' ' all='Total', source=' '*loc_code=' '*
                (inspect_tonnes='Shipments Inspected'
weigh_tonnes='Shipments Weighed')*f=comma12.;
        keylabel sum=' '; format fiscal_month fisc_month. LOC_CODE
$LOC_CODE.;;
        run; title1; title2;title3; title4;


/*For the following note that the sheet_interval option is now PROC
at the moment I only have one proc and so I know I will only have a
single sheet */


ods tagsets.excelxp options (sheet_name='Prairie Shipments'
embedded_titles='Yes' Orientation='Landscape' sheet_Interval='Proc');
proc tabulate data=direct_exports_t;
title2 "SHIPMENTS from PRAIRIE to: USA/Mexico  & Canadian Domestic -
DATA PROVIDED BY PRAIRIE OFFICE";
class Fin_dest Month_num;
Var Tonnes;
Table Month_num=" ", Fin_dest=" "*tonnes=" ";
format month_num fisc_month.;
Keylabel Sum=" ";
run;title1; title2;title3; title4;

/*Sheet interval is now none, and so I will get a sheet called imported
grain 1 and another sheet called imported grain 2*/

ods tagsets.excelxp options (sheet_name='Imported Grain'
embedded_titles='Yes' Orientation='Landscape' sheet_interval='None');
proc tabulate data=IMPORTED; title1 ' ';
        title2 "Unloads of IMPORTED GRAIN by Fiscal Month & Region";
        class  fiscal_month loc_code;  var inspect_tonnes weigh_tonnes
inland_tonnes;
        table fiscal_month=' ' all='Total', loc_code=' '*
        (inspect_tonnes='Receipts Inspection' weigh_tonnes='Receipts
Weighing' inland_tonnes='Prairie Inspected')*f=comma12.;
        keylabel sum=' '; FORMAT fiscal_month fisc_month.  LOC_CODE
$LOC_CODE.;
        run; title1; title2;title3; title4;

proc tabulate data=IMPORTED_SHIPMENTS; title1 ' ';
        title2 "SHIPMENTS of IMPORTED GRAIN by Fiscal Month & Region";
        class  fiscal_month loc_code;  var inspect_tonnes weigh_tonnes ;
        table fiscal_month=' ' all='Total', loc_code=' '*
        (inspect_tonnes='Shipments Inspected' weigh_tonnes='Shipments
Weighed')*f=comma12.;
        keylabel sum=' '; FORMAT fiscal_month fisc_month.  LOC_CODE
$LOC_CODE.;
        run; title1; title2;title3; title4;
ods Tagsets.excelxp close;