CSV with customised sub total labels.

[SanjayM]

Thanks for your comments on
http://support.sas.com/forums/message.jspa?messageID=8139#8139

It worked fine.

I am now trying to created a CSV file with the below code. Note there are sub total labels as 'TOTAL' and 'GRAND TOTAL' which creates a problem in the output.

I want the output as

===============================================

"Col1","Col2","Col3","Col4"
"Col1","Col2","Col3","Col4"
"Col1","Col2","Col3","Col4"
TOTAL for Africa subtotal

"Col1","Col2","Col3","Col4"
"Col1","Col2","Col3","Col4"
"Col1","Col2","Col3","Col4"
TOTAL for Canada subtotal

GRAND TOTAL value
===============================================

Please help. See code below.

ods xml file="/apps/sas/ReportsRepository/ExportCSV.csv" type=csvall;
proc report data=sashelp.shoes nowd;
COLUMN Region newreg Product newprod Subsidiary Sales;
DEFINE Region / group width=20 noprint;
define newreg /computed 'Region';

DEFINE product / group width=20 noprint;
define newprod /computed 'Product';

DEFINE Sales / ANALYSIS SUM;
*break after Product / summarize;

compute before Region;
** "grab" the region value before it is suppressed;
holdreg = Region;
endcomp;
compute newreg / character length=20;
newreg = holdreg;
endcomp;

compute before Product;
** "grab" the product value before it is suppressed;
holdprod = Product;
endcomp;
compute newprod/ character length=20;
newprod = holdprod;
endcomp;

COMPUTE AFTER Product ;
LINE @59 'TOTAL '
@89 Sales.Sum ;
LINE '';
ENDCOMP;
COMPUTE AFTER ;
LINE @59 'GRAND TOTAL'
@89 Sales.Sum;
LINE '';
ENDCOMP;
run;
ods _all_ close;

[Olivier]

I would have tried something that is not really ODS-style, but quite efficient for creating customized flat files like this one...

PROC SORT DATA = sashelp.shoes OUT = work.shoes ;
BY product ;
RUN ;
DATA _NULL_ ;
SET work.shoes END=theEnd ;
BY region ;

RETAIN regSales totSales ;

FILE "/apps/sas/ReportsRepository/ExportCSV.csv" ;

IF _N_=1 THEN totSales = 0 ;
IF FIRST.region THEN regSales = 0 ;
totSales + sales ;
regSales + sales ;

PUT col1 "," col2 "," col3 "," col4 ;

IF LAST.region THEN DO ;
PUT "TOTAL for " region regSales 12.2 ;
PUT ;
END ;

IF theEnd THEN PUT "GRAND TOTAL " totSales 15.2 ;
RUN ;

Is that fine for you ?
Olivier