BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ramani
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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 Facebook 2000
Oct-90 Microsoft -4000

View solution in original post

8 REPLIES 8
ChrisNZ
Tourmaline | Level 20

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 Facebook 2000
Oct-90 Microsoft -4000
ChrisNZ
Tourmaline | Level 20

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;

 

art297
Opal | Level 21
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

 

Ramani
Fluorite | Level 6

Thank art297 and ChrisNZ, both of your solutions work..

Ramani
Fluorite | Level 6
Art297, This solution does not work when I extend the data.
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
art297
Opal | Level 21

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

Ramani
Fluorite | Level 6

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.

 

art297
Opal | Level 21

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

 

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
  • 8 replies
  • 684 views
  • 1 like
  • 3 in conversation