topic Re: how to create weekly from daiky if atleast 4 obs per week are there Finance in SAS Data Management

how to create weekly from daiky if atleast 4 obs per week are there Finance

Ahmad — Fri, 15 Jun 2012 01:14:35 GMT

Hi i have a panel data of daily stock prices for 6 years, i want to create a weekly observations base on the average of the days in a week on which there is a stock return. Furthermore i want to include only those stocks for which atleast 4 observations per week exist. Can anyone help me with this

REgards

Re: how to create weekly from daiky if atleast 4 obs per week are there Finance

Linlin — Fri, 15 Jun 2012 01:34:57 GMT

how about:

data have;

informat date mmddyy10.;

input stock $ return date;

format date mmddyy10.;

cards;

a 2 01/02/2011

a 3 01/03/2011

a 4 01/04/2011

a 2 01/05/2011

a 2 01/06/2011

a 3 01/07/2011

a 4 01/08/2011

a 2 02/09/2011

a 2 01/02/2012

a 3 01/03/2012

a 4 01/04/2012

a 2 01/05/2012

a 2 01/06/2012

a 3 01/07/2012

a 4 01/08/2012

a 2 02/09/2012

;

proc sql;

create table want as select count(*) as days,

stock,intnx('week',date,0) as week format=mmddyy10.,mean(return) as ave_return

from have

group by stock,week

having days>=4;

quit;

proc print;run;

Linlin

Re: how to create weekly from daiky if atleast 4 obs per week are there Finance

PGStats — Fri, 15 Jun 2012 01:52:25 GMT

Try this approach :

/* create random test data for 5 stocks with missing values */
data test(drop=s);
array stock{5};
format date date9.;
do date = '01JAN2010'd to '31DEC2011'd;
      if weekday(date) in (2,3,4,5,6) then do;
            do s = 1 to dim(stock);
                  stock{s} = ifn(rand('UNIFORM') < 0.9, 100 + 10*rand('NORMAL'), .);
                  end;
            output;
            end;
       end;
run;

/* List the stock variable names */
%let stocks=stock1-stock5;

proc transpose data=test out=temp0;
by date;
var &stocks.;
run;

proc sql;
create table temp1 as
select intnx("WEEK", date, 0) as week format=date9., _name_, mean(COL1) as meanStock
from temp0
group by calculated week, _name_
having count(COL1) >= 4;
drop table temp0;
quit;

proc transpose data=temp1 out=want(drop=_name_);
by week;
id _name_;
var meanStock;
run;