Create portfolios with interbrand values

[Inge12]

Hi all,

I have a new question! I now only have to create 2 simple portfolios with the same dataset (in this situation the data of  2001 is also used)

Portfolio 1: Interbrand value higher then 20

Portfolio 2: Interbrand value lower then 10

The difficult thing about creating these portfolios is that they should only change in the year 2005 and 2006....

This means that the portfolios are created in 2001 (stays the same till 2004) change in 2005, change again in 2006. And the rest of the years the portfolios do not change.

Hope you can help me with this new situation!

Inge

[Ksharp]

You can make a sub-dataset to hold 2005 2006 ,then append the remaining observations.behind it .

proc sort data=have out=temp(where=(year in (2005 2006)));by Brand Year;run;

data portfolio1 portfolio2 portfolio3 portfolio4;

set temp;

by brand;

if Interbrand_Value gt lag(Interbrand_Value) and not first.brand and Interbrand_Value gt 20 then output portfolio1;

  else if Interbrand_Value lt lag(Interbrand_Value) and not first.brand and Interbrand_Value gt 20 then output portfolio2;

   else if Interbrand_Value gt lag(Interbrand_Value) and not first.brand and  Interbrand_Value lt 10 then output portfolio3;

    else if Interbrand_Value lt lag(Interbrand_Value) and not first.brand and  Interbrand_Value lt 10 then output portfolio4;

run;

[Inge12]

Thank you for your help, but I don't think I get the correct output now.

I only want two simple portfolios:

Portfolio 1: Interbrand value higher then 20

Portfolio 2: Interbrand value lower then 10

I want to create these two portfolios in 2001, and keep them the same till 2005, then the portfolios will change and in 2006 I want to change it again. After 2006 the portfolios should stay the same as well. 

This means that when a brand has a interbrand value below 20 in 2002/2003 or 2004 that it still remains in portfolio 1.

I hope you can help me with this.

[Ksharp]

EASY.

data have;
input Brand &$10. Year Interbrand_Value :comma. GVKEY Ticker_symbol$;
cards;
Coca Cola    2001    68,945    003144    KO
Coca Cola    2002    69,637    003144    KO
Coca Cola    2003    70,453    003144    KO
Coca Cola    2004    67,394    003144    KO
Coca Cola    2005    67,525    003144    KO
Coca Cola    2006    67,000    003144    KO
Coca Cola    2007    65,324    003144    KO
Coca Cola    2008    66,667    003144    KO
Coca Cola    2009    68,734    003144    KO
Coca Cola    2010    70,452    003144    KO
Coca Cola    2011    71,861    003144    KO
Coca bbbb    2001    18    003144    Ky
Coca bbbb    2002    20    003144    Ky
Coca bbbb   2003    15    003144    Ky
Coca bbbb   2004    21   003144    Ky
Coca bbbb    2005    16    003144    Ky
Coca bbbb    2006    17    003144    Ky
Coca bbbb    2007    14    003144    Ky
Coca bbbb    2008    20    003144    Ky
Coca bbbb    2009    21    003144    Ky
Coca bbbb    2010    16    003144    Ky
Coca bbbb    2011    18   003144    Ky
;
run;
data p1 p2;
 set have(where=(year in (2001 2005 2006)));
 if Interbrand_Value gt 20 then output p1;
  else if Interbrand_Value lt 10 then output p2;
run;
proc sql;
 create table year as select distinct year from have;
 create table portfolios1 as
  select a.year,Brand ,Interbrand_Value ,GVKEY,Ticker_symbol
   from (select year from year where year in (2001 2002 2003 2004)) as a,
        (select * from p1 where year=2001)
  union all corresponding 
  select * from  p1 where year in (2005 2006)
  union all corresponding 
  select a.year,Brand ,Interbrand_Value ,GVKEY,Ticker_symbol
   from (select year from year where year not in (2001 2002 2003 2004 2005 2006)) as a,
        (select * from p1 where year=2006);

 create table portfolios2 as
  select a.year,Brand ,Interbrand_Value ,GVKEY,Ticker_symbol
   from (select year from year where year in (2001 2002 2003 2004)) as a,
        (select * from p2 where year=2001)
  union all corresponding 
  select * from  p2 where year in (2005 2006)
  union all corresponding 
  select a.year,Brand ,Interbrand_Value ,GVKEY,Ticker_symbol
   from (select year from year where year not in (2001 2002 2003 2004 2005 2006)) as a,
        (select * from p2 where year=2006);
quit;

Ksharp