The SAS Output Delivery System and reporting techniques

creating an excel report with each row has different format

Reply
N/A
Posts: 0

creating an excel report with each row has different format

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

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

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 ;
N/A
Posts: 0

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

Thanks a bunch Olivier. It worked

Suren
Ask a Question
Discussion stats
  • 2 replies
  • 150 views
  • 0 likes
  • 2 in conversation