/* 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
*/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.