Help using Base SAS procedures

Create portfolios with interbrand values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Create portfolios with interbrand values

I have a dataset with brands that are ranked in the Interbrand list (top 100 most valuable brands). This list is presented each year, and I have the data from 2001 until 2011.

Coca Cola200168,945

003144

KO
Coca Cola200269,637003144KO
Coca Cola200370,453003144KO
Coca Cola200467,394003144KO
Coca Cola200567,525003144KO
Coca Cola200667,000003144KO
Coca Cola200765,324003144KO
Coca Cola200866,667003144KO
Coca Cola200968,734003144KO
Coca Cola201070,452003144KO
Coca Cola201171,861003144KO

This is an example of the data for Coca Cola. (Brand, Year, Interbrand Value (in bold), GVKEY and Ticker symbol)

I would like to create 4 portfolios with the following characteristics. 1: Interbrand value above 20 and increasing in value, 2: Interbrand value of 20 and decreasing in value, 3: Interbrand value below 10 and increasing in value, 4: Interbrand value below 10 and decreasing in value.

It should be possible that the portfolios change each year.

I hope somebody can help me to create this in SAS!

Thanks in advance!


Accepted Solutions
Solution
‎05-01-2012 10:35 AM
Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

Sorry again:smileysilly:.  the previous one doesn't work . try this one:

data have;
brand='GE';
input Year Interbrand_Value;
cards;
2001 42
2002 41
2003 42
2004 44
2005 46
2006 18
2007 10
2008 8
2009 7
2010 9
;

proc sort data=have;
by brand year;

data want;;
  set have;
  by brand;
  if first.brand then flag=1;
  check=lag(Interbrand_Value);
  run;

data p1 p2 p3 p4;
  set want;
  if flag=1 then delete;
  if Interbrand_Value>20 then do;
          if  Interbrand_Value>check then output p1;
          else output p2;
                       end;
else if Interbrand_Value<=10 then do;
           if Interbrand_Value>check then output p3;
          else  output p4;
                        end;
run;
proc print data=p2;run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

Re: Create portfolios with interbrand values

Hi,

please check the code below for creating 4 datasets. also i created a macro for sorting the value in ascending and descending order. %sort is the call. Hope it helps.

data above20 below20 above10 below10;

input Brand &$10. Year Interbrand_Value :comma. GVKEY Ticker_symbol$;

if Interbrand_Value>20 then output above20;

if Interbrand_Value<=20 then output below20;

if Interbrand_Value>10 then output above10;

if Interbrand_Value<=10 then output below10;

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

;

run;

%macro sort(dsn=,descending=,var=,out=);

%if %lowcase(&descending)=descending %then %do;

proc sort data=&dsn out=&out;

by &descending &var;

run;

%end;

%else %do;

proc sort data=&dsn out=&out;

by &var;

run;

%end;

%mend;

%sort(dsn=,descending=,var=,out=)

Thanks,
Jag
Super User
Posts: 9,662

Re: Create portfolios with interbrand values

I want to know how many members in each four portfolios you need ?

Ksharp

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

It is useful to see how you created the 4 dataset. In my case the portfolios however have two characteristics.

There are two portfolios where the brands have interbrand values above 20, in one situation the interbrand value increases (portfolio 1) and the other situation the interbrand value decreases (portfolio 2). The other two portfolios should have values below 10, portfolio 3 is increasing in value, and portfolio 4 decreasing. The first year that the portfolios can be created is in 2002, and each year the portfolio can be different. As an example: in 2002 Coca Cola should be in portfolio 1, and in 2004 in portfolio 2.

I think that there are about 150 brands in my list and it is desirable to have at least 10 brands in each portfolio. And it is not needed to have each brand in a portfolio.

I hope that somebody can give me some advice how to create these portfolios.

Thanks!

Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

Is this what you want?

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

;

data p1 p2 p3 p4;

  set have;

  by Ticker_symbol;

  if not first.Ticker_symbol;

  if Interbrand_Value>20 then do;

      if Interbrand_Value>lag(Interbrand_Value) then output p1;

   else output p2; end;

   else do;

       if Interbrand_Value>lag(Interbrand_Value) then output p3;

      else output p4; end;

run;

Linlin

Super User
Posts: 9,662

Re: Create portfolios with interbrand values

Yes. It is clearer a little bit.

But I still can't understand what you mean totally. The following is based on my understanding.

data have;
input Brand &$10. Year Interbrand_Value :commax. 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    2    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    1    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
Kmart    2001    6,945    003144    KO
Kmart    2002    6,637    003144    KO
Kmart    2003    70,453    003144    KO
Kmart    2004    7,394    003144    KO
Kmart    2005    67,525    003144    KO
Kmart    2006    67,000    003144    KO
Kmart    2007    65,324    003144    KO
Kmart    2008    6,667    003144    KO
Kmart    2009    68,734    003144    KO
Kmart    2010    70,452    003144    KO
Kmart    2011    1,861    003144    KO
Kappa    2001    18    003144    Ky
Kappa    2002    2    003144    Ky
Kappa   2003    15    003144    Ky
Kappa   2004    2   003144    Ky
Kappa    2005    16    003144    Ky
Kappa    2006    17    003144    Ky
Kappa    2007    14    003144    Ky
Kappa    2008    2    003144    Ky
Kappa    2009    21    003144    Ky
Kappa    2010    1   003144    Ky
Kappa    2011    18   003144    Ky
;
run;
proc sort data=have(where=(Interbrand_Value gt 20)) out=temp1;by Brand Year;run;
data portfolio1; 
 set temp1;
 by brand;
 if Interbrand_Value gt lag(Interbrand_Value) and not first.brand then output;
run; 


proc sort data=have(where=(Interbrand_Value gt 20)) out=temp2;by Brand Year;run;
data portfolio2; 
 set temp2;
 by brand;
 if Interbrand_Value lt lag(Interbrand_Value) and not first.brand then output;
run; 


proc sort data=have(where=(Interbrand_Value lt 10)) out=temp3;by Brand Year;run;
data portfolio3; 
 set temp3;
 by brand;
 if Interbrand_Value gt lag(Interbrand_Value) and not first.brand then output;
run; 


proc sort data=have(where=(Interbrand_Value lt 10)) out=temp4;by Brand Year;run;
data portfolio4; 
 set temp4;
 by brand;
 if Interbrand_Value lt lag(Interbrand_Value) and not first.brand then output;
run; 

Ksharp

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

Thank you for your answers! I will try if I can make the portfolios with your help.

Maybe I can make my question more understandable.

The Interbrand value means how much the brand is worth in Millions. Here is a small overview of 3 brands, I did not include the GVKEY and the Ticker symbol as those numers have no influence on creating the portfolios. I need those values later on in my research.

Coca Cola200168,945
Coca Cola200269,637
Coca Cola200370,453
Coca Cola200467,394
Coca Cola200567,525
Coca Cola200667,000
Coca Cola200765,324
Coca Cola200866,667
Coca Cola200968,734
Coca Cola201070,452
Coca Cola201171,861
Microsoft200165,068
Microsoft200264,091
Microsoft200365,174
Microsoft200461,372
Microsoft200559,941
Microsoft200656,926
Microsoft200758,709
Microsoft200859,007
Microsoft200956,647
Microsoft201060,895
Microsoft201159,087
IBM200152,752
IBM200251,188
IBM200351,767
IBM200453,791
IBM200553,376
IBM200656,201
IBM200757,090
IBM200859,031
IBM200960,211
IBM201064,727
IBM201169,905

The first portfolio will be in 2002, because for creating the portfolios I need to know whether the interbrand value increased.

Portfolio 1: Interbrand value above 20 and increasing in value (interbrand value)

Portfolio 2: Interbrand value above 20 and decreasing in value

Portfolio 3: Interbrand value below 10 and increasing in value

Portfolio 4: Interbrand value below 10 and decreasing in value

I hope my question is clear now?

Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

Hi,

Did you try the codes suggested? I believe my code works. for the sample data, would you please include some with interbrand value below 10 and post one sample output you are looking for?

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

I tried the codes Linlin, and yours did not work. Tomorrow I will figure out why it did not work. The code of Ksharp seems to work. I have to see whether it gives the results I want. I really want to thank you both for your help!

These are 3 brands with Interbrand values below 10.

Panasonic20013,490007114PC
Panasonic20023,141007114PC
Panasonic20033,257007114PC
Panasonic20043,480007114PC
Panasonic20053,714007114PC
Panasonic20063,977007114PC
Panasonic20074,135007114PC
Panasonic20084,281007114PC
Panasonic20094,225007114PC
Panasonic20104,351007114PC
Panasonic20115,047007114PC
Tiffany & Co20013,483013646TIF
Tiffany & Co20023,482013646TIF
Tiffany & Co20033,540013646TIF
Tiffany & Co20043,638013646TIF
Tiffany & Co20053,618013646TIF
Tiffany & Co20063,819013646TIF
Tiffany & Co20074,003013646TIF
Tiffany & Co20084,208013646TIF
Tiffany & Co20094,000013646TIF
Tiffany & Co20104,127013646TIF
Tiffany & Co20114,498013646TIF
BP20013,247002410BP
BP20023,390002410BP
BP20033,582002410BP
BP20043,662002410BP
BP20053,802002410BP
BP20064,010002410BP
BP20073,794002410BP
BP20083,911002410BP
BP20093,716002410BP
BP2010002410BP
BP2011002410BP
Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

Hi,

Using Ksharp's dataset,  the output datasets from Ksharp's and my code are different. for example, Kmart 2003 is in my dataset p1, not in Ksharp's portfolio1. According to your rule (value over 20 and increased value) I believe it should be in p1:smileysilly:. 

data have;

input Brand &$10. Year Interbrand_Value :commax. 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    2    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    1    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

Kmart    2001    6,945    003144    KO

Kmart    2002    6,637    003144    KO

Kmart    2003    70,453    003144    KO

Kmart    2004    7,394    003144    KO

Kmart    2005    67,525    003144    KO

Kmart    2006    67,000    003144    KO

Kmart    2007    65,324    003144    KO

Kmart    2008    6,667    003144    KO

Kmart    2009    68,734    003144    KO

Kmart    2010    70,452    003144    KO

Kmart    2011    1,861    003144    KO

Kappa    2001    18    003144    Ky

Kappa    2002    2    003144    Ky

Kappa   2003    15    003144    Ky

Kappa   2004    2   003144    Ky

Kappa    2005    16    003144    Ky

Kappa    2006    17    003144    Ky

Kappa    2007    14    003144    Ky

Kappa    2008    2    003144    Ky

Kappa    2009    21    003144    Ky

Kappa    2010    1   003144    Ky

Kappa    2011    18   003144    Ky

;

run;

proc sort data=have;

by brand year;

data p1 p2 p3 p4;

  set have;

  by brand;

  if not first.brand;

  if Interbrand_Value>20 then do;

      if Interbrand_Value>lag(Interbrand_Value) then output p1;

   else output p2; end;

   else do;

       if Interbrand_Value>lag(Interbrand_Value) then output p3;

      else output p4; end;

run;

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

You are right Linlin. The codes you gave me put the brands in the right portfolio. There is only a small problem left. The first year should not be in the portfolios, however for 2002 I have to know whether the interbrand value increased from 2001 to 2002. For 2002 there are some brands in the wrong portfolio...

Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

Hi,

I did not see any records from 2001(first year) in my output datasets. Which brands are in the wrong output datasets(p1,p2,p3,p4)?

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

GE200142,396005047GE
GE200241,311005047GE
GE200342,340005047GE
GE200444,111005047GE
GE200546,996005047GE
GE200648,907005047GE
GE200751,569005047GE
GE200853,086005047GE
GE200947,777005047GE
GE201042,808005047GE
GE201142,808005047GE

This brand for example: GE is in 2002 in p1, but it should be in p2. I also do not have any records from 2001 in my output dataset, however this causes that there are some mistakes in 2002. Is it possible to use the value of 2001, but not include the records in the portfolios?

Super Contributor
Posts: 1,636

Re: Create portfolios with interbrand values

sorry. Try this one:   This one does not work.

data have;
brand='GE';
input Year Interbrand_Value;
cards;
2001 42
2002 41
2003 42
2004 44
2005 46
2006 18
2007 10
2008 8
2009 7
2010 9
;

proc sort data=have;
by brand year;

data p1 p2 p3 p4;
  set have;
  by brand;
  if first.brand then flag=1;
   if Interbrand_Value>20 then do;
            if Interbrand_Value>lag(Interbrand_Value) and flag ne 1  then output p1;
   if Interbrand_Value<=lag(Interbrand_Value) and flag ne 1  then output p2;
                        end;
if Interbrand_Value<=10 then do;
           if Interbrand_Value>lag(Interbrand_Value) and flag ne 1  then output p3;
   if Interbrand_Value<=lag(Interbrand_Value) and flag ne 1  then output p4;
                        end;
run;
proc print data=p2;run;

Occasional Contributor
Posts: 17

Re: Create portfolios with interbrand values

Thank you for your help Linlin!! :-)

☑ This topic is SOLVED.

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

Discussion stats
  • 33 replies
  • 645 views
  • 3 likes
  • 4 in conversation