Creating Monthly Portfolios

[sharmas]

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.

[Linlin]

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

[Ksharp]

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

Ksharp

[manojinpec]

Please post some sample data and resultants dataset.

[sharmas]

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.

[Ksharp · Accepted solution]

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

[sharmas]

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.

[Ksharp]

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

[sharmas]

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.

[Linlin]

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;

[Ksharp]

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

[MikeZdeb]

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;