topic Excel Sheet Append 4 different countries in ODS and Base Reporting

Excel Sheet Append 4 different countries

deleted_user — Mon, 19 Mar 2007 11:29:43 GMT

Hi all,

I supposed have to generate the Report for 15 countries in one workbook. Each country should be in one work-sheet.
write no m using
Example:
DATA _NULL_;
now=today();
ldate=now-30;
CALL SYMPUT("MDATE",PUT(ldate,monname8.));
CALL SYMPUT("YDATE",PUT(ldate,year4.));
CALL SYMPUT("dDATE",PUT(today(),WORDDATE18.));
RUN;

title1 "&title - ESO SUMMARY" italic height=16pt;
footnote1 'Analytics' justify=right;
footnote2 "&ddate" justify=right;

ods html file=" c:\path " style=statdoc;

%rpt;(Report generation for countries through Macro)

ods html close;
ods _all_ close;
ods listing;

Re: Excel Sheet Append 4 different countries

Olivier — Mon, 19 Mar 2007 12:48:25 GMT

Hi.

If you have SAS 9 and Excel 2003 or higher, you can build XML files using ODS TAGSETS.EXCELXP ; this will give you the opportunity to use options like SHEET_INTERVAL=BYGROUP, and adding a BY country ; statement in your report program will automatically create a different worksheet for each country, no matter how many they are.
Details & documentation can be found on :
http://support.sas.com/rnd/base/topics/odsmarkup/
http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf
and the syntax can be displayed in your log running code like :
ODS TAGSETS.EXCELXP OPTIONS(DOC="HELP") FILE = "c:\temp\nothing.xls" ;
ODS TAGSETS.EXCELXP CLOSE ;

Cheers.
Olivier

Re: Excel Sheet Append 4 different countries

deleted_user — Mon, 19 Mar 2007 15:28:49 GMT

For ExcelXP-related programming, you can use my program:

http://sas2themax.com/blogs/owner/archive/2007/02/04/SAS_2D00_to_2D00_Excel-via-XML-by-DelGobbo_2C00_-simplified_2100_.aspx

A detailed how-to article is posted here:

http://sas2themax.com/blogs/owner/archive/2007/02/11/How-to-export-your-SAS-data-as-a-multi_2D00_sheet-Excel-XML-spreadsheet.aspx

You can set, edit, save, and reuse your ExcelXP options -- no need for an *.ini file, macros, or any other external files.

I'd be happy to entertain any questions regarding the program.

Regards, Audi

E-mail: audi@sas2themax.com