Forming Portfolios

Accepted Solution Solved
Reply
Regular Contributor
Posts: 183
Accepted Solution

Forming Portfolios

Hi Experts,

My dataset looks the following. I need to form portfolios in each month. Portfolios should be constructed as follows: Portfolio in each month includes all stocks (indicate by cusip) that had at least one transaction (tradedate) made during the previous 6 months. For example, portfolio in July 2004 consists of those stocks that had at least one transaction between January and June 2004, portfolio in August 2004 consists of those stocks that had at least one transaction between February and July 2004, and so on.

Tradedate     cusip         Return

02/01/200484760C10-0.064
02/01/2004903119100.001217
06/01/2004535919200.002222
07/01/2004090572200.011876
07/01/200496176510-0.007919
08/01/200450216C100.02963
08/01/2004629156400.087363
08/01/2004681919100.017202
09/01/2004422245100.086682
12/01/200414313010-0.018152
12/01/2004974250100
13/01/200409367110-0.004547
13/01/2004589584100.020609
14/01/200416516710-0.00593
14/01/200436467W10-0.013468

Accepted Solutions
Solution
‎04-24-2015 09:21 AM
Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

Why not do it on your own based on my code to enhance yourself skill ?

 
libname x v9 'c:\temp';
proc sql;
create table temp as
select distinct input(put(tradedate,monyy7.),monyy7.) as mon format=monyy7.
  from x.Buysample_forffreg ;

create table want as
select a.*,(select avg(return) from  x.Buysample_forffreg
   where tradedate between intnx('month',a.mon,-6,'b') and intnx('month',a.mon,-1,'e') )   as avg_return
 from temp as a;
quit;


Xia Keshan

View solution in original post


All Replies
Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

" had at least one transaction (tradedate) made during the previous 6 months"

You mean at least one transaction for each month or just at least one transaction for all of 6 months ?

And make some more data , You sample data is too small to test .

Xia Keshan

Regular Contributor
Posts: 183

Re: Forming Portfolios

Hi Xia,

At least one transaction during the previous 6 months. I have  sent the whole dataset to your email.

Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

I might am a little confused about your question . By your logic , why not directly extract those obs in that range ?

libname x v9 'c:\temp';

%let m=jul2004 ;

data portfolio;

set x.Buysample_forffreg;

if intnx('month',"01&m"d,-6,'b') le tradedate le intnx('month',"01&m"d,-1,'e');

run;

Xia Keshan

Regular Contributor
Posts: 183

Re: Forming Portfolios

Your 'portfolio' dataset contains those observations from January to June 2004. So this is the portfolio for July 2004. What about for other months? By the by, average returns should be calculated then for each portfolio, i.e. for july 2004, august 2004 ..... There should be a column containing the average return of each portfolio that means new output should have monthly average return of each portfolio.

Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

OK. Mr Abu .

libname x v9 'c:\temp';


%macro portfolio(m=);
data portfolio_&m;
 set x.Buysample_forffreg;
if intnx('month',"01&m"d,-6,'b') le tradedate le intnx('month',"01&m"d,-1,'e');
run;
%mend portfolio;


proc sql;
create table temp as
 select distinct(put(tradedate,monyy7.)) as mon
  from x.Buysample_forffreg     ;
quit;
data _null_;
 set temp;
 call execute('%portfolio(m='||mon||')');
run;


Xia Keshan

Regular Contributor
Posts: 183

Re: Forming Portfolios

Thanks Xia. But as I said in my previous reply that average returns should be calculated then for each portfolio, i.e. for july 2004, august 2004 ..... There should be a column containing the average return of each portfolio that means new output should have monthly average return of each portfolio. So dataset for each portfolio is not required.

Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

libname x v9 'c:\temp';

%macro portfolio(m=);

proc sql;

create table portfolio_&m as

select *,avg(return) as avg_return

  from  x.Buysample_forffreg

   where tradedate between intnx('month',"01&m"d,-6,'b') and intnx('month',"01&m"d,-1,'e');

quit;

%mend portfolio;

proc sql;

create table temp as

select distinct(put(tradedate,monyy7.)) as mon

  from x.Buysample_forffreg ;

quit;

data _null_;

set temp;

call execute('%portfolio(m='||mon||')');

run;

Message was edited by: xia keshan

Regular Contributor
Posts: 183

Re: Forming Portfolios

May be I could not explain you properly. Let me try to explain you again.

If I run your code then datasets of all portfolios are created for each month. I don't need all these datasets. What I need is to have one dataset where there will be observations of average return of each portfolio for each month.

Regular Contributor
Posts: 183

Re: Forming Portfolios

Hi Xia,

Waiting for your reply.

Solution
‎04-24-2015 09:21 AM
Super User
Posts: 10,035

Re: Forming Portfolios

Posted in reply to AbuChowdhury

Why not do it on your own based on my code to enhance yourself skill ?

 
libname x v9 'c:\temp';
proc sql;
create table temp as
select distinct input(put(tradedate,monyy7.),monyy7.) as mon format=monyy7.
  from x.Buysample_forffreg ;

create table want as
select a.*,(select avg(return) from  x.Buysample_forffreg
   where tradedate between intnx('month',a.mon,-6,'b') and intnx('month',a.mon,-1,'e') )   as avg_return
 from temp as a;
quit;


Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 454 views
  • 1 like
  • 2 in conversation