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;
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
Yes you are right this is the correct answer! I want to unmark the other answer as correct but is does not work....
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
please post a few records from stock return dataset.
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! 🙂
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
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
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?
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
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!
Yes, You can .
You need post some sample data and the output you want .
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.
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
Hi Linlin,
Yes this is what I want in the dataset.
Thank you!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.