/* test data. as OzoneX15 posted except the date 11/08/2003 is
manually corrected to 11/08/2006 */
data one;
input tdate :ddmmyy10. fund isin :$12. price :eurox5.
transmod :$4. numberStocks;
cards;
19/02/2004 100174701 BE0003565737 47 buy 3000
19/03/2004 100174701 BE0003565737 44,8 buy 3800
24/03/2004 100174701 BE0003565737 44,59 buy 12050
23/06/2005 100174701 BE0003565737 66,65 buy 3900
11/11/2005 100174701 BE0003565737 73,15 buy 3000
14/11/2005 100174701 BE0003565737 73,2 buy 3800
17/05/2006 100174701 BE0003565737 84,3 buy 250
12/06/2006 100174701 BE0003565737 78,05 buy 800
11/08/2006 100174701 BE0003565737 87,85 sell 40000
11/08/2006 100174701 BE0003565737 87,85 sell 3800
21/09/2006 100174701 BE0003565737 84,75 buy 530
23/11/2006 100174701 BE0003565737 85,3 buy 30
4/12/2006 100174701 BE0003565737 85,95 buy 20
23/01/2007 100174701 BE0003565737 97,65 buy 260
13/02/2007 100174701 BE0003565737 97,8 sell 400
23/02/2007 100174701 BE0003565737 96,2 buy 150
6/03/2007 100174701 BE0003565737 89,67 sell 300
6/03/2007 100174701 BE0003565737 89,67 sell 40000
;
run;
/* assuming that the data are sorted by fund isin and tdate.
create buy-sell observations matching buys and sells on the
first-in, first-out basis */
data two;
/* set up */
length fund 8 isin $12. tid bdate bprice bshare 8
share sdate sprice sshare 8;
dcl hash buys(ordered:'a');
buys.defineKey('tid');
buys.defineData('bdate','bprice','bshare');
buys.defineDone();
dcl hash sells(ordered:'a');
sells.defineKey('tid');
sells.defineData('sdate','sprice','sshare');
sells.defineDone();
dcl hiter buy('buys');
dcl hiter sell('sells');
/* load the hashes with data */
do until (last.isin);
set one;
by fund isin tdate;
tid + 1;
if transmod="buy" then do;
bdate = tdate;
bprice = price;
bshare = numberStocks;
buys.add();
end; else do;
sdate = tdate;
sprice = price;
sshare = numberStocks;
sells.add();
end;
end;
/* prepare for output */
keep fund isin bdate bprice share sdate sprice;
format bdate sdate ddmmyy10. bprice sprice eurox5.2;
/* output */
brc = buy.first();
src = sell.first();
do while(brc = 0 and src = 0);
select;
when (bshare lt sshare) do;
temp = sshare - bshare;
share = bshare;
output;
sshare = temp;
brc = buy.next();
end;
when (bshare = sshare) do;
share = bshare;
output;
brc = buy.next();
src = sell.next();
end;
when (bshare gt sshare) do;
temp = bshare - sshare;
share = sshare;
output;
bshare = temp;
src = sell.next();
end;
otherwise;
end;
end;
/* just in case buy and sell volumns do not match */
do while (brc = 0);
call missing(sdate, sprice);
share = bshare;
output;
brc = buy.next();
end;
do while (src = 0);
call missing(bdate, bprice);
share = sshare;
output;
src = sell.next();
end;
run;
/* check */
proc print data=two;
var bdate bprice share sdate sprice;
run;
/* on lst
Obs bdate bprice share sdate sprice
1 19/02/2004 47,00 3000 11/08/2006 87,85
2 19/03/2004 44,80 3800 11/08/2006 87,85
3 24/03/2004 44,59 12050 11/08/2006 87,85
4 23/06/2005 66,65 3900 11/08/2006 87,85
5 11/11/2005 73,15 3000 11/08/2006 87,85
6 14/11/2005 73,20 3800 11/08/2006 87,85
7 17/05/2006 84,30 250 11/08/2006 87,85
8 12/06/2006 78,05 800 11/08/2006 87,85
9 21/09/2006 84,75 530 11/08/2006 87,85
10 23/11/2006 85,30 30 11/08/2006 87,85
11 04/12/2006 85,95 20 11/08/2006 87,85
12 23/01/2007 97,65 260 11/08/2006 87,85
13 23/02/2007 96,20 150 11/08/2006 87,85
14 . . 8410 11/08/2006 87,85
15 . . 3800 11/08/2006 87,85
16 . . 400 13/02/2007 97,80
17 . . 300 06/03/2007 89,67
18 . . 40000 06/03/2007 89,67
*/
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.