The SAS Output Delivery System and reporting techniques

Excel Sheet Append 4 different countries

Reply
N/A
Posts: 0

Excel Sheet Append 4 different countries

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;
Super Contributor
Posts: 260

Re: Excel Sheet Append 4 different countries

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: Excel Sheet Append 4 different countries

Posted in reply to deleted_user
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...

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_she...

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
Ask a Question
Discussion stats
  • 2 replies
  • 133 views
  • 0 likes
  • 2 in conversation