BookmarkSubscribeRSS Feed
SanjayM
Calcite | Level 5
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;
1 REPLY 1
Olivier
Pyrite | Level 9
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 593 views
  • 0 likes
  • 2 in conversation