Date:         Wed, 14 Jun 2006 11:32:13 -0400
Reply-To:     "Fehd, Ronald J. (CDC/CCHIS/NCHM)" <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J. (CDC/CCHIS/NCHM)" <rjf2@CDC.GOV>
Subject:      Re: SAS to Excel #362
Content-Type: text/plain; charset="us-ascii"

> From: Phil
> My situation is; I've written some SAS code which lists the
> Top 20 fobs or each of 12 different criteria and I want to
> export these 12 datasets to a single Excel worksheet. The 12
> reports are identical in structure, differing only in
> content, Title & Footnote.
>
> I've had a skim through SAS.com and this goldmine of a ng and
> realised that, apu in SAS, there are several ways to get from
> A to B. So, would any of you nice people care to opine on
> what is the best method to achieve my objective? Is it
> ODS/XML? DDE? Or something else?

Below is code to write to a specific sheet in a spreadsheet

from the GASUG paper:
Building Reusable Programs as Includes or Macros

http://www.gasug.org/papers/Build-Reusable-Slides.pdf

Ron Fehd  the macro maven  CDC Atlanta GA USA RJF2 at cdc dot gov


 /*       name: Export2xls.sas
Requirements: --------------------------
   description: convert data set to Excel
       purpose: write one sheet in Excel file

Contexts: ------------------------------
 program group: conversion
 program  type: subroutine
     SAS  type: macro, with parameters

Specifications: ------------------------
         input: libref, data set name
       process: export, replace
        output: Excel filename, sheet

Usage: ---------------------------------
%Export2xls(In_Lib    = SAShelp
           ,In_Data   = Class
           ,Out_File  = SAShelp-Class
           ,Out_Sheet = Class);
*** .................................... */
%Macro Export2xls
         (In_Data   =
         ,In_Lib    = Work   /* default */
         ,Out_File  =
         ,Out_Sheet = Sheet1 /* default */
         )/ des     = 'site: Export to Excel sheet';

PROC Export data    =    &In_Lib..&In_Data.
            outfile = "&Out_File..xls"
            dbms    =  Excel
            replace ;
            sheet   = "&Out_Sheet.";
run;%Mend;