Solved: Re: Create portfolios with interbrand values - Page 2

Linlin · Posted 05-01-2012 10:35 AM

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;

Ksharp · Posted 05-01-2012 11:44 PM

Linlin.

You are right. I make a mistake here. I delete the first obs of very brand group whatever it is above or below 20 ,10.

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 out=temp;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;

Ksharp

Inge12 · Posted 05-01-2012 10:51 AM

Yes you are right this is the correct answer! I want to unmark the other answer as correct but is does not work....

Inge12 · Posted 05-05-2012 09:37 AM

I finished creating my portfolios. Now I have to link the portfolios with another dataset.

For example portfolio 1 with a datset that includes the stock returns of the different brands. The ticker symbol is the variable that links the two datasets.

There is only one problem, the portfolios are yearly based and the stock returns are monthly.

Could you give me some advice how to link these two datasets?

Inge

Linlin · Posted 05-05-2012 10:37 AM

please post a few records from stock return dataset.

Inge12 · Posted 05-06-2012 11:28 AM

Here I have an example for you:

Date	Ticker Symbol	Price	CFACPR
31-1-2002	KO	43,75	1
28-2-2002	KO	47,38999939	1
28-3-2002	KO	52,25999832	1
30-4-2002	KO	55,50999832	1
31-5-2002	KO	55,56000137	1
28-6-2002	KO	56	1
31-7-2002	KO	49,93999863	1
30-8-2002	KO	51	1
30-9-2002	KO	47,95999908	1
31-10-2002	KO	46,47999954	1
29-11-2002	KO	45,63999939	1
31-12-2002	KO	43,84000015	1
31-1-2003	KO	40,45999908	1
28-2-2003	KO	40,22000122	1
31-3-2003	KO	40,47999954	1
30-4-2003	KO	40,40000153	1
30-5-2003	KO	45,56999969	1
30-6-2003	KO	46,40999985	1
31-7-2003	KO	44,97000122	1
29-8-2003	KO	43,52000046	1
30-9-2003	KO	42,95999908	1
31-10-2003	KO	46,40000153	1
28-11-2003	KO	46,5	1
31-12-2003	KO	50,75	1
30-1-2004	KO	49,24000168	1
27-2-2004	KO	49,95999908	1
31-3-2004	KO	50,29999924	1
30-4-2004	KO	50,56999969	1
28-5-2004	KO	51,34999847	1
30-6-2004	KO	50,47999954	1
30-7-2004	KO	43,86000061	1
31-8-2004	KO	44,70999908	1
30-9-2004	KO	40,04999924	1
29-10-2004	KO	40,65999985	1
30-11-2004	KO	39,31000137	1
31-12-2004	KO	41,63999939	1
31-1-2005	KO	41,49000168	1
28-2-2005	KO	42,79999924	1
31-3-2005	KO	41,66999817	1
29-4-2005	KO	43,43999863	1
31-5-2005	KO	44,63000107	1
30-6-2005	KO	41,75	1
29-7-2005	KO	43,75999832	1
31-8-2005	KO	44	1
30-9-2005	KO	43,18999863	1
31-10-2005	KO	42,77999878	1
30-11-2005	KO	42,68999863	1
30-12-2005	KO	40,31000137	1
31-1-2006	KO	41,38000107	1
28-2-2006	KO	41,97000122	1
31-3-2006	KO	41,86999893	1
28-4-2006	KO	41,95999908	1
31-5-2006	KO	44,02999878	1
30-6-2006	KO	43,02000046	1
31-7-2006	KO	44,5	1
31-8-2006	KO	44,81000137	1
29-9-2006	KO	44,68000031	1
31-10-2006	KO	46,72000122	1
30-11-2006	KO	46,83000183	1
29-12-2006	KO	48,25	1
31-1-2007	KO	47,88000107	1
28-2-2007	KO	46,68000031	1
30-3-2007	KO	48	1
30-4-2007	KO	52,18999863	1
31-5-2007	KO	52,99000168	1
29-6-2007	KO	52,31000137	1
31-7-2007	KO	52,11000061	1
31-8-2007	KO	53,77999878	1
28-9-2007	KO	57,47000122	1
31-10-2007	KO	61,75999832	1
30-11-2007	KO	62,09999847	1
31-12-2007	KO	61,36999893	1
31-1-2008	KO	59	1
29-2-2008	KO	58,45999908	1
31-3-2008	KO	60,86999893	1
30-4-2008	KO	58,86999893	1
30-5-2008	KO	57,25999832	1
30-6-2008	KO	51,97999954	1
31-7-2008	KO	51,5	1
29-8-2008	KO	52,06999969	1
30-9-2008	KO	52,88000107	1
31-10-2008	KO	44,06000137	1
28-11-2008	KO	46,86999893	1
31-12-2008	KO	45,27000046	1
30-1-2009	KO	42,72000122	1
27-2-2009	KO	40,84999847	1
31-3-2009	KO	43,95000076	1
30-4-2009	KO	43,04999924	1
29-5-2009	KO	49,15999985	1
30-6-2009	KO	47,99000168	1
31-7-2009	KO	49,84000015	1
31-8-2009	KO	48,77000046	1
30-9-2009	KO	53,70000076	1
30-10-2009	KO	53,31000137	1
30-11-2009	KO	57,20000076	1
31-12-2009	KO	57	1
29-1-2010	KO	54,25	1
26-2-2010	KO	52,72000122	1
31-3-2010	KO	55	1
30-4-2010	KO	53,45000076	1
28-5-2010	KO	51,40000153	1
30-6-2010	KO	50,11999893	1
30-7-2010	KO	55,11000061	1
31-8-2010	KO	55,88249969	1
30-9-2010	KO	58,52000046	1
29-10-2010	KO	61,31999969	1
30-11-2010	KO	63,16999817	1
31-12-2010	KO	65,76999664	1
31-1-2011	KO	62,84999847	1
28-2-2011	KO	63,91999817	1
31-3-2011	KO	66,33999634	1
29-4-2011	KO	67,45999908	1
31-5-2011	KO	66,80999756	1
30-6-2011	KO	67,29000092	1
29-7-2011	KO	68,01000214	1
31-8-2011	KO	70,44999695	1
30-9-2011	KO	67,55999756	1
31-10-2011	KO	68,31999969	1
30-11-2011	KO	67,23000336	1
30-12-2011	KO	69,97000122	1

The link between de two datasets is the ticker symbol. The point that makes it difficult is that the brands are not in the same portfolio each year and that this dataset is monthly based and the portfolios only change each year.

I hope that you can give me some advice! 🙂

Linlin · Posted 05-06-2012 07:49 PM

Hi Inge12,

Is this what you want?

data monthly;

informat date ddmmyy10. ;

format date ddmmyy10.;

input Date Ticker_Symbol $ Price :commax. CFACPR;

cards;

31-1-2002 KO 43,75 1

28-2-2002 KO 47,38999939 1

28-3-2002 KO 52,25999832 1

30-4-2002 KO 55,50999832 1

31-5-2002 KO 55,56000137 1

28-6-2002 KO 56 1

31-7-2002 KO 49,93999863 1

30-8-2002 KO 51 1

30-9-2002 KO 47,95999908 1

31-10-2002 KO 46,47999954 1

29-11-2002 KO 45,63999939 1

31-12-2002 KO 43,84000015 1

31-1-2003 KO 40,45999908 1

28-2-2003 KO 40,22000122 1

31-3-2003 KO 40,47999954 1

30-4-2003 KO 40,40000153 1

30-5-2003 KO 45,56999969 1

30-6-2003 KO 46,40999985 1

31-7-2003 KO 44,97000122 1

29-8-2003 KO 43,52000046 1

30-9-2003 KO 42,95999908 1

31-10-2003 KO 46,40000153 1

28-11-2003 KO 46,5 1

31-12-2003 KO 50,75 1

30-1-2004 KO 49,24000168 1

27-2-2004 KO 49,95999908 1

31-3-2004 KO 50,29999924 1

30-4-2004 KO 50,56999969 1

28-5-2004 KO 51,34999847 1

30-6-2004 KO 50,47999954 1

30-7-2004 KO 43,86000061 1

31-8-2004 KO 44,70999908 1

30-9-2004 KO 40,04999924 1

29-10-2004 KO 40,65999985 1

30-11-2004 KO 39,31000137 1

31-12-2004 KO 41,63999939 1

31-1-2005 KO 41,49000168 1

28-2-2005 KO 42,79999924 1

31-3-2005 KO 41,66999817 1

29-4-2005 KO 43,43999863 1

31-5-2005 KO 44,63000107 1

30-6-2005 KO 41,75 1

29-7-2005 KO 43,75999832 1

31-8-2005 KO 44 1

30-9-2005 KO 43,18999863 1

31-10-2005 KO 42,77999878 1

30-11-2005 KO 42,68999863 1

30-12-2005 KO 40,31000137 1

31-1-2006 KO 41,38000107 1

28-2-2006 KO 41,97000122 1

31-3-2006 KO 41,86999893 1

28-4-2006 KO 41,95999908 1

31-5-2006 KO 44,02999878 1

30-6-2006 KO 43,02000046 1

31-7-2006 KO 44,5 1

31-8-2006 KO 44,81000137 1

29-9-2006 KO 44,68000031 1

31-10-2006 KO 46,72000122 1

30-11-2006 KO 46,83000183 1

29-12-2006 KO 48,25 1

31-1-2007 KO 47,88000107 1

28-2-2007 KO 46,68000031 1

30-3-2007 KO 48 1

30-4-2007 KO 52,18999863 1

31-5-2007 KO 52,99000168 1

29-6-2007 KO 52,31000137 1

31-7-2007 KO 52,11000061 1

31-8-2007 KO 53,77999878 1

28-9-2007 KO 57,47000122 1

31-10-2007 KO 61,75999832 1

30-11-2007 KO 62,09999847 1

31-12-2007 KO 61,36999893 1

31-1-2008 KO 59 1

29-2-2008 KO 58,45999908 1

31-3-2008 KO 60,86999893 1

30-4-2008 KO 58,86999893 1

30-5-2008 KO 57,25999832 1

30-6-2008 KO 51,97999954 1

31-7-2008 KO 51,5 1

29-8-2008 KO 52,06999969 1

30-9-2008 KO 52,88000107 1

31-10-2008 KO 44,06000137 1

28-11-2008 KO 46,86999893 1

31-12-2008 KO 45,27000046 1

30-1-2009 KO 42,72000122 1

27-2-2009 KO 40,84999847 1

31-3-2009 KO 43,95000076 1

30-4-2009 KO 43,04999924 1

29-5-2009 KO 49,15999985 1

30-6-2009 KO 47,99000168 1

31-7-2009 KO 49,84000015 1

31-8-2009 KO 48,77000046 1

30-9-2009 KO 53,70000076 1

30-10-2009 KO 53,31000137 1

30-11-2009 KO 57,20000076 1

31-12-2009 KO 57 1

29-1-2010 KO 54,25 1

26-2-2010 KO 52,72000122 1

31-3-2010 KO 55 1

30-4-2010 KO 53,45000076 1

28-5-2010 KO 51,40000153 1

30-6-2010 KO 50,11999893 1

30-7-2010 KO 55,11000061 1

31-8-2010 KO 55,88249969 1

30-9-2010 KO 58,52000046 1

29-10-2010 KO 61,31999969 1

30-11-2010 KO 63,16999817 1

31-12-2010 KO 65,76999664 1

31-1-2011 KO 62,84999847 1

28-2-2011 KO 63,91999817 1

31-3-2011 KO 66,33999634 1

29-4-2011 KO 67,45999908 1

31-5-2011 KO 66,80999756 1

30-6-2011 KO 67,29000092 1

29-7-2011 KO 68,01000214 1

31-8-2011 KO 70,44999695 1

30-9-2011 KO 67,55999756 1

31-10-2011 KO 68,31999969 1

30-11-2011 KO 67,23000336 1

30-12-2011 KO 69,97000122 1

;

data yearly;

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

cards;

Coca Cola 2002 69,637 003144 KO

Coca Cola 2003 70,453 003144 KO

Coca Cola 2005 67,525 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

;

proc sql;

create table linked as select Date,a.Ticker_Symbol, Price , CFACPR from monthly as a,yearly as b

where year(date)=year and a.Ticker_Symbol=b.Ticker_Symbol;

quit;

proc print;

run;

Obs date Symbol Price CFACPR

1 31/01/2002 KO 43.7500 1

2 28/02/2002 KO 47.3900 1

3 28/03/2002 KO 52.2600 1

4 30/04/2002 KO 55.5100 1

5 31/05/2002 KO 55.5600 1

6 28/06/2002 KO 56.0000 1

7 31/07/2002 KO 49.9400 1

8 30/08/2002 KO 51.0000 1

9 30/09/2002 KO 47.9600 1

10 31/10/2002 KO 46.4800 1

11 29/11/2002 KO 45.6400 1

12 31/12/2002 KO 43.8400 1

13 31/01/2003 KO 40.4600 1

14 28/02/2003 KO 40.2200 1

15 31/03/2003 KO 40.4800 1

16 30/04/2003 KO 40.4000 1

17 30/05/2003 KO 45.5700 1

18 30/06/2003 KO 46.4100 1

19 31/07/2003 KO 44.9700 1

20 29/08/2003 KO 43.5200 1

21 30/09/2003 KO 42.9600 1

22 31/10/2003 KO 46.4000 1

23 28/11/2003 KO 46.5000 1

24 31/12/2003 KO 50.7500 1

25 31/01/2005 KO 41.4900 1

26 28/02/2005 KO 42.8000 1

27 31/03/2005 KO 41.6700 1

28 29/04/2005 KO 43.4400 1

29 31/05/2005 KO 44.6300 1

30 30/06/2005 KO 41.7500 1

31 29/07/2005 KO 43.7600 1

32 31/08/2005 KO 44.0000 1

33 30/09/2005 KO 43.1900 1

34 31/10/2005 KO 42.7800 1

35 30/11/2005 KO 42.6900 1

36 30/12/2005 KO 40.3100 1

37 31/01/2008 KO 59.0000 1

38 29/02/2008 KO 58.4600 1

39 31/03/2008 KO 60.8700 1

40 30/04/2008 KO 58.8700 1

41 30/05/2008 KO 57.2600 1

42 30/06/2008 KO 51.9800 1

43 31/07/2008 KO 51.5000 1

44 29/08/2008 KO 52.0700 1

45 30/09/2008 KO 52.8800 1

46 31/10/2008 KO 44.0600 1

47 28/11/2008 KO 46.8700 1

48 31/12/2008 KO 45.2700 1

49 30/01/2009 KO 42.7200 1

50 27/02/2009 KO 40.8500 1

51 31/03/2009 KO 43.9500 1

52 30/04/2009 KO 43.0500 1

53 29/05/2009 KO 49.1600 1

54 30/06/2009 KO 47.9900 1

55 31/07/2009 KO 49.8400 1

56 31/08/2009 KO 48.7700 1

57 30/09/2009 KO 53.7000 1

58 30/10/2009 KO 53.3100 1

59 30/11/2009 KO 57.2000 1

60 31/12/2009 KO 57.0000 1

61 29/01/2010 KO 54.2500 1

62 26/02/2010 KO 52.7200 1

63 31/03/2010 KO 55.0000 1

64 30/04/2010 KO 53.4500 1

65 28/05/2010 KO 51.4000 1

66 30/06/2010 KO 50.1200 1

67 30/07/2010 KO 55.1100 1

68 31/08/2010 KO 55.8825 1

69 30/09/2010 KO 58.5200 1

70 29/10/2010 KO 61.3200 1

71 30/11/2010 KO 63.1700 1

72 31/12/2010 KO 65.7700 1

73 31/01/2011 KO 62.8500 1

74 28/02/2011 KO 63.9200 1

75 31/03/2011 KO 66.3400 1

76 29/04/2011 KO 67.4600 1

77 31/05/2011 KO 66.8100 1

78 30/06/2011 KO 67.2900 1

79 29/07/2011 KO 68.0100 1

80 31/08/2011 KO 70.4500 1

81 30/09/2011 KO 67.5600 1

82 31/10/2011 KO 68.3200 1

83 30/11/2011 KO 67.2300 1

84 30/12/2011 KO 69.9700 1

Ksharp · Posted 05-07-2012 01:13 AM

Since you don't give what output you need. Check it out whether it is what you want.

data monthly;
informat date ddmmyy10. ;
format date ddmmyy10.;
input Date Ticker_Symbol $ Price :commax. CFACPR;
cards;
31-1-2002 KO 43,75 1
28-2-2002 KO 47,38999939 1
28-3-2002 KO 52,25999832 1
31-3-2003 KO 40,47999954 1
30-4-2003 KO 40,40000153 1
30-5-2003 KO 45,56999969 1
30-6-2003 KO 46,40999985 1
31-7-2003 KO 44,97000122 1
;
run;
data yearly;
input Brand &$10. Year Interbrand_Value :commax. GVKEY Ticker_symbol$;
cards;
Coca Cola    2002    69,637    003144    KO
Coca Cola    2003    70,453    003144    KO
Coca Cola    2005    67,525    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
;

proc sql;
create table want as
select a.*, return
 from yearly as a left join (  select year(date) as year,Ticker_Symbol,sum(price) as return
                                from monthly
                                 group by year,Ticker_Symbol) as b
  on a.year=b.year and a.Ticker_Symbol=b.Ticker_Symbol;
quit;

Ksharp

Inge12 · Posted 05-07-2012 04:50 AM

Sorry that its not clear what I want.

I have 4 portfolios, the new data should be combined with these portfolios. Coca Cola is for example in portfolio 1 and 2. Is it possible to combine one dataset to 4 different portfolios?

Ksharp · Posted 05-07-2012 04:54 AM

You can run the code above four times for each of 4 portfolios .

OR

You can combine these 4 portfolios into one dataset ,then use this code .

Ksharp

Inge12 · Posted 05-07-2012 06:00 AM

Thank you the returns are now linked to portfolios. Is it also possible to keep the returns monthly? And I also want to keep the CFACPR code in the new database. Thank you!

Ksharp · Posted 05-08-2012 04:05 AM

Yes, You can .

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

Inge12 · Posted 05-08-2012 05:41 AM

Ok, I have two datasets:

This is a sample of the first (portfolio 1): Brand, Year, Interbrand value (in millions) GVKEY, Ticker Symbol and Sector.

Coca Cola	2002	69,637	3144	KO	Beverage
Coca Cola	2003	70,453	3144	KO	Beverage
Coca Cola	2005	67,525	3144	KO	Beverage
Coca Cola	2008	66,667	3144	KO	Beverage
Coca Cola	2009	68,734	3144	KO	Beverage
Coca Cola	2010	70,452	3144	KO	Beverage
Coca Cola	2011	71,861	3144	KO	Beverage
Dell	2003	10,367	14489	DELL	Electron
Dell	2004	11,5	14489	DELL	Electron
Dell	2005	13,231	14489	DELL	Electron
Dell	2008	11,695	14489	DELL	Electron

The second dataset looks like this:

Date, Ticker Symbol, Stock price, CFACPR

31-1-2002	DELL	27,48999977	1
28-2-2002	DELL	24,69000053	1
28-3-2002	DELL	26,11000061	1
30-4-2002	DELL	26,34000015	1
31-5-2002	DELL	26,85000038	1
28-6-2002	DELL	26,13999939	1
31-7-2002	DELL	24,93000031	1
30-8-2002	DELL	26,62000084	1
30-9-2002	DELL	23,51000023	1
31-10-2002	DELL	28,61000061	1
29-11-2002	DELL	28,60000038	1
31-12-2002	DELL	26,73999977	1
31-1-2003	DELL	23,86000061	1
28-2-2003	DELL	26,95999908	1
31-3-2003	DELL	27,30999947	1
30-4-2003	DELL	28,97999954	1
30-5-2003	DELL	31,37000084	1
30-6-2003	DELL	31,84000015	1
31-7-2003	DELL	33,68000031	1
29-8-2003	DELL	32,61999893	1
30-9-2003	DELL	33,41999817	1
31-10-2003	DELL	36	1
28-11-2003	DELL	34,56999969	1
31-12-2003	DELL	33,97999954	1
30-1-2004	DELL	33,43999863	1
27-2-2004	DELL	32,65000153	1
31-3-2004	DELL	33,61999893	1
30-4-2004	DELL	34,77999878	1
28-5-2004	DELL	35,24000168	1
30-6-2004	DELL	35,81999969	1
30-7-2004	DELL	35,47000122	1
31-8-2004	DELL	34,84000015	1
30-9-2004	DELL	35,59999847	1
29-10-2004	DELL	35,06000137	1
30-11-2004	DELL	40,52000046	1
31-12-2004	DELL	42,13999939	1
31-1-2005	DELL	41,75999832	1
28-2-2005	DELL	40,09000015	1
31-3-2005	DELL	38,41999817	1
29-4-2005	DELL	34,83000183	1
31-5-2005	DELL	39,93000031	1
30-6-2005	DELL	39,45999908	1
29-7-2005	DELL	40,47000122	1
31-8-2005	DELL	35,59999847	1
30-9-2005	DELL	34,20000076	1
31-10-2005	DELL	31,87999916	1
30-11-2005	DELL	30,15099907	1
30-12-2005	DELL	29,95000076	1
31-1-2006	DELL	29,30999947	1
28-2-2006	DELL	29	1
31-3-2006	DELL	29,76000023	1
28-4-2006	DELL	26,20000076	1
31-5-2006	DELL	25,37999916	1
30-6-2006	DELL	24,45999908	1
31-7-2006	DELL	21,68000031	1
31-8-2006	DELL	22,54999924	1
29-9-2006	DELL	22,84000015	1
31-10-2006	DELL	24,32999992	1
30-11-2006	DELL	27,23999977	1
29-12-2006	DELL	25,09000015	1
31-1-2007	DELL	24,21999931	1
28-2-2007	DELL	22,85000038	1
30-3-2007	DELL	23,20999908	1
30-4-2007	DELL	25,20999908	1
31-5-2007	DELL	26,9109993	1
29-6-2007	DELL	28,54999924	1
31-7-2007	DELL	27,96999931	1
31-8-2007	DELL	28,25	1
28-9-2007	DELL	27,60000038	1
31-10-2007	DELL	30,60000038	1
30-11-2007	DELL	24,54000092	1
31-12-2007	DELL	24,51000023	1
31-1-2008	DELL	20,04000092	1
29-2-2008	DELL	19,90010071	1
31-3-2008	DELL	19,92000008	1
30-4-2008	DELL	18,62999916	1
30-5-2008	DELL	23,05999947	1
30-6-2008	DELL	21,87999916	1
31-7-2008	DELL	24,56999969	1
29-8-2008	DELL	21,72999954	1
30-9-2008	DELL	16,47999954	1
31-10-2008	DELL	12,19999981	1
28-11-2008	DELL	11,17000008	1
31-12-2008	DELL	10,23999977	1
30-1-2009	DELL	9,5	1
27-2-2009	DELL	8,529999733	1
31-3-2009	DELL	9,479999542	1
30-4-2009	DELL	11,61999989	1
29-5-2009	DELL	11,56999969	1
30-6-2009	DELL	13,72999954	1
31-7-2009	DELL	13,38000011	1
31-8-2009	DELL	15,82999992	1
30-9-2009	DELL	15,26000023	1
30-10-2009	DELL	14,44999981	1
30-11-2009	DELL	14,11999989	1
31-12-2009	DELL	14,35999966	1
29-1-2010	DELL	12,89999962	1
26-2-2010	DELL	13,23999977	1
31-3-2010	DELL	15,02000046	1
30-4-2010	DELL	16,20000076	1
28-5-2010	DELL	13,32999992	1
30-6-2010	DELL	12,06000042	1
30-7-2010	DELL	13,23999977	1
31-8-2010	DELL	11,77000046	1
30-9-2010	DELL	12,97000027	1
29-10-2010	DELL	14,39500046	1
30-11-2010	DELL	13,22000027	1
31-12-2010	DELL	13,55000019	1
31-1-2011	DELL	13,15999985	1
28-2-2011	DELL	15,82999992	1
31-3-2011	DELL	14,51000023	1
29-4-2011	DELL	15,47000027	1
31-5-2011	DELL	16,07999992	1
30-6-2011	DELL	16,67000008	1
29-7-2011	DELL	16,23999977	1
31-8-2011	DELL	14,86499977	1
30-9-2011	DELL	14,14000034	1
31-10-2011	DELL	15,81000042	1
30-11-2011	DELL	15,76000023	1
30-12-2011	DELL	14,63000011	1
31-1-2002	KO	43,75	1
28-2-2002	KO	47,38999939	1
28-3-2002	KO	52,25999832	1
30-4-2002	KO	55,50999832	1
31-5-2002	KO	55,56000137	1
28-6-2002	KO	56	1
31-7-2002	KO	49,93999863	1
30-8-2002	KO	51	1
30-9-2002	KO	47,95999908	1
31-10-2002	KO	46,47999954	1
29-11-2002	KO	45,63999939	1
31-12-2002	KO	43,84000015	1
31-1-2003	KO	40,45999908	1
28-2-2003	KO	40,22000122	1
31-3-2003	KO	40,47999954	1
30-4-2003	KO	40,40000153	1
30-5-2003	KO	45,56999969	1
30-6-2003	KO	46,40999985	1
31-7-2003	KO	44,97000122	1
29-8-2003	KO	43,52000046	1
30-9-2003	KO	42,95999908	1
31-10-2003	KO	46,40000153	1
28-11-2003	KO	46,5	1
31-12-2003	KO	50,75	1
30-1-2004	KO	49,24000168	1
27-2-2004	KO	49,95999908	1
31-3-2004	KO	50,29999924	1
30-4-2004	KO	50,56999969	1
28-5-2004	KO	51,34999847	1
30-6-2004	KO	50,47999954	1
30-7-2004	KO	43,86000061	1
31-8-2004	KO	44,70999908	1
30-9-2004	KO	40,04999924	1
29-10-2004	KO	40,65999985	1
30-11-2004	KO	39,31000137	1
31-12-2004	KO	41,63999939	1
31-1-2005	KO	41,49000168	1
28-2-2005	KO	42,79999924	1
31-3-2005	KO	41,66999817	1
29-4-2005	KO	43,43999863	1
31-5-2005	KO	44,63000107	1
30-6-2005	KO	41,75	1
29-7-2005	KO	43,75999832	1
31-8-2005	KO	44	1
30-9-2005	KO	43,18999863	1
31-10-2005	KO	42,77999878	1
30-11-2005	KO	42,68999863	1
30-12-2005	KO	40,31000137	1
31-1-2006	KO	41,38000107	1
28-2-2006	KO	41,97000122	1
31-3-2006	KO	41,86999893	1
28-4-2006	KO	41,95999908	1
31-5-2006	KO	44,02999878	1
30-6-2006	KO	43,02000046	1
31-7-2006	KO	44,5	1
31-8-2006	KO	44,81000137	1
29-9-2006	KO	44,68000031	1
31-10-2006	KO	46,72000122	1
30-11-2006	KO	46,83000183	1
29-12-2006	KO	48,25	1
31-1-2007	KO	47,88000107	1
28-2-2007	KO	46,68000031	1
30-3-2007	KO	48	1
30-4-2007	KO	52,18999863	1
31-5-2007	KO	52,99000168	1
29-6-2007	KO	52,31000137	1
31-7-2007	KO	52,11000061	1
31-8-2007	KO	53,77999878	1
28-9-2007	KO	57,47000122	1
31-10-2007	KO	61,75999832	1
30-11-2007	KO	62,09999847	1
31-12-2007	KO	61,36999893	1
31-1-2008	KO	59	1
29-2-2008	KO	58,45999908	1
31-3-2008	KO	60,86999893	1
30-4-2008	KO	58,86999893	1
30-5-2008	KO	57,25999832	1
30-6-2008	KO	51,97999954	1
31-7-2008	KO	51,5	1
29-8-2008	KO	52,06999969	1
30-9-2008	KO	52,88000107	1
31-10-2008	KO	44,06000137	1
28-11-2008	KO	46,86999893	1
31-12-2008	KO	45,27000046	1
30-1-2009	KO	42,72000122	1
27-2-2009	KO	40,84999847	1
31-3-2009	KO	43,95000076	1
30-4-2009	KO	43,04999924	1
29-5-2009	KO	49,15999985	1
30-6-2009	KO	47,99000168	1
31-7-2009	KO	49,84000015	1
31-8-2009	KO	48,77000046	1
30-9-2009	KO	53,70000076	1
30-10-2009	KO	53,31000137	1
30-11-2009	KO	57,20000076	1
31-12-2009	KO	57	1
29-1-2010	KO	54,25	1
26-2-2010	KO	52,72000122	1
31-3-2010	KO	55	1
30-4-2010	KO	53,45000076	1
28-5-2010	KO	51,40000153	1
30-6-2010	KO	50,11999893	1
30-7-2010	KO	55,11000061	1
31-8-2010	KO	55,88249969	1
30-9-2010	KO	58,52000046	1
29-10-2010	KO	61,31999969	1
30-11-2010	KO	63,16999817	1
31-12-2010	KO	65,76999664	1
31-1-2011	KO	62,84999847	1
28-2-2011	KO	63,91999817	1
31-3-2011	KO	66,33999634	1
29-4-2011	KO	67,45999908	1
31-5-2011	KO	66,80999756	1
30-6-2011	KO	67,29000092	1
29-7-2011	KO	68,01000214	1
31-8-2011	KO	70,44999695	1
30-9-2011	KO	67,55999756	1
31-10-2011	KO	68,31999969	1
30-11-2011	KO	67,23000336	1
30-12-2011	KO	69,97000122	1

The second dataset should be linked to the portfolios (have 4 of them).

The dataset that I want should have the following elements:

All the elements of the first dataset (brand, year and so on) And from the second dataset: Date, stock price for each month (not a total) and CFACPR number.

Linlin · Posted 05-08-2012 08:48 AM

Hi Inge12,

Hope this is want you want.

data monthly;

informat date ddmmyy10. ;

format date ddmmyy10.;

input Date Ticker_Symbol $ Price :commax. CFACPR;

cards;

31-1-2002 KO 43,75 1

28-2-2002 KO 47,38999939 1

28-3-2002 KO 52,25999832 1

30-4-2002 KO 55,50999832 1

31-5-2002 KO 55,56000137 1

28-6-2002 KO 56 1

31-7-2002 KO 49,93999863 1

30-8-2002 KO 51 1

30-9-2002 KO 47,95999908 1

31-10-2002 KO 46,47999954 1

29-11-2002 KO 45,63999939 1

31-12-2002 KO 43,84000015 1

31-1-2003 KO 40,45999908 1

28-2-2003 KO 40,22000122 1

31-3-2003 KO 40,47999954 1

30-4-2003 KO 40,40000153 1

30-5-2003 KO 45,56999969 1

30-6-2003 KO 46,40999985 1

31-7-2003 KO 44,97000122 1

29-8-2003 KO 43,52000046 1

30-9-2003 KO 42,95999908 1

31-10-2003 KO 46,40000153 1

28-11-2003 KO 46,5 1

31-12-2003 KO 50,75 1

30-1-2004 KO 49,24000168 1

27-2-2004 KO 49,95999908 1

31-3-2004 KO 50,29999924 1

30-4-2004 KO 50,56999969 1

28-5-2004 KO 51,34999847 1

30-6-2004 KO 50,47999954 1

30-7-2004 KO 43,86000061 1

31-8-2004 KO 44,70999908 1

30-9-2004 KO 40,04999924 1

29-10-2004 KO 40,65999985 1

30-11-2004 KO 39,31000137 1

31-12-2004 KO 41,63999939 1

31-1-2005 KO 41,49000168 1

28-2-2005 KO 42,79999924 1

31-3-2005 KO 41,66999817 1

29-4-2005 KO 43,43999863 1

31-5-2005 KO 44,63000107 1

30-6-2005 KO 41,75 1

29-7-2005 KO 43,75999832 1

31-8-2005 KO 44 1

30-9-2005 KO 43,18999863 1

31-10-2005 KO 42,77999878 1

30-11-2005 KO 42,68999863 1

30-12-2005 KO 40,31000137 1

31-1-2006 KO 41,38000107 1

28-2-2006 KO 41,97000122 1

31-3-2006 KO 41,86999893 1

28-4-2006 KO 41,95999908 1

31-5-2006 KO 44,02999878 1

30-6-2006 KO 43,02000046 1

31-7-2006 KO 44,5 1

31-8-2006 KO 44,81000137 1

29-9-2006 KO 44,68000031 1

31-10-2006 KO 46,72000122 1

30-11-2006 KO 46,83000183 1

29-12-2006 KO 48,25 1

31-1-2007 KO 47,88000107 1

28-2-2007 KO 46,68000031 1

30-3-2007 KO 48 1

30-4-2007 KO 52,18999863 1

31-5-2007 KO 52,99000168 1

29-6-2007 KO 52,31000137 1

31-7-2007 KO 52,11000061 1

31-8-2007 KO 53,77999878 1

28-9-2007 KO 57,47000122 1

31-10-2007 KO 61,75999832 1

30-11-2007 KO 62,09999847 1

31-12-2007 KO 61,36999893 1

31-1-2008 KO 59 1

29-2-2008 KO 58,45999908 1

31-3-2008 KO 60,86999893 1

30-4-2008 KO 58,86999893 1

30-5-2008 KO 57,25999832 1

30-6-2008 KO 51,97999954 1

31-7-2008 KO 51,5 1

29-8-2008 KO 52,06999969 1

30-9-2008 KO 52,88000107 1

31-10-2008 KO 44,06000137 1

28-11-2008 KO 46,86999893 1

31-12-2008 KO 45,27000046 1

30-1-2009 KO 42,72000122 1

27-2-2009 KO 40,84999847 1

31-3-2009 KO 43,95000076 1

30-4-2009 KO 43,04999924 1

29-5-2009 KO 49,15999985 1

30-6-2009 KO 47,99000168 1

31-7-2009 KO 49,84000015 1

31-8-2009 KO 48,77000046 1

30-9-2009 KO 53,70000076 1

30-10-2009 KO 53,31000137 1

30-11-2009 KO 57,20000076 1

31-12-2009 KO 57 1

29-1-2010 KO 54,25 1

26-2-2010 KO 52,72000122 1

31-3-2010 KO 55 1

30-4-2010 KO 53,45000076 1

28-5-2010 KO 51,40000153 1

30-6-2010 KO 50,11999893 1

30-7-2010 KO 55,11000061 1

31-8-2010 KO 55,88249969 1

30-9-2010 KO 58,52000046 1

29-10-2010 KO 61,31999969 1

30-11-2010 KO 63,16999817 1

31-12-2010 KO 65,76999664 1

31-1-2011 KO 62,84999847 1

28-2-2011 KO 63,91999817 1

31-3-2011 KO 66,33999634 1

29-4-2011 KO 67,45999908 1

31-5-2011 KO 66,80999756 1

30-6-2011 KO 67,29000092 1

29-7-2011 KO 68,01000214 1

31-8-2011 KO 70,44999695 1

30-9-2011 KO 67,55999756 1

31-10-2011 KO 68,31999969 1

30-11-2011 KO 67,23000336 1

30-12-2011 KO 69,97000122 1

;

data yearly;

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

cards;

Coca Cola 2002 69,637 003144 KO

Coca Cola 2003 70,453 003144 KO

Coca Cola 2005 67,525 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

;

proc sql;

create table linked as select a.*,Date, Price, CFACPR from yearly as a,monthly as b

where year(date)=year and a.Ticker_Symbol=b.Ticker_Symbol;

quit;

proc print data=linked(obs=10);

run;

obs Brand Year Value GVKEY symbol date Price CFACPR

1    Coca Cola    2002       69.637       3144      KO       31/01/2002    43.7500       1
2    Coca Cola    2002       69.637       3144      KO       28/02/2002    47.3900       1
3    Coca Cola    2002       69.637       3144      KO       28/03/2002    52.2600       1
4    Coca Cola    2002       69.637       3144      KO       30/04/2002    55.5100       1
5    Coca Cola    2002       69.637       3144      KO       31/05/2002    55.5600       1
6    Coca Cola    2002       69.637       3144      KO       28/06/2002    56.0000       1
7    Coca Cola    2002       69.637       3144      KO       31/07/2002    49.9400       1
8    Coca Cola    2002       69.637       3144      KO       30/08/2002    51.0000       1
9    Coca Cola    2002       69.637       3144      KO       30/09/2002    47.9600       1
10    Coca Cola    2002       69.637       3144      KO       31/10/2002    46.4800       1

Inge12 · Posted 05-08-2012 10:14 AM

Hi Linlin,

Yes this is what I want in the dataset.

Thank you!!

Classroom Training Available!