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.
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.