Hi,
I'm trying to wrap my head around how to compute buys and sells by a mutual fund in a particular quarter.
My data looks like this:
Date Fund Stock Shares-held
Jun-90 Fund A Apple 5000
Microsoft 5000
Sep-90 Fund A Apple 6000
Microsoft 4000
Oracle 2000
Dec-90 Fund A Apple 6000
Oracle 2000
Facebook 2000
I'm in need of an output data set something like this
buy/Sell Change in shares
Jun-90 Apple 5000
Microsoft 5000
Sep-90 Apple 1000
Microsoft -1000
Oracle 2000
Dec-90 Microsoft -4000
Facebook 2000
I tried using some combinations of sorting, if statements and lag statements and cannot seem to get all the values I want.
Thank you
-R
Likes this (I'll let you handle the dates properly) ?
data HAVE(index=(A=(FUND STOCK DATE )));
input DATE $ FUND $ STOCK $10. SHARES_HELD;
cards;
Apr-90 FundA Apple 5000
Apr-90 FundA Microsoft 5000
Jun-90 FundA Apple 6000
Jun-90 FundA Microsoft 4000
Jun-90 FundA Oracle 2000
Oct-90 FundA Apple 6000
Oct-90 FundA Oracle 2000
Oct-90 FundA Facebook 2000
run;
proc sql;
create table CARTESIAN as
select * from (select unique DATE from HAVE)
,(select unique FUND from HAVE)
,(select unique STOCK from HAVE)
order by FUND, STOCK, DATE;
quit;
data CHANGES(index=(A=(FUND DATE STOCK)));
merge HAVE CARTESIAN;
by FUND STOCK DATE;
if SHARES_HELD=. then SHARES_HELD=0;
CHANGE=ifn(first.STOCK,SHARES_HELD,SHARES_HELD-lag(SHARES_HELD));
run;
data WANT;
set CHANGES;
by FUND DATE STOCK;
run;
proc print noobs;
var DATE STOCK CHANGE;
where CHANGE;
run;
DATE | STOCK | CHANGE |
---|---|---|
Apr-90 | Apple | 5000 |
Apr-90 | Microsoft | 5000 |
Jun-90 | Apple | 1000 |
Jun-90 | Microsoft | -1000 |
Jun-90 | Oracle | 2000 |
Oct-90 | 2000 | |
Oct-90 | Microsoft | -4000 |
Likes this (I'll let you handle the dates properly) ?
data HAVE(index=(A=(FUND STOCK DATE )));
input DATE $ FUND $ STOCK $10. SHARES_HELD;
cards;
Apr-90 FundA Apple 5000
Apr-90 FundA Microsoft 5000
Jun-90 FundA Apple 6000
Jun-90 FundA Microsoft 4000
Jun-90 FundA Oracle 2000
Oct-90 FundA Apple 6000
Oct-90 FundA Oracle 2000
Oct-90 FundA Facebook 2000
run;
proc sql;
create table CARTESIAN as
select * from (select unique DATE from HAVE)
,(select unique FUND from HAVE)
,(select unique STOCK from HAVE)
order by FUND, STOCK, DATE;
quit;
data CHANGES(index=(A=(FUND DATE STOCK)));
merge HAVE CARTESIAN;
by FUND STOCK DATE;
if SHARES_HELD=. then SHARES_HELD=0;
CHANGE=ifn(first.STOCK,SHARES_HELD,SHARES_HELD-lag(SHARES_HELD));
run;
data WANT;
set CHANGES;
by FUND DATE STOCK;
run;
proc print noobs;
var DATE STOCK CHANGE;
where CHANGE;
run;
DATE | STOCK | CHANGE |
---|---|---|
Apr-90 | Apple | 5000 |
Apr-90 | Microsoft | 5000 |
Jun-90 | Apple | 1000 |
Jun-90 | Microsoft | -1000 |
Jun-90 | Oracle | 2000 |
Oct-90 | 2000 | |
Oct-90 | Microsoft | -4000 |
Slightly more compact:
data CHANGES(index=(A=(FUND DATE STOCK)));
merge HAVE CARTESIAN;
by FUND STOCK DATE;
CHANGE=sum(0,SHARES_HELD,-lag(SHARES_HELD)*^first.STOCK);
run;
data want (drop=_:); set have; retain _date _fund; if not missing(date) then _date=date; else date=_date; if not missing(fund) then _fund=fund; else fund=_fund; run; proc sort data=want; by stock date; run; data want (drop=_:); set want; by stock; _shares_held=ifn(first.stock,0,lag(Shares_held)); change=Shares_held-_Shares_held; run; proc sort data=want; by date fund stock; run;
Art, CEO, AnalystFinder.com
Thank art297 and ChrisNZ, both of your solutions work..
What doesn't work? You'll have to be more specific. Your data format and values have changed quite a bit from your original question, thus I removed the data clean-up section of my code. Unless I'm missing something, it appears to follow your specs:
data have; informat date yymmdd10.; informat fund $8.; informat stock $9.; input Date Fund & Stock Shares_held; cards; 1990-04-30 FundA Apple 5000 1990-04-30 FundA Microsoft 5000 1990-06-30 FundA Apple 6000 1990-06-30 FundA Microsoft 4000 1990-06-30 FundA Oracle 2000 1990-09-30 FundA Apple 6000 1990-09-30 FundA Oracle 2000 1990-09-30 FundA Facebook 2000 1990-12-30 FundA Microsoft 1000 1990-12-30 FundA Apple 6000 ; proc sort data=have out=want; by stock date; run; data want (drop=_:); set want; by stock; _shares_held=ifn(first.stock,0,lag(Shares_held)); change=Shares_held-_Shares_held; run; proc sort data=want; by date fund stock; run;
Art, CEO, AnalystFinder.com
I apologise for not being specific earlier,
Your code captures the difference in shares from one quarter to another. but it does not capture when all the shares of a stock have been sold.
E.g. All of Microsoft is sold in September and hence I need a value where shares_change = -4000 or microsoft.
Microsoft is added back to the portfolio in December, but the value wrongly shows as -3000.
Oracle is also sold in December and hence it is not being captured in December.
Got it! I didn't understand that missing entries meant that all shares had been sold. As such, yes, I'd stick with the code suggested by @ChrisNZ
Art, CEO, AnalystFinder.com
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.