get monthly index constituents
Posted: 30 November 2015 02:08 PM   [ Ignore ]
Newbie
Rank
Total Posts:  3
Joined  2015-11-25

Hello,

I was able to get yearly S&P500; constituents, but I am struggling to get similar results on a monthly basis.

Here is my code:

rsubmit;
libname comp “/wrds/comp/sasdata/naa”;
proc download data=comp.NAMES_IX   out=work.NAMES_IX;run;
libname comp “/wrds/comp/sasdata/naa/index”;
proc download data=comp.IDXCST_HIS out=work.IDXCST_HIS;run;
endrsubmit;

data sp500;
set Idxcst_his;
if gvkeyx eq “000003” ;
if thru eq . then thru = mdy(1,1,2050); * replacing ‘missing’ (still in index) with a future date;
dFrom = from;
dThru = thru;
format dFrom yymmn.;
format dThru yymmn.;
run;

%macro byyear;
  %do year=1964 %to 2015;
  data set_&year;
  set SP500;
  if dFrom<&year; and dThru>&year;
  run;  /* creates the workfile for each year*/
  data set_&year;
  set set_&year;
  year=&year;
  run; /*adds the column year*/
  data mod_sp500;
  set mod_sp500 set_&year;
  run; /* creates a workfile with all years combined sorted by year*/
%end;
%mend byyear;
%byyear;


/*creates correct sp500 constituents file */
proc append base=sp500yearly data= set_1964 data=mod_sp500 ;run;

I have failed to convert this code to monthly basis. I might be beside the track. Can you help me?

Thank you.

Profile
 
 
Posted: 30 November 2015 03:45 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi gbarbe77,

To make sure I understand correctly: you need to construct a dataset that contains the S&P500; firms for every month since (say) 1964?
(basically will be a table of length 50 years x 12 months x 500 firms)

Best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 30 November 2015 03:59 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  3
Joined  2015-11-25

Yes exactly.

And the wanted format would be


YYYYMM Company name(or GVKEY)
199901 COMPANYX
199901 COMPANYY
      ....
      .... (for the 498 other companies)
      ....
199902 COMPANYX
199902 COMPANYY
      ....

I was able to do it with the code I had. Probably not the best code out there for that situation. But it worked for annually.

Profile
 
 
Posted: 01 December 2015 09:54 AM   [ Ignore ]   [ # 3 ]
Newbie
Rank
Total Posts:  3
Joined  2015-11-25

I was able to do it on an annually basis.***

Still need to do it on a monthly basis.

Profile
 
 
Posted: 01 December 2015 09:18 PM   [ Ignore ]   [ # 4 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

ok, got it; didn’t have time yet to look into it.

Best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 05 December 2015 07:49 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi GB,

This seems to work:

data sp500
set comp.Idxcst_his;
if 
gvkeyx eq "000003" ;
if 
thru eq then thru mdy(1,1,2050); * replacing ‘missing’ (still in indexwith a future date;
dFrom from;
dThru thru;
format dFrom yymmn.;
format dThru yymmn.;
run;

/* create dataset with end-of-month date (eom) for years 1964-2014 */
data months (keep emo);
do 
year 1964 to 2015;
 do 
month 1 to 12 ;
  
/* create date: first of month, -1 to take last day of previous month */
  
eom mdy (month1year) - 1;
  if 
1964 <= year(eom) <=2014 then output;
 
end/* end month */
end/* end year;*/

format eom date9.;
run;

/* match with index info */
proc sql;
 
create table spmonthly as select a.*, b.eom from sp500 amonths b where a.dFrom <= b.eom <= a.dThru
quit;

/* how many obs per month? => most have 500 obs, some months 499, 501 or 502*/
proc sql;
 
create table count as select eomcount(*) as numobs from spmonthly group by eom;
quit

Best Regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile