Contributor
Posts: 43

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

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

Super Contributor
Posts: 1,636

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

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

Posts: 5,482

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

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;

PG

PG
Discussion stats
• 2 replies
• 235 views
• 0 likes
• 3 in conversation