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

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

bera00 — Tue, 27 Feb 2018 16:52:25 GMT

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):

date	fund_id	stock_id	number	price
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

I posted an excel sample data

What I want :

date	fund_id	stock_id	number	price	number1	price1
3/31/2005	A	8916108	500	45	0	0
3/31/2005	A	226406106	20000	13.73	0	0
3/31/2005	A	25243Q205	9000	56.9	0	0
3/31/2005	A	292505104	15000	70.42	0	0
3/31/2005	A	633067103	4000	43.305	0	0
3/31/2005	A	767204100	1500	129.75	0	0
3/31/2005	A	874039100	700	32	0	0
3/31/2005	A	5458954	900	85	0	0
3/31/2005	A	874039100	1000	100	0	0
6/30/2005	A	25243Q205	2000	120	9000	56.9
6/30/2005	A	292505104	500	150	15000	70.42
6/30/2005	A	87403645	400	12	0	0
6/30/2005	A	645646	3000	11	0	0
6/30/2005	A	5454545	1200	13	0	0
3/31/2005	B	633067103	5000	43.305	0	0
3/31/2005	B	767204100	1500	129.75	0	0
3/31/2005	B	874039100	700	15	0	0
3/31/2005	B	77778	900	20	0	0
3/31/2005	B	121212	1000	40	0	0
3/31/2005	B	131313	2000	88	0	0

Thank you in advance for your precious help.

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

PeterClemmensen — Tue, 27 Feb 2018 22:02:01 GMT

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;

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

mkeintz — Wed, 28 Feb 2018 03:26:50 GMT

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.