topic creating an excel report with each row has different format in ODS and Base Reporting

creating an excel report with each row has different format

deleted_user — Wed, 20 Dec 2006 06:35:20 GMT

HI
I would like know how in sas ( may be using proc report and ods) to create an excel report with each row has different format.I tried few methods. But didn't succeed.
for e,g:

Say I have a table with the a single row being sale and colums being type , Jan ,Feb, March. Another table with some thing similar but premium.Another one with closeratio and the respective months

fo e.g

sale table
type jan feb mar
sale 1000 500 800

saleindollar
type jan feb mar
saledol $39000 $8500 $15000

saleratio
type jan feb mar
saleratio .8 .5 .6

I would like to create an excel output something like

summary
--------
type jan feb Mar
Sale 1000 500 800
saledol $39000 $8500 $15000
saleratio .8 .5 .6

Any idea how to create a similar report. Thanks in advance for the help

Suren

Re: creating an excel report with each row has different format

Olivier — Wed, 20 Dec 2006 08:16:46 GMT

I think that's a job for PROC TABULATE ; you can add ODS statements to recover the output in Excel, with ODS HTML (if you're using SAS v8 and/or Excel 2000) or ODS TAGSETS.EXCELXP (if you're using SAS 9 and Excel 2003 or more recent versions).
Before using the Tabulate procedure, you must transpose your data a little, something like :

DATA work.test ;
INPUT type $ jan feb mar ;
CARDS ;
sale 1000 500 800
saledol 39000 8500 15000
saleratio .8 .5 .6
;
RUN ;
PROC TRANSPOSE DATA = work.test
OUT = work.transposed
(RENAME = (_name_ = month)) ;
ID type ;
RUN ;
PROC TABULATE DATA = work.transposed ORDER = DATA ;
CLASS month ;
VAR sale saledol salerati ;
TABLE (sale * F=BEST12.
saledol * F=DOLLAR12.
salerati * F=PERCENT8.2),
month=" " * SUM = " ";
RUN ;

Re: creating an excel report with each row has different format

deleted_user — Thu, 21 Dec 2006 04:07:43 GMT

Thanks a bunch Olivier. It worked

Suren