BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Linlin
Lapis Lazuli | Level 10

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
Super User

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
Calcite | Level 5

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

Inge12
Calcite | Level 5

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
Lapis Lazuli | Level 10

please post a few records from stock return dataset.

Inge12
Calcite | Level 5

Here I have an example for you:

Date Ticker SymbolPriceCFACPR
31-1-2002KO43,751
28-2-2002KO47,389999391
28-3-2002KO52,259998321
30-4-2002KO55,509998321
31-5-2002KO55,560001371
28-6-2002KO561
31-7-2002KO49,939998631
30-8-2002KO511
30-9-2002KO47,959999081
31-10-2002KO46,479999541
29-11-2002KO45,639999391
31-12-2002KO43,840000151
31-1-2003KO40,459999081
28-2-2003KO40,220001221
31-3-2003KO40,479999541
30-4-2003KO40,400001531
30-5-2003KO45,569999691
30-6-2003KO46,409999851
31-7-2003KO44,970001221
29-8-2003KO43,520000461
30-9-2003KO42,959999081
31-10-2003KO46,400001531
28-11-2003KO46,51
31-12-2003KO50,751
30-1-2004KO49,240001681
27-2-2004KO49,959999081
31-3-2004KO50,299999241
30-4-2004KO50,569999691
28-5-2004KO51,349998471
30-6-2004KO50,479999541
30-7-2004KO43,860000611
31-8-2004KO44,709999081
30-9-2004KO40,049999241
29-10-2004KO40,659999851
30-11-2004KO39,310001371
31-12-2004KO41,639999391
31-1-2005KO41,490001681
28-2-2005KO42,799999241
31-3-2005KO41,669998171
29-4-2005KO43,439998631
31-5-2005KO44,630001071
30-6-2005KO41,751
29-7-2005KO43,759998321
31-8-2005KO441
30-9-2005KO43,189998631
31-10-2005KO42,779998781
30-11-2005KO42,689998631
30-12-2005KO40,310001371
31-1-2006KO41,380001071
28-2-2006KO41,970001221
31-3-2006KO41,869998931
28-4-2006KO41,959999081
31-5-2006KO44,029998781
30-6-2006KO43,020000461
31-7-2006KO44,51
31-8-2006KO44,810001371
29-9-2006KO44,680000311
31-10-2006KO46,720001221
30-11-2006KO46,830001831
29-12-2006KO48,251
31-1-2007KO47,880001071
28-2-2007KO46,680000311
30-3-2007KO481
30-4-2007KO52,189998631
31-5-2007KO52,990001681
29-6-2007KO52,310001371
31-7-2007KO52,110000611
31-8-2007KO53,779998781
28-9-2007KO57,470001221
31-10-2007KO61,759998321
30-11-2007KO62,099998471
31-12-2007KO61,369998931
31-1-2008KO591
29-2-2008KO58,459999081
31-3-2008KO60,869998931
30-4-2008KO58,869998931
30-5-2008KO57,259998321
30-6-2008KO51,979999541
31-7-2008KO51,51
29-8-2008KO52,069999691
30-9-2008KO52,880001071
31-10-2008KO44,060001371
28-11-2008KO46,869998931
31-12-2008KO45,270000461
30-1-2009KO42,720001221
27-2-2009KO40,849998471
31-3-2009KO43,950000761
30-4-2009KO43,049999241
29-5-2009KO49,159999851
30-6-2009KO47,990001681
31-7-2009KO49,840000151
31-8-2009KO48,770000461
30-9-2009KO53,700000761
30-10-2009KO53,310001371
30-11-2009KO57,200000761
31-12-2009KO571
29-1-2010KO54,251
26-2-2010KO52,720001221
31-3-2010KO551
30-4-2010KO53,450000761
28-5-2010KO51,400001531
30-6-2010KO50,119998931
30-7-2010KO55,110000611
31-8-2010KO55,882499691
30-9-2010KO58,520000461
29-10-2010KO61,319999691
30-11-2010KO63,169998171
31-12-2010KO65,769996641
31-1-2011KO62,849998471
28-2-2011KO63,919998171
31-3-2011KO66,339996341
29-4-2011KO67,459999081
31-5-2011KO66,809997561
30-6-2011KO67,290000921
29-7-2011KO68,010002141
31-8-2011KO70,449996951
30-9-2011KO67,559997561
31-10-2011KO68,319999691
30-11-2011KO67,230003361
30-12-2011KO69,970001221

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
Lapis Lazuli | Level 10

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
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

Yes, You can .

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

Inge12
Calcite | Level 5

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 Cola200269,6373144KOBeverage
Coca Cola200370,4533144KOBeverage
Coca Cola200567,5253144KOBeverage
Coca Cola200866,6673144KOBeverage
Coca Cola200968,7343144KOBeverage
Coca Cola201070,4523144KOBeverage
Coca Cola201171,8613144KOBeverage
Dell200310,36714489DELLElectron
Dell200411,514489DELLElectron
Dell200513,23114489DELLElectron
Dell200811,69514489DELLElectron

The second dataset looks like this:

Date, Ticker Symbol, Stock price, CFACPR

31-1-2002DELL27,489999771
28-2-2002DELL24,690000531
28-3-2002DELL26,110000611
30-4-2002DELL26,340000151
31-5-2002DELL26,850000381
28-6-2002DELL26,139999391
31-7-2002DELL24,930000311
30-8-2002DELL26,620000841
30-9-2002DELL23,510000231
31-10-2002DELL28,610000611
29-11-2002DELL28,600000381
31-12-2002DELL26,739999771
31-1-2003DELL23,860000611
28-2-2003DELL26,959999081
31-3-2003DELL27,309999471
30-4-2003DELL28,979999541
30-5-2003DELL31,370000841
30-6-2003DELL31,840000151
31-7-2003DELL33,680000311
29-8-2003DELL32,619998931
30-9-2003DELL33,419998171
31-10-2003DELL361
28-11-2003DELL34,569999691
31-12-2003DELL33,979999541
30-1-2004DELL33,439998631
27-2-2004DELL32,650001531
31-3-2004DELL33,619998931
30-4-2004DELL34,779998781
28-5-2004DELL35,240001681
30-6-2004DELL35,819999691
30-7-2004DELL35,470001221
31-8-2004DELL34,840000151
30-9-2004DELL35,599998471
29-10-2004DELL35,060001371
30-11-2004DELL40,520000461
31-12-2004DELL42,139999391
31-1-2005DELL41,759998321
28-2-2005DELL40,090000151
31-3-2005DELL38,419998171
29-4-2005DELL34,830001831
31-5-2005DELL39,930000311
30-6-2005DELL39,459999081
29-7-2005DELL40,470001221
31-8-2005DELL35,599998471
30-9-2005DELL34,200000761
31-10-2005DELL31,879999161
30-11-2005DELL30,150999071
30-12-2005DELL29,950000761
31-1-2006DELL29,309999471
28-2-2006DELL291
31-3-2006DELL29,760000231
28-4-2006DELL26,200000761
31-5-2006DELL25,379999161
30-6-2006DELL24,459999081
31-7-2006DELL21,680000311
31-8-2006DELL22,549999241
29-9-2006DELL22,840000151
31-10-2006DELL24,329999921
30-11-2006DELL27,239999771
29-12-2006DELL25,090000151
31-1-2007DELL24,219999311
28-2-2007DELL22,850000381
30-3-2007DELL23,209999081
30-4-2007DELL25,209999081
31-5-2007DELL26,91099931
29-6-2007DELL28,549999241
31-7-2007DELL27,969999311
31-8-2007DELL28,251
28-9-2007DELL27,600000381
31-10-2007DELL30,600000381
30-11-2007DELL24,540000921
31-12-2007DELL24,510000231
31-1-2008DELL20,040000921
29-2-2008DELL19,900100711
31-3-2008DELL19,920000081
30-4-2008DELL18,629999161
30-5-2008DELL23,059999471
30-6-2008DELL21,879999161
31-7-2008DELL24,569999691
29-8-2008DELL21,729999541
30-9-2008DELL16,479999541
31-10-2008DELL12,199999811
28-11-2008DELL11,170000081
31-12-2008DELL10,239999771
30-1-2009DELL9,51
27-2-2009DELL8,5299997331
31-3-2009DELL9,4799995421
30-4-2009DELL11,619999891
29-5-2009DELL11,569999691
30-6-2009DELL13,729999541
31-7-2009DELL13,380000111
31-8-2009DELL15,829999921
30-9-2009DELL15,260000231
30-10-2009DELL14,449999811
30-11-2009DELL14,119999891
31-12-2009DELL14,359999661
29-1-2010DELL12,899999621
26-2-2010DELL13,239999771
31-3-2010DELL15,020000461
30-4-2010DELL16,200000761
28-5-2010DELL13,329999921
30-6-2010DELL12,060000421
30-7-2010DELL13,239999771
31-8-2010DELL11,770000461
30-9-2010DELL12,970000271
29-10-2010DELL14,395000461
30-11-2010DELL13,220000271
31-12-2010DELL13,550000191
31-1-2011DELL13,159999851
28-2-2011DELL15,829999921
31-3-2011DELL14,510000231
29-4-2011DELL15,470000271
31-5-2011DELL16,079999921
30-6-2011DELL16,670000081
29-7-2011DELL16,239999771
31-8-2011DELL14,864999771
30-9-2011DELL14,140000341
31-10-2011DELL15,810000421
30-11-2011DELL15,760000231
30-12-2011DELL14,630000111
31-1-2002KO43,751
28-2-2002KO47,389999391
28-3-2002KO52,259998321
30-4-2002KO55,509998321
31-5-2002KO55,560001371
28-6-2002KO561
31-7-2002KO49,939998631
30-8-2002KO511
30-9-2002KO47,959999081
31-10-2002KO46,479999541
29-11-2002KO45,639999391
31-12-2002KO43,840000151
31-1-2003KO40,459999081
28-2-2003KO40,220001221
31-3-2003KO40,479999541
30-4-2003KO40,400001531
30-5-2003KO45,569999691
30-6-2003KO46,409999851
31-7-2003KO44,970001221
29-8-2003KO43,520000461
30-9-2003KO42,959999081
31-10-2003KO46,400001531
28-11-2003KO46,51
31-12-2003KO50,751
30-1-2004KO49,240001681
27-2-2004KO49,959999081
31-3-2004KO50,299999241
30-4-2004KO50,569999691
28-5-2004KO51,349998471
30-6-2004KO50,479999541
30-7-2004KO43,860000611
31-8-2004KO44,709999081
30-9-2004KO40,049999241
29-10-2004KO40,659999851
30-11-2004KO39,310001371
31-12-2004KO41,639999391
31-1-2005KO41,490001681
28-2-2005KO42,799999241
31-3-2005KO41,669998171
29-4-2005KO43,439998631
31-5-2005KO44,630001071
30-6-2005KO41,751
29-7-2005KO43,759998321
31-8-2005KO441
30-9-2005KO43,189998631
31-10-2005KO42,779998781
30-11-2005KO42,689998631
30-12-2005KO40,310001371
31-1-2006KO41,380001071
28-2-2006KO41,970001221
31-3-2006KO41,869998931
28-4-2006KO41,959999081
31-5-2006KO44,029998781
30-6-2006KO43,020000461
31-7-2006KO44,51
31-8-2006KO44,810001371
29-9-2006KO44,680000311
31-10-2006KO46,720001221
30-11-2006KO46,830001831
29-12-2006KO48,251
31-1-2007KO47,880001071
28-2-2007KO46,680000311
30-3-2007KO481
30-4-2007KO52,189998631
31-5-2007KO52,990001681
29-6-2007KO52,310001371
31-7-2007KO52,110000611
31-8-2007KO53,779998781
28-9-2007KO57,470001221
31-10-2007KO61,759998321
30-11-2007KO62,099998471
31-12-2007KO61,369998931
31-1-2008KO591
29-2-2008KO58,459999081
31-3-2008KO60,869998931
30-4-2008KO58,869998931
30-5-2008KO57,259998321
30-6-2008KO51,979999541
31-7-2008KO51,51
29-8-2008KO52,069999691
30-9-2008KO52,880001071
31-10-2008KO44,060001371
28-11-2008KO46,869998931
31-12-2008KO45,270000461
30-1-2009KO42,720001221
27-2-2009KO40,849998471
31-3-2009KO43,950000761
30-4-2009KO43,049999241
29-5-2009KO49,159999851
30-6-2009KO47,990001681
31-7-2009KO49,840000151
31-8-2009KO48,770000461
30-9-2009KO53,700000761
30-10-2009KO53,310001371
30-11-2009KO57,200000761
31-12-2009KO571
29-1-2010KO54,251
26-2-2010KO52,720001221
31-3-2010KO551
30-4-2010KO53,450000761
28-5-2010KO51,400001531
30-6-2010KO50,119998931
30-7-2010KO55,110000611
31-8-2010KO55,882499691
30-9-2010KO58,520000461
29-10-2010KO61,319999691
30-11-2010KO63,169998171
31-12-2010KO65,769996641
31-1-2011KO62,849998471
28-2-2011KO63,919998171
31-3-2011KO66,339996341
29-4-2011KO67,459999081
31-5-2011KO66,809997561
30-6-2011KO67,290000921
29-7-2011KO68,010002141
31-8-2011KO70,449996951
30-9-2011KO67,559997561
31-10-2011KO68,319999691
30-11-2011KO67,230003361
30-12-2011KO69,970001221

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
Lapis Lazuli | Level 10

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
Calcite | Level 5

Hi Linlin,

Yes this is what I want in the dataset.

Thank you!!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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