Check if a stock was in the fund portfolio at previous date

Reply
Occasional Contributor
Posts: 15

Check if a stock was in the fund portfolio at previous date

[ Edited ]

Hello all,

I would appreciate your help because I am a bit stuck in the sas programming. I have a data that involves the holdings composition of different funds (fund_id) at each quarter (stock_id , number and  prices) 

For each fund_id :

I would like to know for each stock held by a fund_id  at each quarter , if the stock existed on a previous quarter or not (i.e. if the fund held the same stock in a previous quarter). If it is the case ( i.e. if the stock existed on a previous quarter for the same fund), I should recuperate the number and price of that stock  in the previous quarter and creates for them new variables. If it's not the case  I do nothing or I put zeros for the newly created variables.

 

my variables are as follow ( what I have):

 

datefund_idstock_idnumberprice
3/31/2005A891610850045
3/31/2005A2264061062000013.73
3/31/2005A25243Q205900056.9
3/31/2005A2925051041500070.42
3/31/2005A633067103400043.305
3/31/2005A7672041001500129.75
3/31/2005A87403910070032
3/31/2005A545895490085
3/31/2005A8740391001000100
6/30/2005A25243Q2052000120
6/30/2005A292505104500150
6/30/2005A8740364540012
6/30/2005A645646300011
6/30/2005A5454545120013
3/31/2005B633067103500043.305
3/31/2005B7672041001500129.75
3/31/2005B87403910070015
3/31/2005B7777890020
3/31/2005B121212100040
3/31/2005B131313200088
6/30/2005B44456320045
6/30/2005B7672041001000036
6/30/2005B8740391003333100
6/30/2005B4666662014

 

I posted an excel sample data

 

What I want :

datefund_idstock_idnumberpricenumber1price1
3/31/2005A89161085004500
3/31/2005A2264061062000013.7300
3/31/2005A25243Q205900056.900
3/31/2005A2925051041500070.4200
3/31/2005A633067103400043.30500
3/31/2005A7672041001500129.7500
3/31/2005A8740391007003200
3/31/2005A54589549008500
3/31/2005A874039100100010000
6/30/2005A25243Q2052000120900056.9
6/30/2005A2925051045001501500070.42
6/30/2005A874036454001200
6/30/2005A64564630001100
6/30/2005A545454512001300
3/31/2005B633067103500043.30500
3/31/2005B7672041001500129.7500
3/31/2005B8740391007001500
3/31/2005B777789002000
3/31/2005B12121210004000
3/31/2005B1313132000880

0

 

 

 

Thank you in advance for your precious help.

 

PROC Star
Posts: 1,283

Re: Check if a stock was in the fund portfolio at previous date

Something like this?

 

data have;
input date:mmddyy10. fund_id$ stock_id:$20. number price;
format date mmddyy10.;
datalines;
3/31/2005 A 8916108 500 45
3/31/2005 A 226406106 20000 13.73
3/31/2005 A 25243Q205 9000 56.9
3/31/2005 A 292505104 15000 70.42
3/31/2005 A 633067103 4000 43.305
3/31/2005 A 767204100 1500 129.75
3/31/2005 A 874039100 700 32
3/31/2005 A 5458954 900 85
3/31/2005 A 874039100 1000 100
6/30/2005 A 25243Q205 2000 120
6/30/2005 A 292505104 500 150
6/30/2005 A 87403645 400 12
6/30/2005 A 645646 3000 11
6/30/2005 A 5454545 1200 13
3/31/2005 B 633067103 5000 43.305
3/31/2005 B 767204100 1500 129.75
3/31/2005 B 874039100 700 15
3/31/2005 B 77778 900 20
3/31/2005 B 121212 1000 40
3/31/2005 B 131313 2000 88
6/30/2005 B 44456 3200 45
6/30/2005 B 767204100 10000 36
6/30/2005 B 874039100 3333 100
6/30/2005 B 466666 20 14
;

proc sort data=have out=temp;
   by fund_id stock_id date;
run;

data want;
   set temp;
   by fund_id stock_id;
   number1=.; price1=.;
   lag_date=lag1(date);lag_stock=lag1(stock_id);lag_number=lag1(number);lag_price=lag1(price);
   if (stock_id=lag_stock & intck('month', lag_date, date)=3) then do;
      number1=lag_number;
      price1=lag_price;
   end;
   drop lag:;
   format lag_date mmddyy10.;
run;

proc sort data=want;
   by fund_id date stock_id;
run;
Trusted Advisor
Posts: 1,345

Re: Check if a stock was in the fund portfolio at previous date

This is the same question you posted in Finding a stock within fund holdings in previous quarter, which has many more answers.  I'd suggest closing this question.

Ask a Question
Discussion stats
  • 2 replies
  • 85 views
  • 0 likes
  • 3 in conversation