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
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.
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.