topic Re: Proc print/Report BY group Totaling in ODS and Base Reporting

Proc print/Report BY group Totaling

deleted_user — Tue, 10 Apr 2007 04:51:22 GMT

For the code below, how I do get the BY group totals? Buy_Sell totals is the sum of both 'BUY' as well as 'SELL' currently, while i'd like the totals to be of all BUY separately and all 'SELL' separately.

I could do it using first.Buy_Sell logic(an example is there on your support site) but I want to know whether it's easier to do that using one of proc print/report/tabulate or any other proc you can suggest.

Shanks,

data trades;
/* read the data */
informat trade_date date9.;
length symbol $12;
input trade_date
symbol $
quantity
Buy_Sell $
Price
Brokerage
Service_Tax
STT
;
/* calculate the costs */
buy_sell_total=quantity * price;
nett_total= buy_sell_total + Brokerage + Service_Tax + STT;
cards;
22-Feb-07 HLLLTDEQNR 10 Buy 196.4 15 1.84 2.46
22-Feb-07 ITCLTDEQNR 10 Buy 175.75 15 1.84 2.2
22-Feb-07 TINCOIEQNR 50 Buy 53.2 15 1.84 3.33
24-Feb-07 IFCLTDEQNR 20 Buy 30.25 15 1.84 1
26-Feb-07 HLLLTDEQNR 10 Sell 174 15 1.84 2.46
01-Mar-07 DABINDEQNR 10 Buy 96 15 1.84 1
01-Mar-07 RELPETEQNR 20 Buy 66.6 15 1.84 2
28-Mar-07 RELPETEQNR 50 Buy 74.5 18.5 2.26 5
09-Mar-07 ITCLTDEQNR 20 Buy 159 16 1.96 4
30-MAR-07 ITCLTDEQNR 20 Buy 151 15.20 1.86 4
;;;;
run;

proc sort data=trades;
by Buy_sell trade_date;
run;

proc print data=trades noobs;
var trade_date symbol brokerage Service_tax STT buy_sell_total nett_total;
format trade_date ddmmyyd9.;
sum brokerage Service_tax STT buy_sell_total nett_total;
by buy_sell;
*id Buy_sell;
sumby Buy_sell;
run;

Re: Proc print/Report BY group Totaling

Cynthia_sas — Tue, 10 Apr 2007 15:17:30 GMT

Hi!
Try this PROC REPORT code. The NOBYLINE option allows you to suppress the BYLINE and use the value of the by variable (BUY_SELL) in the SAS TITLE statement.

For help with the PROC REPORT code, consult the SAS on-line doc and look for the topic "Concepts: REPORT Procedure".

Good luck!
cynthia
[pre]

options nobyline;
proc report data=trades nowd;
title 'Report for: #byval1';
by buy_sell;
column buy_sell
trade_date symbol brokerage Service_tax
STT buy_sell_total nett_total;
define buy_sell /group noprint;
define trade_date/display f=ddmmyyd9. "Trade/Date";
define symbol /display "Symbol";
define brokerage /sum "Brokerage" width=9;
define Service_tax /sum "Svc Tax";
define STT /sum "STT";
define buy_sell_total /sum "Buy-Sell/Total" f=dollar14.2;
define nett_total /sum "Nett/Total" f=dollar14.2;
break after buy_sell /summarize dol dul;
run;

options byline;
title;
[/pre]

Re: Proc print/Report BY group Totaling

deleted_user — Wed, 11 Apr 2007 04:52:03 GMT

many thanks, it works as suggested.