## Forming Portfolios

Solved
Regular Contributor
Posts: 194

# 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.

 02/01/2004 84760C10 -0.064 02/01/2004 90311910 0.001217 06/01/2004 53591920 0.002222 07/01/2004 09057220 0.011876 07/01/2004 96176510 -0.007919 08/01/2004 50216C10 0.02963 08/01/2004 62915640 0.087363 08/01/2004 68191910 0.017202 09/01/2004 42224510 0.086682 12/01/2004 14313010 -0.018152 12/01/2004 97425010 0 13/01/2004 09367110 -0.004547 13/01/2004 58958410 0.020609 14/01/2004 16516710 -0.00593 14/01/2004 36467W10 -0.013468

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

## Re: Forming Portfolios

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.

create table want as
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

All Replies
Super User
Posts: 10,761

## Re: Forming Portfolios

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: 194

## 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,761

## Re: Forming Portfolios

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;

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

run;

Xia Keshan

Regular Contributor
Posts: 194

## 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,761

## Re: Forming Portfolios

OK. Mr Abu .

```libname x v9 'c:\temp';

%macro portfolio(m=);
data portfolio_&m;
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
quit;
data _null_;
set temp;
call execute('%portfolio(m='||mon||')');
run;

```

Xia Keshan

Regular Contributor
Posts: 194

## 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,761

## Re: Forming Portfolios

libname x v9 'c:\temp';

%macro portfolio(m=);

proc sql;

create table portfolio_&m as

select *,avg(return) as avg_return

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

quit;

data _null_;

set temp;

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

run;

Message was edited by: xia keshan

Regular Contributor
Posts: 194

## 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: 194

## Re: Forming Portfolios

Hi Xia,

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

## Re: Forming Portfolios

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.

create table want as
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.