BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
many thanks, it works as suggested.

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
  • 2 replies
  • 611 views
  • 0 likes
  • 2 in conversation