topic Creating Monthly Portfolios in SAS Procedures

Creating Monthly Portfolios

sharmas — Tue, 27 Mar 2012 20:06:49 GMT

Hey all,

I am new to SAS so I'm finding it hard to code in it. I have a bunch of monthly return data for many stocks (jan 1960-dec 1981). I want to create a portfolio for each month by creating the average for a month (from about 2000 stocks for each month). I couldn't find a way to loop it by time so that I can do this at once instead of doing it again. Help is much appreciated.

Creating Monthly Portfolios

Linlin — Tue, 27 Mar 2012 22:01:34 GMT

Is this helpful?

data have;

informat date ddmmyy10.;

input DATE return;

yrmon=put(DATE,yymmd.);

cards;

1/1/2000 0.25

1/2/2000 0.32

2/2/2000 0.45

3/3/2000 0.15

4/4/2000 0.20

3/4/2000 0.20

;

proc means data=have noprint ;

var return;

class yrmon;

output out=temp(where=(_type_=1)) mean=;

run;

proc print data=temp;run;

Obs yrmon _TYPE_ _FREQ_ return

1 2000-01 1 1 0.250

2 2000-02 1 2 0.385

3 2000-03 1 1 0.150

4 2000-04 1 2 0.200

Linlin

Creating Monthly Portfolios

Ksharp — Wed, 28 Mar 2012 03:46:01 GMT

You need to post some sample data and output you want.

Ksharp

Creating Monthly Portfolios

manojinpec — Wed, 28 Mar 2012 04:17:53 GMT

Please post some sample data and resultants dataset.

Creating Monthly Portfolios

sharmas — Wed, 28 Mar 2012 16:21:57 GMT

Hey all,

Thanks for the answers. Linlin made it simple. But here's what I want.

This is what my data sets look like

Date Firm No. Price Volume Return Share_outstanding MarketCap Illiquidity

1-85 1 2.2

1-85 2 3.2

1-85 3 1

2-85 1 3

2-85 2 4

2-85 3 5

12-2011

Output Wanted

Date Firm No. Price Volume Return Share_outstanding MarketCap Illiquidity PortRet PortMktCap

1-85 1 2.2 2.1

1-85 2 3.2 2.1

1-85 3 1 2.1

2-85 1 3 4

2-85 2 4 4

2-85 3 5 4

I need to find the average return and MarketCap for each month to create a monthly portfolio and portfolios marketCap. Also, at the end, I want it placed in the respective months so that I can calculate a new variable Measure= Illiquidity * (PortMktCap/InitialMktCap(1-85 here))

I'm very new to SAS. Just learning hte DATA and PROC steps, but a huge project came up. Thank you everyone for helping me out. I appreciate it.

Re: Creating Monthly Portfolios

Ksharp — Thu, 29 Mar 2012 02:54:34 GMT

OK. easy.

data x;
input Date : date9.     Firm  Share_outstanding ;
cards;
1jan85        1       2.2         
2jan85        2       3.2                   
3jan85        3        1                  
1feb85        1        3                 
2feb85         2       4        
3feb85         3       5   
;
run; 
proc summary data=x nway;
class date;
format date monyy.;
var Share_outstanding;
output out=temp(drop=_:) mean=PortMktCap;
run;
data want;
 merge x temp;
 by date groupformat;
 format date monyy.;
run;

Ksharp

Creating Monthly Portfolios

sharmas — Fri, 30 Mar 2012 17:54:29 GMT

Thanks very much guys. This has been really helpful and I have been able to get a good start in learning SAS. A small step at a time. But I'm confused about one more thing. Would appreciate it if you can help me out here.

here is what i have

Firm identifier Year Liquidity Liq_Std Size

1 1985 2.3 4.5 5

2 1985

3 1985

1 1986

2 1986

3 1986

4 1986

5 1986

and...The year goes till 2011 and there are probably 100-200 firms in each year (not balanced in every year). I have to divide the firms into 25 different groups based on their value of liquidity, liq_std, and size (highs in one, med high in another.........low in last ) by year.

So basically, I will have 3 different 25 portfolio's each year for liquidity, liq_std and size based on its value.

I was thinking about using proc rank but wasn't sure how to go about it. Any help is much appreciated. Thanks again.

Creating Monthly Portfolios

Ksharp — Sat, 31 Mar 2012 02:39:42 GMT

So Did you check the documentation ?

data elect;
input Firm     Year            Liquidity        Liq_Std        Size;
datalines;
1                         1985            2.3               4.5              5
2                         1985            2.3               4.3             2
3                          1985          2.3               4.5              5
1                          1986           2.2               2.5              3
2                           1986           2.1               4.5              3
3                          1986           2.4               2.5              6
4                          1986           4.3               4.4              8
5                           1986           5.3               4.5              5
;
proc sort data=elect;by year;run;
proc rank data=elect out=results groups=5 ;
by year;
var  Liquidity        Liq_Std        Size;
ranks  Liquidity_rank        Liq_Std_rank        Size_rank;
run;

Ksharp

Creating Monthly Portfolios

sharmas — Sun, 01 Apr 2012 18:14:08 GMT

Thanks for all the help. I really do appreciate it. Getting a hang of SAS. But ran into yet another problem, hopefully the last one. So what I have is,

Date Port no. Return Liquidity

1985 1 2.5 3

1985 2 2.8 3.2

1985 3 2.3 3.4

1986 1 3.2 2.3

1986 2 3.5 2.1

1986 3 2.4 1.8

But I want it in a sort of panel format. What i want is:

Port 1_return Port 2_return Port3_return Port1_liquidity Port2_liquidity Port3_liquidity

Date

1985 2.5 2.8 2.3 3 3.2 3.4

1986

1987

and so forth.

Ksharp, thank you for all the help. Hopefully, this is the last one. Thanks bunch.

Creating Monthly Portfolios

Linlin — Sun, 01 Apr 2012 22:03:53 GMT

try this one:

data have;

input Date Port_no Return Liquidity;

cards;

1985 1 2.5 3

1985 2 2.8 3.2

1985 3 2.3 3.4

1986 1 3.2 2.3

1986 2 3.5 2.1

1986 3 2.4 1.8

;

data want(keep= date port_r: port_l:);

array port_return{3} ;

array port_liquity{3} ;

count=0;

do until(last.date);

set have;

by date;

count+1;

port_return{count}=return;port_liquity{count}=liquidity;

end;

run;

proc print;run;

Re: Creating Monthly Portfolios

Ksharp — Fri, 06 Apr 2012 01:59:57 GMT

Another variety version of Linlin's.

data have;
input Date       Port_no     Return     Liquidity;
cards;
1985        1               2.5            3
1985        2               2.8            3.2
1985        3               2.3            3.4
1986        1               3.2             2.3
1986        2               3.5             2.1 
1986        3               2.4              1.8
;
run; 

 
proc sort data=have;by date port_no;run;
data want(keep= date port_r: port_l:);
  array port_return{3} ;
  array port_liquity{3} ;
  do until(last.date);
   set have;
   by date;
   port_return{Port_no}=return;port_liquity{Port_no}=liquidity;
   end;
run;

Ksharp

Re: Creating Monthly Portfolios

MikeZdeb — Fri, 06 Apr 2012 02:35:50 GMT

hi ... here are a couple alternatives to the already posted solutions ...

data x;

input date : date9. firm share_outstanding ;

datalines;

1jan85 1 2.2

2jan85 2 3.2

3jan85 3 1

1feb85 1 3

2feb85 2 4

3feb85 3 5

;

proc sql;

create table want as

select date format=monyy., firm, share_outstanding, mean(share_outstanding) as portmktcap from x

group by put(date,monyy.)

order by date;

quit;

and ...

data have;

input date port_no return liquidity;

cards;

1985 1 2.5 3

1985 2 2.8 3.2

1985 3 2.3 3.4

1986 1 3.2 2.3

1986 2 3.5 2.1

1986 3 2.4 1.8

;

proc summary nway data=have (rename=(return=port_return liquidity=port_liquidity));

class date;

output out=want (drop=_type_ _freq_) idgroup(out[3](port_return port_liquidity)=);

run;