BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sharmas
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

11 REPLIES 11
Linlin
Lapis Lazuli | Level 10

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
Super User

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

Ksharp

manojinpec
Obsidian | Level 7

Please post some sample data and resultants dataset.

sharmas
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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
Super User

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
Rhodochrosite | Level 12

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 3612 views
  • 7 likes
  • 5 in conversation